How to perform a database search and display the results in PHP

#1

INTRODUCTION

The search feature is really important nowadays, for searching posts, products, and other things... Today, we will learn how to perform a database search and display the results using PHP.

/!\ Make sure you are using PHP 5.6

THE TUTORIAL

Create a file called dbconnect.php and add the following content into it: 
<?php

 error_reporting( ~E_DEPRECATED & ~E_NOTICE );
 
 define('DBHOST', 'localhost');
 define('DBUSER', 'DB_USERNAME');
 define('DBPASS', 'DB_PASSWORD');
 define('DBNAME', 'DB_NAME');
 
 $conn = mysqli_connect(DBHOST,DBUSER,DBPASS);
 $dbcon = mysqli_select_db($conn,DBNAME);
 
 if ( !$conn ) {
  die("Connection failed : " . mysqli_error());
 }
 
 if ( !$dbcon ) {
  die("Database Connection failed : " . mysqli_error());
 }
?>

Now, create a file called search.php, and add the following content to it:

<?php
include_once('dbconnect.php');
if(isset($_POST['search'])){
	$q = $_POST['q'];
	$query = mysqli_query($conn,"SELECT * FROM `table_name` WHERE `thing_to_search` LIKE '%$qname%'"); 
//Replace table_name with your table name and `thing_to_search` with the column you want to search
	$count = mysqli_num_rows($query);
	if($count == "0"){
		$output = '<h2>No result found!</h2>';
	}else{
		while($row = mysqli_fetch_array($query)){
		$s = $row['column_to_display']; // Replace column_to_display with the column you want the results from
				$output .= '<h2>'.$s.'</h2><br>';
			}
		}
	}
?>
<!DOCTYPE html>
<html>
	<head>
		<title>Search</title>
	</head>
	<body>
		<form method="POST" action="search.php">
			<input type="text" name="q" placeholder="query">
			<input type="submit" name="search" value="Search">
		</form>
		<?php echo $output; ?>
	</body>
</html>

/!\

  1. Replace table_name with your table name and thing_to_search with the column you want to search
  2. Replace column_to_display with the column you want the results from

CONCLUSION

We have created a search feature that will search the database and display the results as

<h2>
paragraphs.

IN THE END

Hope you enjoyed this tutorial, and if it worked for you, give it a thumbs up by clicking the like button.
If you need any more help, you can create a new topic here, and we will be happy to help you!

Mysqli_fetch_all wont work (PHP/mysql)
#2

Hi i think this needs to be edited :slight_smile:

#3

Owh, yeah, messed up :confused:

#4

This has a SQL injection vulnerability - a cracker could construct a statement in q to escape out of the string delimiters and inject SQL of their own choosing. For example, they could join to other tables to attempt to discover secret information.

A good resource about this is this article, and this is useful too. You’ll need to use parameter binding here.

#5

I’m not sure what happens if you mix database APIs, but it is not recommended. :slight_smile: Should this be a mysqli_ function? Either way, the mysql_ functions were deprecated in 5.6, and were removed in 7.0. Can you use a MySQLi equivalent?

#6

Thanks for pointing out, it is a typo mistake.
For the other one, doing security is up to users :slight_smile:

#7

Security experts disagree with that view strongly.

I like PHP, but as a result of its extremely low bar to entry, we’ve got the whole world :world_map: and their dog :dog: writing programming tutorials. Many of them contain serious security problems, which are then replicated by programmers who don’t know what issues to look out for. Finally, by accident, a few of them become employed programmers or, perhaps their side-project becomes unfortunately popular. The security woes they are creating are then visited upon their hapless users, who had no idea their system was terrible.

So, on Stack Overflow, we say that teachers should teach good security practices, in order not to keep creating new security holes. It is an uphill battle at the best of times. If you follow the first link I supplied, you’ll find it’s pretty easy:

$stmt = mysqli_prepare(
    $conn,
    "SELECT * FROM `table_name` WHERE `thing_to_search` LIKE ?"
); 
mysqli_stmt_bind_param($stmt, 's', '%$qname%');
mysqli_stmt_execute($stmt);