How to use the MySQL database for your PHP applications. Due to the importantness of this topic, we have divided the tutorial into 3 parts. This tutorial is the third and last part of the tutorial on using MySQL in PHP, in which we will connect part of the store application that users see (front-end) to the database. Since in the previous tutorials we have fully trained CURD, this tutorial is nothing new and is just a completion of our previous work to finally have a small online store application + web-based admin panel ready to use. If you are familiar with these items, go to the end of this tutorial and see the next step. You can see a demo of what we will have after the end of this tutorial.
In the previous tutorial, our admin panel was completed, but our store still reads data from the Data.php file and is not connected to the database. Since our data is now registered in the database and ready to use, we want to make the store read its data from the database, and thus, we will no longer need the data.php file.
What you should know before starting?
In this tutorial, we will use and complete the same project we created in the previous tutorials. This tutorial is the third and final series of the PHP to MySQL connection tutorial, and to learn it you must start the course from the beginning, i.e. the first tutorial. You can see the first tutorial here: How to use MySQL database in your application?
Let's get started
First, we want to connect the first page of the store, index.php, to the database. See the home page of your store:
http://127.0.0.1/database
Products are displayed but generate information from the data.php file we created earlier. Open the index.php file. As you know, this page is used to display a list of products. So we need to display the list of data stored in the products table of the database. This is very similar to what we did in the products_read.php file in the admin panel. We can even open this file and use the code snippet and copy and paste it as much as possible, but due to the repetition of the tutorial, we try to write and describe the code once again. These are the codes we need then copy them on the top of the index.php file before $pageTitle = 'My Shop';
// include database connection
include 'config/db.php';
$out = '';
// select all data
$query = "SELECT id, title , price , photo FROM products ORDER BY id";
$stmt = $con->prepare($query);
$stmt->execute();
// this is how to get number of rows returned
$num = $stmt->rowCount();
//check if more than 0 record found
if($num>0) {
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)){
// creating new table row per record
$out .= '
<div class="col-xl-4 col-lg-6 col-md-6 col-sm-12">
<div class="card mb-3">
<img src="uploads/'.$row['photo'].'" class="card-img-top">
<div class="card-body">
<h3 class="card-title">'.$row['title'].'</h3>
<p class="card-text">$'.$row['price'].'</p>
<a href="details.php?id='.$row['id'].'"><button type="button" class="btn btn-success">Details</button></a>
</div>
</div>
</div>
';
}
// end products
} else { // if no records found
$out = '<div class="alert alert-danger">No records found.</div>';
}
We will now describe these codes. We are going to work with the database, so the first thing to do is to include the db.php file. Since in index.php we want to first get all the data we need and store it in variables and then display it where it is needed, i.e. lower, so we have to do all the work at the top and get the result, then save them to a variable like $out. So we defined an empty variable called $out that is going to be set later. Then we need a query to receive the data and a loop to store the data. We need id, title, price, and photo values on the home page, so we want them from the database in the query. The last part of the query, order by id, tells the database to sort the returned results by their id number. Sorting is used in many cases. For example, if we want to display products based on price on the first page, we can use order by price.
Then prepare and execute and if a return value is available, we use a while loop. This is the most important part of our code. In the while loop, we used exactly the same bootstrap card that we repeated below in the for-loop. As we said, each round of the loop returns a product. So if we repeat the Card here and build it every time with the data received from the database and then save it in a $out variable, it has exactly solved our need. The only important point is that if we set $out each time, the previous value will be deleted, but we need all the Cards. We used ".=" after $out. This means adding a new value to the previous values in $out. In short, it is the following code:
$out = $out + 'new data';
Finally, we said if the return value from the database was empty display the message “No records found”.
So far our data is ready to display. Now, all we have to do is display this $out value in the appropriate place at the bottom. So we delete the data.php file and the corresponding loop and just write:
<div class="row">
<?php echo $out; ?>
</div>
See the home page of your store now:
http://127.0.0.1/database
Exactly the same as the first, but this time the data is read from the database and not from the data.php file.
In the next step, we want to modify the details.php file. Open it. As you know, in this file, we display the information of a product based on its id value sent by "GET". So we need something exactly like the products_read_one.php file. Delete all code before including header.php and replace it with the following code:
// check for GET
if (isset($_GET['id'])) {
// include database connection
include 'config/db.php';
// read current record's data
try {
// prepare select query
$query = "SELECT title, description, price, photo FROM products WHERE id = :id LIMIT 0,1";
$stmt = $con->prepare( $query );
// posted values
$id = htmlspecialchars(strip_tags($_GET['id']));
// bind the parameters
$stmt->bindValue(':id', $id);
// execute our query
$stmt->execute();
// store retrieved row to a variable
$row = $stmt->fetch(PDO::FETCH_ASSOC);
// add data to $out
$out = '
<div class="card">
<div class="row no-gutters">
<div class="col-md-4">
<img src="uploads/'.$row['photo'].'" class="card-img">
</div>
<div class="col-md-8">
<div class="card-body">
<h2 class="card-title">'.$row['title'].'</h2>
<p class="card-text">'.$row['description'].'</p>
</div>
</div>
</div>
<div class="card-footer">$'.$row['price'].'</div>
</div>
';
}
// show error
catch(PDOException $exception){
die('ERROR: ' . $exception->getMessage());
}
} else {
die ("ERROR: ID not set.");
}
$pageTitle = $row['title'];
In the above code, we first checked the received id value sent by "GET". If available, we included the db.php file and wrote a query to retrieve the data related to the id in the products table. Then we used the received data to create a Card and saved it in the $out variable. This Card is exactly what it used to be here, but this time it was set with the values received from the database. Then we set the value of $pageTitle to the value of the "title" received from the database. We're done with this file because we've already displayed $out lower in the right place. Now click on one of the Details buttons on the main page of your store to see the product page. The difference is that this time the data is read from the database. It’s simple! Isn’t it?
Now all that remains is the pages.php file that must read the data from the database. Open the file. In this file, we have to get the data from the pages table exactly based on the submitted id. Then delete all the code before including header.php and replace the following code:
// check for GET
if (isset($_GET['id'])) {
// include database connection
include 'config/db.php';
// read current record's data
try {
// prepare select query
$query = "SELECT title, body FROM pages WHERE id = :id LIMIT 0,1";
$stmt = $con->prepare( $query );
// posted values
$id = htmlspecialchars(strip_tags($_GET['id']));
// bind the parameters
$stmt->bindValue(':id', $id);
// execute our query
$stmt->execute();
// store retrieved row to a variable
$row = $stmt->fetch(PDO::FETCH_ASSOC);
// add data to $out
$out = '
<div class="card">
<div class="card-body">
<h2 class="card-title">'.$row['title'].'</h2>
<p class="card-text">'.nl2br($row['body']).'</p>
</div>
</div>
';
}
// show error
catch(PDOException $exception){
die('ERROR: ' . $exception->getMessage());
}
} else {
die ("ERROR: ID not set.");
}
$pageTitle = $row['title'];
In the above code, we first checked the received id value sent by "GET" again. If available, we included the db.php file and wrote a query to retrieve the data related to the id in the pages table. Then we used the received data to create a Card and saved it in the $out variable. Then we set the value of $pageTitle to the value of the "title" received from the database, and that's it. This file is also ready and $out is displayed in the appropriate place at the bottom.
But there is still a small problem. Open the header.php file and see the About and Contact links in the navbar. We sent the words about and contact to the pages.php file as id. Because the data in the data.php file was defined with this key in the array. Now that it is stored in the database with numeric keys, we need to set the correct number corresponding to it. In the admin panel, we can see that the ID of "About Us" is 1 and the ID of "Contact Us" is 2. So the Navbar changes as follows:
<ul class="navbar-nav mr-auto">
<li class="nav-item active">
<a class="nav-link" href="index.php">Home</a>
</li>
<li class="nav-item">
<a class="nav-link" href="pages.php?id=1">About</a>
</li>
<li class="nav-item">
<a class="nav-link" href="pages.php?id=2">Contact</a>
</li>
</ul>
Now click on the links at the top of your store home page to see if everything works fine.
We finished our work and connected our store completely to the database. Now we can delete the data.php file from the project because we no longer need this file and it is not used anywhere. Also, the products folder is extra in the uploads folder where we previously copied the product photos, because in the admin panel we uploaded the photos and now they are available in the uploads folder and are displayed in the same way in the store.
You can now edit existing products and pages through the admin panel or add a new product or page and see the result in your store. The store is now a complete app and you can even use it commercially! Then everyone can access the admin panel and delete your products! 😁 Wait for the next tutorials, please...