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

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

Comments

comments