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):

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35

<?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”.