PEAR Manual
Prev |  | Next

# DB_common::tableInfo()

DB_common::tableInfo() -- Gets info about columns in a table or a query result

## Synopsis

``array tableInfo` (mixed $result [, integer $mode = `NULL`])`

## Description

Get information about columns in a table or a query result.

## Parameter

mixed `$result`

  DB_result object from a query or a string containing the name of a table

  If the name of the table needs to be delimited (ie: the name is a reserved word or has spaces in it), use the quoteIdentifier() method on the table name when passing it.

  This can also be a query result resource identifier, but doing so is deprecated.

integer `$mode`

  one of the tableInfo mode constants

## Return value

array - an associative array of the table's information or a DB_Error object on failure

The array's contents depends on the `$mode` parameter.

The names of tables and columns will be lowercased if the `DB_PORTABILITY_LOWERCASE` portability mode is enabled.

The `flags` element contains a space separated list of extra information about the column. If the DBMS is able to report a column's default value, the value is passed through rawurlencode() to avoid problems caused by potential spaces in the value.

Most DBMS's only provide the `table` and `flags` elements if `$result` is a table name. Only fbsql and mysql provide full information from queries.

The `type` element contains the type returned by the DBMS. It varies from DBMS to DBMS.

## tableInfo Modes

This section describes the format of the returned array and how it varies depending on which `$mode` was used when the function was called.

The sample output below is based on this query:

    SELECT tblFoo.fldID, tblFoo.fldPhone, tblBar.fldId
      FROM tblFoo
      JOIN tblBar ON tblFoo.fldId = tblBar.fldId;

`NULL` or `0`

      [0] => Array (
        [table] => tblFoo
        [name] => fldId
        [type] => int
        [len] => 11
        [flags] => primary_key not_null
      )
      [1] => Array (
        [table] => tblFoo
        [name] => fldPhone
        [type] => string
        [len] => 20
        [flags] =>
      )
      [2] => Array (
        [table] => tblBar
        [name] => fldId
        [type] => int
        [len] => 11
        [flags] => primary_key not_null
      )

`DB_TABLEINFO_ORDER`

  In addition to the information found in the default output, a notation of the number of columns is provided by the `num_fields` element while the `order` element provides an array with the column names as the keys and their location index number (corresponding to the keys in the default output) as the values.

  If a result set has identical field names, the last one is used.

      [num_fields] => 3
      [order] => Array (
        [fldId] => 2
        [fldTrans] => 1
      )

`DB_TABLEINFO_ORDERTABLE`

  Similar to `DB_TABLEINFO_ORDER` but adds more dimensions to the array in which the table names are keys and the field names are sub-keys. This is helpful for queries that join tables which have identical field names.

      [num_fields] => 3
      [ordertable] => Array (
        [tblFoo] => Array (
            [fldId] => 0
            [fldPhone] => 1
        )
        [tblBar] => Array (
            [fldId] => 2
        )
      )

`DB_TABLEINFO_FULL`

  Contains the information from both `DB_TABLEINFO_ORDER` and `DB_TABLEINFO_ORDERTABLE`

The tableInfo mode constants are bitwised, so they can be combined using `|`.

## Throws

Table 33-1. Possible PEAR_Error values

Error code | Error message | Reason | Solution
--- | --- | --- | ---
`DB_ERROR_NOT_CAPABLE` | DB backend not capable | Driver doesn't support this feature. | Switch to another database system, if you really need this feature.
`DB_ERROR_NEED_MORE_DATA` | insufficient data supplied | The data passed in the `$result` parameter was not a valid table name or result identifier. | Check the table name for typographical errors or that the query ran correctly.
`DB_ERROR_NODBSELECTED` | no database selected | No database was chosen. | Check the DSN in connect().
`can't distinguish duplicate field names` |  | The query result has multiple columns with the same name. PHP's Informix extension deals with columns having the same names by overwriting the prior columns information. Therefore, tableInfo() is unable to properly represent these result sets. | Use aliases for columns that have the same names.

## Note

This function can not be called statically.

tableInfo() is not portable because not all drivers have this method, many DBMS's are unable to determine table names from query results and the metadata returned by each database system varies dramatically.

## Example

Example 33-1. Finding information about a table
    <?php
    // Once you have a valid DB object named $db...
    $info = $db->tableInfo('tablename');
    print_r($info);
    ?>

Example 33-2. Finding information about a query result
    <?php
    // Once you have a valid DB object named $db...
    $res  =& $db->query('SELECT * FROM tablename');
    $info =  $db->tableInfo($res);
    print_r($info);
    ?>
Warning
Prior to version 1.6.0, tableInfo() was a part of the DB_result class, so had to be called like this:
    <?php
    // Once you have a valid DB object named $db...
    $res  =& $db->query('SELECT * FROM tablename');
    $info =  $res->tableInfo();  // <---- DEPRECATED
    print_r($info);
    ?>

Prev | Home | Next
DB_common::setOption() | Up | DB_result