Get the structure of a MySQL table in PHP (PDO).

In this tutorial, I will be showing you how to get the structure / column details of a MySQL table using PHP’s PDO object.

Code snippet (be sure to read the comments):

<?php

//Our MySQL connection details.
define('MYSQL_SERVER', 'localhost');
define('MYSQL_DATABASE_NAME', 'test');
define('MYSQL_USERNAME', 'root');
define('MYSQL_PASSWORD', '');

//Instantiate the PDO object and connect to MySQL.
$pdo = new PDO(
        'mysql:host=' . MYSQL_SERVER . ';dbname=' . MYSQL_DATABASE_NAME, 
        MYSQL_USERNAME, 
        MYSQL_PASSWORD
);

//The name of the table that we want the structure of.
$tableToDescribe = 'users';

//Query MySQL with the PDO objecy.
//The SQL statement is: DESCRIBE [INSERT TABLE NAME]
$statement = $pdo->query('DESCRIBE ' . $tableToDescribe);

//Fetch our result.
$result = $statement->fetchAll(PDO::FETCH_ASSOC);

//The result should be an array of arrays,
//with each array containing information about the columns
//that the table has.
var_dump($result);

//For the sake of this tutorial, I will loop through the result
//and print out the column names and their types.
foreach($result as $column){
    echo $column['Field'] . ' - ' . $column['Type'], '<br>';
}

A step-by-step guide to the code above:

  1. We defined our MySQL connection details.
  2. We connected to MySQL using the PDO object.
  3. For this example, I’ve chosen the table “users”. However, you will want to change the $tableToDescribe variable┬áto match the name of the table that you are wanting to describe.
  4. We executed a DESCRIBE query with PDO.
  5. We fetched the result.
  6. I did a var_dump on the result array, just so you can see what type of data will be returned.
  7. I looped through the result array, printing out the column name and its type (VARCHAR, INT, etc).

The DESCRIBE query will return an array of associative arrays, with each array representing a column in the table. This array will contain the following information:

  • Field: The name of the field / column.
  • Type: The type of column it is (VARCHAR, INT, TEXT, etc).
  • Null: Whether the column can be NULL or not.
  • Key: The type of index (if any) that has been applied to the column. A Primary Key will read “Pri”, whereas a regular index will read “MUL”. Unique non-Primary-Key indexes will read “UNI”.
  • Default: The column’s default value.
  • Extra: Extra information about the column. For example, if it’s an Auto Increment column, then this will read “auto_increment”.