Using php Ajax and a dropdown select menu to filter database data

Updated: 15-Jul-2022 / Tags: PHP and AJAX / Views: 6708 - Author: George

Introduction

Hello everyone.
In this tutorial we are going to use a select menu, to filter items displayed in the screen.
In web development terms this is a full stack tutorial. That means that we are gonna use html, css, javascript, Ajax, php, and mysql to create this little app.

Live Demo

Select an option to see the content changing, based on the option you have selected.

As you saw in the demo above, we have a page with a two column layout. On the left we have a select menu, which will act as a filter, and on the right side we displaying products that are coming from the database.

When we selecting an option, let say books, we are sending an Ajax request to a php file, and from there we perform a database query, and we get back all products that are listed as books.

We will see everything in detail. Lets begin with the projects folder.

Project's folder

Let's see what files we need to build our application.

\-- project-root
  |-- index.php		
  |-- styles.css		
  |-- functions.php		
  |-- script.php		
  |-- script.js
  
  |-- products
    |-- img-1.jpg
    |-- img-2.jpg
    |-- img-3.jpg
    ... more images
  • index.php, this is our app's page.
  • styles.css, our stylesheet to make things pretty, and readable.
  • functions.php, in this file we are going to write the functions that we need to make things work on the back-end.
  • The script.php file will handle the Ajax request, when we choose an option.
  • And in the script.js file we will write the Ajax request.
  • Next we have a products folder where all the products images are stored. Every image is associated with a product.

Those are all the files we need, now let's start the tutorial by looking at the database's products table.

The database table

This is the products table.
Every product has an id, a title, an image, a price, a description, and a category column.
Those 4 products are an example on how the products table looks like, there are like 20 products in the real table, which you get if you download the source code.

id title image price description category
1 Node.js: Novice to Ninja 1st Edition products/node.jpg 39.95 Some dummy text. books
2 Star Wars Squadrons products/star-wars.jpg 39.95 Some dummy text. games
3 SAMSUNG Galaxy S22 Ultra products/phone-1.jpg 1.136 Some dummy text. phones
4 Rode PodMic Cardioid Dynamic products/mic-1.jpg 99 Some dummy text. microphones

Now let's go to the index.php file.

The index file

In the index file, we have a basic html structure, with a link to the css file in line 7, and a script tag to link the javascript file in line 13.
Another important thing here is that we include the functions.php file at the top of the document. So every time we load the index file in the browser the functions.php file will run also.

<?php include "functions.php" ?>
<!DOCTYPE html>
<html lang="en">
<head>
	<meta charset="UTF-8">
	<meta name="viewport" content="width=device-width, initial-scale=1.0">
	<link rel="stylesheet" href="styles.css">
	<title>Start up</title>
</head>
<body>

	<!-- Link to the javascript file -->
	<script src="script.js"></script> 
</body>
</html>

Inside the body tags i will have a div element with a class of "page" in line 11. This element will hold all the html we need for our little app.

Inside the "page" element i will create two more elements, a div element with a class of "left", and another div element with a class of "right".

I will create a two column page layout from those two divs in the css file.

<body>
	<div class="page">
		<div class="left"></div>
		<div class="right"></div>
	</div>

	<script src="script.js"></script>
</body>

The left section

Inside the left section we are going to have the select menu, which will act as a filter. We saw this in the demo.

The important stuff here is the id="products", we are going to use the id to target the select menu in the javascript file, so we can get the selected option value.

	<div class="left">
		<select name="products" id="products">
			<option value="">All Products</option>
			<option value="books">Books</option>
			<option value="games">Games</option>
			<option value="tablets">Tablets</option>
			<option value="phones">Phones</option>
			<option value="microphones">Microphones</option>
		</select>
	</div>	

Another important thing is that the option values have to match exact with the category of each product in the database.

Example: The string "books" has to be the same in the option value="books", as in the category column in the products table.

And in line 15 in the first option element we have an empty value, value="", we are going to use this empty value in the php file when we get there, keep that in mind.

The right section

Now let's go to the right section and fetch all the products that we have in the database.

	<div class="right">
		<h2>All Products</h2>
		<div class="product-wrapper">
			
		<?php 
			$products = getAllProducts();
			foreach ($products as $product) {
				?>
					<div class="product">
						<div class="left">
							<img src="<?php echo $product['image'] ?>" alt="">
						</div>
						<div class="right">
							<p class="title"><?php echo $product['title'] ?></p>
							<p class="price"><?php echo $product['price'] ?>€</p>
							<p class="description"><?php echo $product['description'] ?></p>
						</div>
					</div>
				<?php
			}
		?>

		</div>
	</div>

