Getting MySQL table column names, types and more as JSON

How to get information in JSON format about a MySQL table.

INFORMATION_SCHEMA has information about the metadata of the database structure such as column types, defaults, lenghts and keys.

To view all the available information returned for a table execute the following with your database & table name added.

SELECT *
FROM INFORMATION_SCHEMA.COLUMNS
WHERE TABLE_SCHEMA = 'database'
  AND TABLE_NAME = 'table';

Now to build an array for the JSON output using PHP:

$database = 'the_db_name';
$table = 'test_table';

$output_array = array();

$select = $db->prepare("SELECT COLUMN_NAME, COLUMN_TYPE, COLUMN_DEFAULT, DATA_TYPE, IS_NULLABLE, COLUMN_KEY FROM INFORMATION_SCHEMA.COLUMNS WHERE TABLE_SCHEMA = ? AND TABLE_NAME = ?;");
$select->execute([$database, $table]);

$cols = 0;
while ($row = $select->fetch(PDO::FETCH_ASSOC)) {
    $column = $row['COLUMN_NAME'];
    $type = $row['COLUMN_TYPE'];
    $type_short = $row['DATA_TYPE'];
    $default = $row['COLUMN_DEFAULT'];
    $is_nullable = $row['IS_NULLABLE'];
    $column_key = $row['COLUMN_KEY'];
    $output_array[] = array('column' => $column, 'type' => $type, 'type_short' => $type_short, 'default' => $default, 'is_nullable' => $is_nullable, 'key' => $column_key);
    $cols++;
}

$output_array = array_merge(array('database' => $database, 'table' => $table, 'columns' => $cols), $output_array);

To view output:

echo json_encode($output_array);

Here is a sample response:

{
  "database": "store",
  "table": "items_sample",
  "columns": 4,
  "0": {
    "column": "id",
    "type": "int(11)",
    "type_short": "int",
    "default": null,
    "is_nullable": "NO",
    "key": "PRI"
  },
  "1": {
    "column": "item",
    "type": "varchar(255)",
    "type_short": "varchar",
    "default": null,
    "is_nullable": "NO",
    "key": "MUL"
  },
  "2": {
    "column": "quantity",
    "type": "int(11)",
    "type_short": "int",
    "default": "0",
    "is_nullable": "NO",
    "key": ""
  },
  "3": {
    "column": "sold",
    "type": "int(11)",
    "type_short": "int",
    "default": "0",
    "is_nullable": "NO",
    "key": ""
  }
}