PHP: Get list of possible foreign key values in MySql using PDO

Using foreign keys is very good way to enforce Referential integrity of database. Often it is needed to give ability to insert a record into a database from some kind of user interface, such as HTML form. When foreign keys are involved, it is needed to check that user does not insert data which does not have corresponding foreign key. We might have form which have select-element for picking the value, but to create an element we need find out where the foreign table is and which are acceptable values.

Lets assume database have two tables: Departments and Cities. Department have city-column which is foreign key pointing to cities table.

Departments

Name	City
Manhattan flowers	New York City
Michigan beauty	Chigago

Cities

City	State
New York City	New York
Chigago	Illinois

We need to create an user interface for adding new departmens. While "city" is set to be foreign key, we need to make sure user cannot point to city which is not defined in cities table. All data related to foreign key is stored in information_schema, as base I used example written by Stefan Luv and Timo Huovinen to find out right table which hold the foreign key data. Then processed the data to find all possible values.
Lets assume you have created an PDO connection which is stored in $db variable:

$q=$db->prepare('SELECT ke.referenced_table_name assoc_table,
  ke.referenced_column_name assoc_col FROM
  information_schema.KEY_COLUMN_USAGE ke WHERE ke.referenced_table_name IS NOT NULL            
AND ke.table_schema=:database AND ke.table_name=:tablename AND ke.column_name=:col');
$q->bindValue(':database','mydatabasename'); //Set your database name here
$q->bindValue(':tablename','Departments');   //Set your table name here
$q->bindValue(':col','City');                //Set the column which foreign key values you want to have here
if($q->execute()) {
$foreingtable=$q->fetch(PDO::FETCH_ASSOC);
$q=$db->prepare('SELECT '.$foreingtable['assoc_col'].' FROM '.$foreingtable['assoc_table']);
if($q->execute())
echo json_encode($q->fetchAll(PDO::FETCH_COLUMN));
 }
else {
header('http/1.1 500 Internal Server Error');
print_r($q->errorInfo());
exit;
 }

Code below will find all possible values of column which is set to be foreign key (New York City, Chigago). It will then return it as JSON format, which may be used to create for example an drop-down list.

If you have any questions or something to add, don't hesitate to comment.