A friend emailed me weeks ago asking if there is a SQL SELECT statement to search for fields in a database if the table names are unknown. As far as I knew there wasn’t and suggested creating a script to do the searching for her.
1. Here’s a code snippet to achieve a search for a single field across all tables in a specified database.
<?php
// variables
$dbname = 'my_db';
$dbfield = 'my_field';
// connect to database
$dblink = mysql_connect('localhost', 'user', 'password');
if ($dblink) {
mysql_select_db($dbname, $dblink);
} else {
die('Failed to connect to DB');
}
// get table list
$query = "show tables";
$result = mysql_query($query, $dblink);
// loop through table names
while (list($table) = mysql_fetch_row($result)) {
// get table description
$query = "describe $table";
$result2 = mysql_query($query, $dblink);
// loop through table description fields
while ($row = mysql_fetch_array($result2, MYSQL_ASSOC)) {
// does this field match what we're looking for?
if ($row['Field'] == $dbfield) {
echo "Found '".$row['Field']."' in table => $table\n";
}
}
mysql_free_result($result2);
}
mysql_free_result($result);
?>
2. This is a variant to achieve a search for an array of fields across all tables in a specified database.
<?php
// variables
$dbname = 'my_db';
$dbfields = array('my_field1','my_field2','my_field3');
// connect to database
$dblink = mysql_connect('localhost', 'user', 'password');
if ($dblink) {
mysql_select_db($dbname, $dblink);
} else {
die('Failed to connect to DB');
}
// get table list
$query = "show tables";
$result = mysql_query($query, $dblink);
// loop through table names
while (list($table) = mysql_fetch_row($result)) {
// get table description
$query = "describe $table";
$result2 = mysql_query($query, $dblink);
// loop through table description fields
while ($row = mysql_fetch_array($result2, MYSQL_ASSOC)) {
// does this field match any in the array?
if (in_array($row['Field'], $dbfields)) {
echo "Found '".$row['Field']."' in table => $table\n";
}
}
mysql_free_result($result2);
}
mysql_free_result($result);
?>
3. And finally, this version searches for an array of fields across all tables and databases in MySQL.
<?php
// variables
$dbfields = array('my_field1','my_field2','my_field3');
// connect to database
$dblink = mysql_connect('localhost', 'user', 'password');
if (!$dblink) {
die('Failed to connect to DB');
}
// get databases list
$query = "show databases";
$result = mysql_query($query, $dblink);
// loop through table names
while (list($dbname) = mysql_fetch_row($result)) {
mysql_select_db($dbname);
// get table list
$query = "show tables";
$result2 = mysql_query($query, $dblink);
// loop through table names
while (list($table) = mysql_fetch_row($result2)) {
// get table description
$query = "describe $table";
$result3 = mysql_query($query, $dblink);
// loop through table description fields
while ($row = mysql_fetch_array($result3, MYSQL_ASSOC)) {
// does this field match what we're looking for?
if (in_array($row['Field'], $dbfields)) {
echo "Found '".$row['Field']."' in table => $table of database => $dbname\n";
}
}
mysql_free_result($result3);
}
mysql_free_result($result2);
}
mysql_free_result($result);
?>
Be sure to change the variables $dbname, $dbfields, and the mysql_connect options before using these scripts. You can run them by typing
php -f /path/filename
on the command-line or from your web server. If run from the web, change the new line (\n) to a HTML line break (<br>).
Source Files: db-search-fields.zip