fcOnTheWeb Logo Web technologies, made easy.

fcOnTheWeb Red DotSearch an entire database with PHP

Today we take a look at a handy script which can be great when working with an unfamiliar database. Perhaps you're working on a project with little documentation, or you're working with a database built by someone else and you need to try and find where a piece of data is stored. You know what that data is, but what table and field is it in? Well, what we have here can search through the entire database and bring out all the records that contain the precious data.

What we've set up is a single page that first takes your database and search details, then submits to itself and performs the search. When it's all done, it presents the results to you in a table format.

You can download the complete, ready to use file here: searchdb.zip.

Let's take a look at each part of the page. First, we'll start with the HMTL form where we enter the data.

This is quite simple: an HMTL form with four inputs. One each for the database name, the username, the password and the term you want to search for. The form is called details and it submits to itself (searchdb.php) via POST.

Let's take a look at the HMTL:

<form name="details" action="searchdb.php" method="POST">

<p>Database: <input type="text" name="database" /></p>

<p>Username: <input type="text" name="username" /></p>

<p>Password: <input type="password" name="password" /></p>

<p>Search term: <input type="text" name="search" /></p>

<p><input type="submit" value="Search" /> <input type="reset" value="Reset" /></p>

</form>

Now, on the same page we need to write the PHP logic which will search through our database and show the results. But first, we only want this to execute if the form has been submitted. If the form hasn't been submitted we want the user to see the input boxes, and if it has been submitted we want them to see the results.

So we need a check to see if the form has been submitted or not. We check to see if the search field has been POSTed through, and that it's not empty. Like so:

<?php if (isset($_POST["search"]) && ($_POST["search"] != "")) {

} ?>

It's inside the if statment where we write our logic. Firstly, we need to connect to the database with the details provided on the form.

We do this with just two lines of code:

$conn = mysql_connect("localhost", $_POST["username"], $_POST["password"]) or die ("Error connecting to mysql");

mysql_select_db($_POST["database"]);

So we connect to a specified database on the same server as where this script is running (localhost) using the username and password supplied.

Now we've connected to the database, we need to first setup two arrays to store the table names and their fields, then we need to get all of the tables in the database. We can get all of the table names using the SHOW statement in SQL. With the results we need to cycle through them and populate the array which will hold the table names.

$aryTables = new array();

$aryFields = new array();

$sql = "SHOW TABLES FROM " . $_POST["database"];

$result = mysql_query($sql);

while ($row = mysql_fetch_row($result)) {

$aryTables[sizeof($aryTables)] = $row[0];

}

Now that we've got an array with all our tables in it, we're going to iterate through that array, and get the fields and data from each table. We do this with a for loop.

for ($i = 0; $i < sizeof($aryTables); $i = $i + 1) {

}

Inside the for loop we first need get all the columns or fields from the table, and add those to our array.

$sql = "SHOW COLUMNS FROM " . $aryTables[$i];

$result = mysql_query($sql);

while ($row = mysql_fetch_row($result)) {

$aryFields[sizeof($aryFields)] = $row[0];

}

Now this is where things start to get a bit tricky. We have all the fields from the table, but we need to build a dynamic SQL statement which checks each field for our search term. So we need to select records from the table we have selected in our for loop, where one of the fields in the table is like our search term.

So we create the first part of our SQL statement to add the table we want, then we iterate through all the fields in our array, adding each one to the SQL statment. Let's take a look.

$sql = "SELECT * FROM " . $aryTables[$i] . " WHERE ";

for ($j = 0; $j < sizeof($aryFields); $j = $j + 1) {

$sql = $sql . $aryTables[$i] . "." . $aryFields[$j] . " LIKE '%" . $_POST["search"] . "%'";

if (($j + 1) != sizeof($aryFields)) {

$sql = $sql . " OR ";

} else {

$sql = $sql . ";";

}

}

What have we done? First we write the the start of our our SQL statement to select from our table, and we finish with a where because we're going to add the criteria in the next loop.

We then set up a for loop to iterate through all the fields in our array. For each field we add to the already created SQL statement. We add the tablename.fieldname and then the keyword like so we can compare. Importantly, we wrap the criteria (which is the search term entered on the initial form) we want the field to be like in % signs. This means it will return a positive if the term is anywhere in the field, rather than just returning records where the whole field matches our search term.

Next we check to see if we have used our last field in the array. If we haven't we insert and OR, or if we have reached the last field then we insert a semi-colon to end the SQL statement.

This loop will create an SQL statement similar to:

SELECT * FROM user WHERE user.id LIKE '%chris%' OR user.name LIKE '%chris%' OR user.phone LIKE '%chris%';

Now that we've got our SQL statement created, we need to execute it and display the results.

We'll execute the statement, check if there are any results returned, and if there are we'll loop through them and display them. Like this:

$result = mysql_query($sql);

if (mysql_num_rows($result) > 0) {

echo "<p>" . $aryTables[$i] . "</p>";

foreach ($aryFields as $field => $value) {

echo "<table><tr><thead>";

echo "<th>" . $value . "</th>";

}

while ($aryData = mysql_fetch_assoc($result)) {

echo "<tr>";

for ($j = 0; $j < sizeof($aryFields); $j = $j + 1) {

echo "<td>" . substr(htmlspecialchars($aryData[$aryFields[$j]], ENT_QUOTES), 0, 150) . "</td>";

}

echo "<tr>";

}

echo "<table>";

}

So we execute the SQL statement we created earlier, and then check to see if any results were returned. If they were, we write out the table name on the page, and then set up the header rows with the field names. We do this by iterating through and writing out the values in our aryFields array.

Next we turn the result set into an associative array. This creates a keyed array with the field name as the key and the record as the value. As we move through each row, we write out the data in the table. We've limited the amount of data written out to 150 characters to help keep things tidy, and we've also used the htmlspecialchars function so we can print out any HTML without it becoming part of the page. Then we close our row and the table.

After all that, we just have two things left to do. First, we need to reset the aryFields array so it's ready for the next table:

$aryFields = array();

And we need to close the database connection when we're all finished looping and printing:

<?php if (isset($conn)) {mysql_close($conn);} ?>

So that's everything to create your own handy, complete database search routine. The only caveat to the use of this script is that it needs to be installed on the same server as the database, although you could change the database connection string if you wanted to run it remotely. Also, running this script on a large database can take a bit of time, so it's not something you want to be using often.

You can download the complete, ready to use file here: searchdb.zip.