How Do I Get Meta Data From MySQL Using PHP?

I know you’ve all asked that question before, right? Well maybe not, but I recently had the need to detect what the DATATYPE for arbitrary columns in arbitrary tables were inside a MySQL database using PHP. Turns out that it isn’t all that hard to get.

The main thing about using this method is that we are simply sending SQL queries to MySQL which instruct it to give us the required information. The first one is to retrieve a list of tables for the currently selected database:

$result = mysql_query("SHOW TABLES FROM $database", $conn);

The next time is when we retrieve the columns for the table and the column definitions:

$tableName = $row[0];
$cols = mysql_query("SHOW COLUMNS FROM $tableName", $conn);

Notice that I looped through the results of the first query (the table names). I then use the value of the table names returned from the query to build the query for extracting the column definitions.

Finally, you might ask why I decided to throw all of the results into an array? I could just as easily (probably more easily) have output the results to the console as I ran through the queries. Well, in my situation I needed to actually store and compare the results at a later time. So that’s what I did! Following is an entire code sample that should work; just fill in your login credentials and have fun!

 $cols)
    {
      echo "===== $table =====\n";
      foreach($cols as $field => $type)
      {
        echo "$field : $type\n";
      }
    }
  }

  // Free result
  if($result)
    mysql_free_result($result);
  // Close connection
  if($conn)
    mysql_close($conn);
?>