How to get the structure of a MySQL table in PHP.

This tutorial will show you how to get the structure of a MySQL table using PHP.

In the example below, we will get the structure using the PDO object and then list the column names, their indexes, and their data types.

//Your 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.
$tableToDescribe = 'users';

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

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

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

//Loop through the result and print the column details.
foreach($result as $column){
    echo $column['Field'] . ' - ' . $column['Type'], '<br>';
}

An explanation of the code above:

  1. We defined our MySQL connection details at the top of the PHP script.
  2. We connected to MySQL using the PDO object.
  3. In the example above, we are retrieving the structure of a table called “users”. You will need to change the $tableToDescribe variable to match the name of your table.
  4. We executed a DESCRIBE query and fetched the result.
  5. After that, we used the var_dump function to dump information about the result. This is helpful for debugging purposes.
  6. Finally, we looped through the result array, printing each column name and its type (VARCHAR, INT, etc.).

The DESCRIBE statement 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 column.
  • Type: The data type of the column (VARCHAR, INT, TEXT, etc.).
  • Null: Whether it 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”, while 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 auto-increments, then this will read “auto_increment”.