Accessing MySQL table metadata from PHP

I was wanting to pull the column comments from a MySQL table to display as 'tooltips' on a webpage display of the table, for users unsure of the meaning of a particular column. I thought this would be trivial. It is not.

MySQL stores table information in an additional special schema called 'information_schema'. You connect to this as you would connect to the database that contains the orginal data from which the web page table is populated. If you have access to that database/table, your same login credentials will allow you access to the corresponding entries in information_schema. It is necessary to connect to the information_schema DB and retrieve the column information from there.

PHP has a default setting where if you send the same connection string to the database twice, you will get the same link id. So the following lines:

// connect to DB via read user
$dbread = mysql_connect('localhost', 'readuser', 'password')
or die('Could not connect: ' . mysql_error());
mysql_select_db('divinity_todb', $dbread);

// connect to DB's information schema:
$db_metadata = mysql_connect('localhost', 'readuser', 'password')
or die('Could not connect: ' . mysql_error());
mysql_select_db('information_schema', $db_metadata);

Will not work ($dbread and $db_metadata point to the same resource id).

You will need to do the following:

// connect to DB via read user
$dbread = mysql_connect('localhost', 'readuser', 'password', true)
or die('Could not connect: ' . mysql_error());
mysql_select_db('divinity_todb', $dbread);

// connect to DB's information schema:
$db_metadata = mysql_connect('localhost', 'readuser', 'password', true)
or die('Could not connect: ' . mysql_error());
mysql_select_db('information_schema', $db_metadata);

Update!

The easier and better way to get the information is simply to specify the schema name in an ordinary connection:

- having selected the database and connected with $dbread

- do something like 'select * from information_schema.tables ...'

This means a separate connection is no longer required.