I recently wanted this ability in a PHP script I was writing, very similar to how you can search the whole database for a string using PHPMyAdmin. My first place I go when I have a problem is google,  but a google result returned no matches at that time, so I decided to write a quick method of my own.

Upon futher investigation, I did finally find this project: http://code.google.com/p/anywhereindb/. I didn’t take a close look into it but I saw it seemed to be a bit more polished and probably has better error checking and handling than mine does. I am still giving you my version though, as I feel it’s a bit more simple and grasps the key concepts.

The code was tested against PHP 5.3, other versions I have no idea if it will work. Note my user and pass for my database are server variables using the security method mention in this post: http://www.brianhare.com/wordpress/2011/02/18/hiding-mysql-passwords-in-php-using-apache-environment-variables/

Source code

<?php
$config['host'] = "localhost";
$config['user'] = $_SERVER['db_user'];
$config['pass'] = $_SERVER['db_pass'];
$database = "my_database";
$search = "billy";
 
mysql_connect($config['host'], $config['user'], $config['pass']);
mysql_select_db($database);
 
$SQL = "SHOW TABLES FROM $database";
$result = mysql_query($SQL) or die ("DB Error, could not list tables - MySQL Error: " . mysql_error());
 
while ($row = mysql_fetch_row($result)) {
	$table = $row[0];
	$SQL = "SELECT * FROM `$table`";
	$table_result = mysql_query($SQL) or die("<pre>". $SQL . "<br>MYSQL Error: " . mysql_error() . "</pre>");
	if(mysql_num_rows($table_result) < 1)
		continue;
	$array = mysql_fetch_assoc($table_result);
	$table_fields = array_keys($array);
	$SQL = "SELECT * FROM `$database`.`$table` WHERE (`" . implode("` LIKE '%$search%' OR `", $table_fields) . "` LIKE '%$search%');";
	$search_result = mysql_query($SQL) or die("<pre>". $SQL . "<br>MYSQL Error: " . mysql_error() . "</pre>");
	if(mysql_num_rows($search_result) < 1)
		continue;
 
	print "Search results for '$search' in table: $table <hr>";
	print "<table border='1' cellpadding='5'>";
	print"<tr>";
	foreach ($table_fields as $field) {
		print "<th>$field</th>";
	}
	print "</tr>";
 
	while($row = mysql_fetch_assoc($search_result)){
		echo "<tr>";
		foreach ($row as $field=>$value) {
			$style = (strpos($value, $search) === false) ? '' : 'style="background-color:lightgreen;"';
			echo "<td $style>$value</td>";
		}
		echo "</tr>";
	}
	echo "</table><br><br>";
}
?>

The most confusing part is probably the

Source code
   
WHERE (`" . implode("` LIKE '%$search%' OR `", $table_fields) . "` LIKE '%$search%');";

Where basically the implode function will expand the array that contains the table fields ($table_fields)and between each table it will add ` LIKE '%billy%' OR ` between each table field. However, you have to take into considerationthe first table field won't have a ` in front and the last one wont have the ` LIKE '%billy%', so those must also be appended. Perhaps the more inuitive thing to have done would of been to do it this way:

Source code
   
$SQL = "SELECT * FROM `$database`.`$table` WHERE (";
foreach($table_fields as $field) {
	$SQL .= "`$field` LIKE '%$search%' OR ";
}
$SQL = rtrim($SQL, " OR ");
$SQL .= ");";

	


No tags