Database Metadata

• Table MetaData
  • List the Tables in Your Database
  • Determine If a Table Exists
• Field MetaData
  • List the Fields in a Table
  • Determine If a Field is Present in a Table
  • Retrieve Field Metadata
  • List the Indexes in a Table

Table MetaData

These functions let you fetch table information.

List the Tables in Your Database

$db->listTables();

Returns an array containing the names of all the tables in the databaseyou are currently connected to. Example:

$tables = $db->listTables();
 
foreach ($tables as $table)
{
        echo $table;
}

Note

Some drivers have additional system tables that are excluded from this return.

Determine If a Table Exists

$db->tableExists();

Sometimes it’s helpful to know whether a particular table exists beforerunning an operation on it. Returns a boolean TRUE/FALSE. Usage example:

if ($db->tableExists('table_name'))
{
        // some code...
}

Note

Replace table_name with the name of the table you are looking for.

Field MetaData

List the Fields in a Table

$db->getFieldNames()

Returns an array containing the field names. This query can be calledtwo ways:

1. You can supply the table name and call it from the $db->object:

$fields = $db->getFieldNames('table_name');
 
foreach ($fields as $field)
{
        echo $field;
}

1. You can gather the field names associated with any query you run bycalling the function from your query result object:

$query = $db->query('SELECT * FROM some_table');
 
foreach ($query->getFieldNames() as $field)
{
        echo $field;
}

Determine If a Field is Present in a Table

$db->fieldExists()

Sometimes it’s helpful to know whether a particular field exists beforeperforming an action. Returns a boolean TRUE/FALSE. Usage example:

if ($db->fieldExists('field_name', 'table_name'))
{
        // some code...
}

Note

Replace field_name with the name of the column you are lookingfor, and replace table_name with the name of the table you arelooking for.

Retrieve Field Metadata

$db->getFieldData()

Returns an array of objects containing field information.

Sometimes it’s helpful to gather the field names or other metadata, likethe column type, max length, etc.

Note

Not all databases provide meta-data.

Usage example:

$fields = $db->getFieldData('table_name');
 
foreach ($fields as $field)
{
        echo $field->name;
        echo $field->type;
        echo $field->max_length;
        echo $field->primary_key;
}

If you have run a query already you can use the result object instead ofsupplying the table name:

$query  = $db->query("YOUR QUERY");
$fields = $query->fieldData();

The following data is available from this function if supported by yourdatabase:

• name - column name
• max_length - maximum length of the column
• primary_key - 1 if the column is a primary key
• type - the type of the column

List the Indexes in a Table

$db->getIndexData()

Returns an array of objects containing index information.

Usage example:

$keys = $db->getIndexData('table_name');
 
foreach ($keys as $key)
{
        echo $key->name;
        echo $key->type;
        echo $key->fields;  // array of field names
}

The key types may be unique to the database you are using.For instance, MySQL will return one of primary, fulltext, spatial, index or uniquefor each key associated with a table.

$db->getForeignKeyData()

Returns an array of objects containing foreign key information.

Usage example:

$keys = $db->getForeignKeyData('table_name');
 
foreach ($keys as $key)
{
        echo $key->constraint_name;
        echo $key->table_name;
        echo $key->column_name;
        echo $key->foreign_table_name;
        echo $key->foreign_column_name;
}

The object fields may be unique to the database you are using. For instance, SQLite3 doesnot return data on column names, but has the additional sequence field for compoundforeign key definitions.