Code breakdown

  • In line 15 we have an h2 tag, and inside the tag we have the text "All Products". The text inside the tag is going to change based on the selection we make in the select menu.

    Example: If we selecting "books" as an option the text inside the h2 tag will change also to "books".

  • In line 16 we have a div tag with a class of "product-wrapper". The only reason that this element exists in our code is that we are going to target it from the javascript file, and replace the content every time we choose an option from the select menu.

    <div class="product-wrapper">
  • In line 18 we are opening php tags to write the php code. And in line 19 we are executing a function named getAllProducts(). This function does not exist yet, we are going to write the function later on, in the functions.php file.

    $products = getAllProducts();

    The getAllProducts() function will return an associative array holding all the products from the products table in a random order.

  • In line 20, we are going to loop through the $products array to fetch every product, and display its properties in an html template.

    foreach ($products as $product) {

  • Lets see what is happening inside the html template in line 22.

    <div class="product">
    	<div class="left">
    		<img src="<?php echo $product['image'] ?>" alt="">
    	</div>
    	<div class="right">
    		<p class="title"><?php echo $product['title'] ?></p>
    		<p class="price"><?php echo $product['price'] ?>€</p>
    		<p class="description"><?php echo $product['description'] ?></p>
    	</div>
    </div>
    		

    The product element is divided in two columns, left, and right. On the left we are going to display the product's image. And on the right we are going to display the title, the price, and the product's description.

    This is all the code that we have in the html file, now let's go to the functions.php file and write the functions that we need.

The functions php file

We are going to write three functions in the functions.php file.

<?php 
function connect(){
	$mysqli = new mysqli('localhost', 'admin', 'admin_pass', 'tutorials');
	if($mysqli->connect_errno != 0){
		return $mysqli->connect_error;
	}else{
		$mysqli->set_charset("utf8mb4");	
	}
	return $mysqli;
}

function getAllProducts(){
	$mysqli = connect();
	$res = $mysqli->query("SELECT * FROM products ORDER BY RAND()");
	while($row = $res->fetch_assoc()){
		$products[] = $row;
	}
	return $products;
}

function getProductsByCategory($category){
	$mysqli = connect();
	$res = $mysqli->query("SELECT * FROM products WHERE category = '$category'");
	while($row = $res->fetch_assoc()){
		$products[] = $row;
	}
	return $products;
}

Code breakdown

  • The first function that we have is the connect() function. This function will connect PHP with the MySQL server.

    function connect(){
    	$mysqli = new mysqli('localhost', 'admin', 'admin_pass', 'tutorials');
    	if($mysqli->connect_errno != 0){
    		return $mysqli->connect_error;
    	}else{
    		$mysqli->set_charset("utf8mb4");	
    	}
    	return $mysqli;
    }
    		

    In line 3 we creating a new mysqli object to connect to the database. You have to pass in the constructor your mysql connection details that is: ('Your server's name, 'your mysql username', 'your mysql password', 'database name').

    Next in line 4 we are going to check the connect_errno (connect error number), property, and if the property returns anything else than zero, we are going to return the mysql connect error so we can have a clue on what went wrong.

    If there is no error i will set the character encoding in line 7, and i will return the mysqli object in line 9.

    We are gonna use the connect function everytime we want to perform a database query. You will see this in the next function.

  • Our second function is the getAllProducts() function. As the name implies we are going to fetch all products from the "producst" table.

    function getAllProducts(){
    	$mysqli = connect();
    	$res = $mysqli->query("SELECT * FROM products ORDER BY RAND()");
    	while($row = $res->fetch_assoc()){
    		$products[] = $row;
    	}
    	return $products;
    }
    		

    The first thing we do inside the function is to connect to the database, so we use the connect() function and store the returned mysqli object in a variable named $mysqli. We can give the variable any name we like.

    In line 14 we perform a select query to fetch the products in a random order using the 'OREDER BY RAND()' directive.

    In line 15 we loop through the returned result set and fetch every table row as an associative array with the fetch_assoc() function.

    In line 16 we creating a new associative array named $products, which will hold all the products.

    And in line 18 the function will return the $products array.

  • Our last function is the getProductsByCategory() function. The only difference from the previous function is that this function takes a parameter which is the selected category (the selected option value).

    function getProductsByCategory($category){
    	$mysqli = connect();
    	$res = $mysqli->query("SELECT * FROM products WHERE category = '$category'");
    	while($row = $res->fetch_assoc()){
    		$products[] = $row;
    	}
    	return $products;
    }
    		

    So we use the parameter to fetch all the products from the database who have the same category as our parameter. Example: WHERE category = 'books'.

    There is nothing more in the file, now let's move to the javascript file, that is the script.js file.

The javascript file

Let's set some global variables first in the javascript file.

In line 1 we access the select menu option, by targeting the element's id.

In line 2 we access the h2 element in which we are going to display the selected category.

And in line 3 we access the products container. This is the container in which we display the fetched products.

let selectMenu = document.querySelector("#products");
let category = document.querySelector(".right h2");
let container = document.querySelector(".product-wrapper");

selectMenu.addEventListener("change", function(){
	let categoryName = this.value;
	category.innerHTML = this[this.selectedIndex].text;  

	let http = new XMLHttpRequest();
	// We are going to fetch the server's response later on.

	http.open('POST', "script.php", true);
	http.setRequestHeader("content-type", "application/x-www-form-urlencoded");
	http.send("category="+categoryName);
});

Code breakdown

  • In line 5 we assign a "change" event-listener to the select menu, so every time we selecting an option a function will run.
    selectMenu.addEventListener("change", function(){
  • In line 6 we fetch the selected option's value.
    let categoryName = this.value;
  • In line 7 we fetch the selected option's text, and we display it inside the h2 tag.

    category.innerHTML = this[this.selectedIndex].text;
  • In line 9, we creating a new xml-http-request object to send the the selected option's value to the server and the php file.

    let http = new XMLHttpRequest();
  • In line 12 we use the open() method to prepare the http-request.

    http.open('POST', "script.php", true);

    The function takes three arguments. In the first argument we declare the http-method, in the second argument we declare the php file which handles the request. And the keyword true tells the javascript to handle the request asynchronous.

  • In line 13 we set the content-type header to "application/x-www-form-urlencoded" this will send the data to the server in a key, value pair format, as an html form would.

    http.setRequestHeader("content-type", "application/x-www-form-urlencoded");
  • And last in line 14 we send the request to the server with the send() method. You see that we are structuring the data that we send in a key, value pair format.

    http.send("category="+categoryName);

    Now we have to go to the script.php file to handle the request.

The script.php file

The first thing that we do in the file is to get access to the functions.php file. We need access to the functions that we wrote earlier so we can use them here in the script.php file.

So in line 2 we use the require statement to load our functions.

<?php 
	require "functions.php";

	if(isset($_POST['category'])){
		$category = $_POST['category'];

		if($category === ""){
			$products = getAllProducts();
		}else{
			$products = getProductsByCategory($category);
		}
		echo json_encode($products);
	}

Code breakdown

  • In line 4 we check if there is an incoming POST request, and if the $POST array, is holding the "category" key, that we send from the javascript file.

  • If this is true, in line 5, we create a variable named $category and assign the selected option value. Remember this is what the javascript file sends, the selected option's value.
  • Now in line 7 we check the $category 's value. If the value is an empty string "", we run the getAllProducts() function and store the returned data in the $products variable.
    We know that the getAllProducts() function is returning an associative array holding all the products from the database table.
  • Else in line 9, if the $category variable is holding a value, let's say "books", we execute the getProductsByCategory() function and fetch all the books from the database table, and store the result again in the $products array variable.
  • And last in line 12, we use the json_encode() function to convert the $products to a json format. And with the echo statement we sending to the javascript file the server's request.

    Now we have to do a last thing, and go back to the javascript file and catch the server's response.

Back to the javascript file

We are back in the javascript file and we are going to write the code to catch the server's response, between the new XMLHttpRequest() object, and the open() method.

let http = new XMLHttpRequest();
	
http.onreadystatechange = function(){
	if(this.readyState == 4 && this.status == 200){
		let response = JSON.parse(this.responseText);
		let out = "";
		for(let item of response){
			out += `
				<div class="product">
					<div class="left">
						<img src="${item.image}" alt="">
					</div>
					<div class="right">
						<p class="title">${item.title}</p>
						<p class="price">${item.price}€</p>
						<p class="description">${item.description}</p>
					</div>
				</div>
			`;
		}
		container.innerHTML = out;
	};
}

http.open('POST', "script.php", true);

Code breakdown

  • In line 11 we use the onreadystatechange event-listener, so we can check in line 12 the readyState and the status properties. To have a successful response the readyState value has to be 4, and the status value 200.
    http.onreadystatechange = function(){
    	if(this.readyState == 4 && this.status == 200){
    		
  • Next in line 13 we use the JSON.parse() function to convert the data from the php file to a javascript object so we can work with.
    The responseText property is holding the server's response data.

    		let response = JSON.parse(this.responseText);
  • In line 14 we creating a variable named out. I am going to use this variable inside a loop.

    		let out = "";
  • Next i am going to loop through the response, and a will display every product in an html template. Which template is added to the out variable.

    		for(let item of response){
    			out += `
    				<div class="product">
    				   <div class="left">
    				      <img src="${item.image}" alt="">
    				   </div>
    				   <div class="right">
    				      <p class="title">${item.title}</p>
    				      <p class="price">${item.price}€</p>
    				      <p class="description">${item.description}</p>
    				   </div>
    				</div>
    				`;
    			}
    		
  • And last in line 29, and outside the for of loop we are going to target the products container and replace the current displayed products, with the new fetched ones.

    container.innerHTML = out;

Summary

We saw how to use a drop down select menu to fetch data from the database using an AJAX request.

And that's it guys, we reached the end of this tutorial, i hope everything is understandable, if not you can leave a comment on which part was difficult to understand so i can re-write that part.

Last Words

Thanks for reading, i hope you find the article helpful.
Please leave a comment if you find any error's, so i can update the page with the correct code.

Source code

If you feel like avoiding all the copying and pasting, you can buy me a coffee and get the source code in a zip file. Get the source code

Buy me a coffee

If you like to say thanks, you can buy me a coffee.

Buy me a coffee with paypal