Search in all tables and columns – PHP & MySQL

Search is one of the feature which is needed in almost every website. Whether you are working on admin panel or user side, you need to provide a functionality where user can search and get data. So, in this article, we will discuss how you can search a string in all tables and columns of a database. We will be using PHP and MySQL.

When it comes to admin panel, then it require to search all tables and all its fields in database for search query. For example, admin wants to know all payments transferred via account number 1234. Then he will simply type 1234 and it automatically scan all tables (including the payment’s table) and display the data.

While in typical environment, you have to manually set the table name along with column name to search. So we will make it to search complete database for searched query.

Create a form

Create a simple form where user can enter string and we will search that string in whole database.

<form method="GET" action="search.php">

    <input name="search" placeholder="Enter query">
    <input type="submit" name="submit">

</form>

Perform the search

Create a new file named “search.php” and paste the following code in it:

<?php

// Check if form submits
if (isset($_GET["submit"]))
{
	// Get searched query
    $search = $_GET["search"];

    // Connect with database
    $conn = mysqli_connect("localhost", "root", "", "classicmodels");
}

?>

First we need to get all tables inside the database. We will be running the MySQL command SHOW TABLES and it will return all tables. Then we will loop through each table. Here we will get object like “Tables_in_dbname” where “dbname” will be your database name. In this case, it will be “Tables_in_classicmodels”.

It will give us the table name which can be used to get all columns (structure) and data inside the table.

$tables = mysqli_query($conn, "SHOW TABLES");
while ($table = mysqli_fetch_object($tables))
{
    $table_name = $table->{"Tables_in_classicmodels"};

    // put below code here
}

Now we need to get all the rows and columns of this table. Since we do not know the number of each table so we will create an array that will store all columns of each table with LIKE clause on each column.

<caption>

We can display column name as caption of each table using <caption> tag. Then we will run the query to get all columns of each table and loop through each column and display that as TH (table heading). After that, we will need the $columns array again when display row data. So we will use the function mysqli_data_seek($columns, 0) and it will move the database cursor of this object to zero.

We also will push the column name in an array with LIKE clause so it will become “WHERE column1 LIKE ‘%value%’ OR column2 LIKE ‘%value%’ “ and so on. We can combine this array into string using implode function and append in $sql variable.

Finally we will run this query and loop through each row returned from it. Inside the row array we also need to run the column array to get all columns of that table. Make sure to call mysqli_data_seek function otherwise you won’t be able to see more than 1 row.

<?php

// put this code inside above while loop

// Create SQL query to get all rows (more on this later)
$sql = "SELECT * FROM " . $table_name . " WHERE ";

// An array to store all columns LIKE clause
$fields = array();

// Query to get all columns from table
$columns = mysqli_query($conn, "SHOW COLUMNS FROM " . $table_name);

?>
	
<table>

	<!-- Display table name as caption -->
    <caption>
        <?php echo $table_name; ?>
    </caption>

    <!-- Display all columns in table header -->
    <tr>
        
		<?php
			// Loop through all columns
			while ($col = mysqli_fetch_object($columns)):

				// Use LIKE clause to search input in each column
				array_push($fields, $col->Field . " LIKE '%" . $search . "%'");

		?>

			<!-- Display column in TH tag -->
			<th><?php echo $col->Field; ?></th>

		<?php
			endwhile;

			// Move cursor of $columns to 0 so it can be used again
			mysqli_data_seek($columns, 0);
		?>
        
    </tr>

    <?php
    	// Combine $fields array by OR clause into one string
		$sql .= implode(" OR ", $fields);
		$result = mysqli_query($conn, $sql);

		// Loop through all rows returned from above query
		while ($row = mysqli_fetch_object($result)):
			?>

			<tr>

				<?php
					// Loop through all columns of this table
					while ($col = mysqli_fetch_object($columns)):
				?>

					<td>

						<?php
							// Display row value from column field
							echo $row->{$col->Field};
						?>

					</td>

				<?php endwhile; mysqli_data_seek($columns, 0); /* end of column while loop */ ?>

			</tr>

		<?php endwhile; /* end of row while loop */ ?>

</table>

So that’s it. Hope that helps you in your almost every project because in every website you will need to allow user to search some string in whole database. If you face any problem, please feel free to ask in the comment’s section below.

Learn how you can recursively search string in all folders in your cPanel’s file manager.

Recursively search string in all folders in cPanel – PHP

[wpdm_package id=’193′]