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. In the second part, in this part of the tutorial, we will learn Read, Update, and Delete in the MySQL database. So we learn how to perform CURD operations in PHP and MySQL, and through it, we complete our store admin panel. If you are familiar with these, 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 training course.
This tutorial is the second of three tutorials on "How to use MySQL database in your app?" In the previous tutorial, we created the database and tables needed for our store in MySQL and added our pages and products to the database through the app admin panel. In this tutorial, we want to see the added pages and products in the admin panel, and if necessary, we can edit or delete them. In this way, our admin panel will be complete and ready to use.
What you should know before starting?
In this tutorial, we will use the same project that we created in the previous tutorials and complete it. This tutorial is the second in a series of PHP&MySQL tutorials, and you'll need to start the course from the first part to learn the topic. You can see the first tutorial here: How to use the MySQL database in your app?
Let's get started
First of all, let's take a look at the two files that are currently in the admin folder. In the header and footer section of these two files, all the code is duplicated and is actually related to loading Bootstrap. So why not use include here? Just like we did in our store before, we can create two header.php and footer.php files here and put duplicate code in those files. Then include them in the main file so that both the size of our files is reduced and we have less problem editing header and footer codes in the future, such as the links in the navbar. Then create two files named header.php and footer.php in the admin folder and copy related code and then include them in pages_create.php and products_create.php. Your code will be as follows:
header.php
<!doctype html>
<html lang="en">
<head>
<!-- Required meta tags -->
<meta charset="utf-8">
<meta name="viewport" content="width=device-width, initial-scale=1, shrink-to-fit=no">
<!-- Bootstrap CSS -->
<link rel="stylesheet" href="https://stackpath.bootstrapcdn.com/bootstrap/4.4.1/css/bootstrap.min.css" integrity="sha384-Vkoo8x4CGsO3+Hhxv8T/Q5PaXtkKtu6ug5TOeNV6gBiFeWPGFN9MuhOf23Q9Ifjh" crossorigin="anonymous">
<title>Admin Panel</title>
<style>
footer {
padding-top: 3rem;
padding-bottom: 3rem;
}
footer p {
margin-bottom: .25rem;
}
</style>
</head>
<body>
<header>
<nav class="navbar navbar-expand-lg navbar-dark bg-dark">
<div class="container">
<a class="navbar-brand" href="#">Admin Panel</a>
<button class="navbar-toggler" type="button" data-toggle="collapse" data-target="#navbarText" aria-controls="navbarText" aria-expanded="false" aria-label="Toggle navigation">
<span class="navbar-toggler-icon"></span>
</button>
<div class="collapse navbar-collapse" id="navbarText">
<ul class="navbar-nav mr-auto">
<li class="nav-item active">
<a class="nav-link" href="#">Dashboard</a>
</li>
<li class="nav-item">
<a class="nav-link" href="#">Products</a>
</li>
<li class="nav-item">
<a class="nav-link" href="#">Pages</a>
</li>
<li class="nav-item">
<a class="nav-link" href="#">Setting</a>
</li>
</ul>
</div>
</div>
</nav>
</header>
footer.php
<footer class="text-muted">
<div class="container">
<p class="float-right">
<a href="#">Back to top</a>
</p>
<p>MyShop © YourDomain.com, The online shop which I created myself with ❤</p>
<p>Need help? <a href="https://www.mojipo.com/">Visit the homepage</a> or read our <a href="https://www.mojipo.com/myshop">getting started guide</a>.</p>
</div>
</footer>
<!-- Optional JavaScript -->
<!-- jQuery first, then Popper.js, then Bootstrap JS -->
<script src="https://code.jquery.com/jquery-3.4.1.slim.min.js" integrity="sha384-J6qa4849blE2+poT4WnyKhv5vZF5SrPo0iEjwBvKU7imGFAV0wwj1yYfoRSJoZ+n" crossorigin="anonymous"></script>
<script src="https://cdn.jsdelivr.net/npm/popper.js@1.16.0/dist/umd/popper.min.js" integrity="sha384-Q6E9RHvbIyZFJoft+2mJbHaEWldlvI9IOYy5n3zV9zzTtmI3UksdQRVvoxMfooAo" crossorigin="anonymous"></script>
<script src="https://stackpath.bootstrapcdn.com/bootstrap/4.4.1/js/bootstrap.min.js" integrity="sha384-wfSDF2E50Y2D1uUdj0O3uMBJnjuUD4Ih7YwaYd1iqfktj0Uod8GCExl3Og8ifwB6" crossorigin="anonymous"></script>
</body>
</html>
pages_create.php
<?php
include ("header.php");
?>
<main class="bg-light">
<div class="jumbotron">
<div class="container">
<h1>Create Pages</h1>
</div>
</div>
<div class="container py-3">
<?php
if($_POST){
// include database connection
include '../config/db.php';
try{
// insert query
$query = "INSERT INTO pages SET title=:title, body=:body";
// prepare query for execution
$stmt = $con->prepare($query);
// posted values
$title=htmlspecialchars(strip_tags($_POST['title']));
$body=htmlspecialchars(strip_tags($_POST['body']));
// bind the parameters
$stmt->bindValue(':title', $title);
$stmt->bindValue(':body', $body);
// Execute the query
if($stmt->execute()){
echo "<div class='alert alert-success'>Record was saved.</div>";
}else{
echo "<div class='alert alert-danger'>Unable to save record.</div>";
}
}
// show error
catch(PDOException $exception){
die('ERROR: ' . $exception->getMessage());
}
}
?>
<a href="pages_read.php" class="btn btn-secondary float-right mb-2">Back to read pages</a>
<!-- html form here where the product information will be entered -->
<form action="pages_create.php" method="post">
<table class="table table-hover table-bordered">
<tr>
<td>Title</td>
<td><input type="text" name="title" class="form-control" /></td>
</tr>
<tr>
<td>Body</td>
<td><textarea name="body" class="form-control"></textarea></td>
</tr>
<tr>
<td></td>
<td>
<input type="submit" value="Create" class="btn btn-primary" />
</td>
</tr>
</table>
</form>
</div>
</main>
<?php
include ("footer.php");
?>
products_create.php
<?php
include ("header.php");
?>
<main class="bg-light">
<div class="jumbotron">
<div class="container">
<h1>Create Product</h1>
</div>
</div>
<div class="container py-3">
<?php
if($_POST){
// check and upload photo first
// include uploader class
include '../libs/class.upload.php';
if ($_FILES["photo"]["error"] == 0) {
$handle = new Upload($_FILES['photo']);
if ($handle->uploaded) {
$handle->allowed = array('image/*');
$handle->Process('../uploads/');
if ($handle->processed) {
$photo = $handle->file_dst_name; // set $photo with file name
} else {
echo '<div class="alert alert-info">'.$handle->error.'</div>';
$photo = 'default.jpg'; // set $photo to default.jpg
}
$handle-> Clean();
} else {
echo '<div class="alert alert-info">'.$handle->error.'</div>';
$photo = 'default.jpg'; // set $photo to default.jpg
}
} else {
echo '<div class="alert alert-warning">Photo not selected!</div>';
$photo = 'default.jpg'; // set $photo to default.jpg
}
// then insert data to database
// include database connection
include '../config/db.php';
try{
// insert query
$query = "INSERT INTO products SET title=:title, description=:description, price=:price, photo=:photo, created=:created";
// prepare query for execution
$stmt = $con->prepare($query);
// posted values
$title=htmlspecialchars(strip_tags($_POST['title']));
$description=htmlspecialchars(strip_tags($_POST['description']));
$price=htmlspecialchars(strip_tags($_POST['price']));
// bind the parameters
$stmt->bindValue(':title', $title);
$stmt->bindValue(':description', $description);
$stmt->bindValue(':price', $price, PDO::PARAM_INT);
$stmt->bindValue(':photo', $photo);
// specify when this record was inserted to the database
$created=date('Y-m-d H:i:s');
$stmt->bindValue(':created', $created);
// Execute the query
if($stmt->execute()){
echo "<div class='alert alert-success'>Record was saved.</div>";
}else{
echo "<div class='alert alert-danger'>Unable to save record.</div>";
}
}
// show error
catch(PDOException $exception){
die('ERROR: ' . $exception->getMessage());
}
}
?>
<a href="products_read.php" class="btn btn-secondary float-right mb-2">Back to read products</a>
<!-- html form here where the product information will be entered -->
<form action="products_create.php" method="post" enctype="multipart/form-data">
<table class="table table-hover table-bordered">
<tr>
<td>Title</td>
<td><input type="text" name="title" class="form-control" /></td>
</tr>
<tr>
<td>Description</td>
<td><textarea name="description" class="form-control"></textarea></td>
</tr>
<tr>
<td>Price</td>
<td><input type="text" name="price" class="form-control" /></td>
</tr>
<tr>
<td>Photo</td>
<td><input type="file" name="photo" /></td>
</tr>
<tr>
<td></td>
<td>
<input type="submit" value="Create" class="btn btn-primary" />
</td>
</tr>
</table>
</form>
</div>
</main>
<?php
include ("footer.php");
?>
Run the URL of the files again to make sure everything is working properly as before. Now we can move on.
Read all Pages table records from the database
Create a new file named pages_read.php in the admin folder and put the following code in it.
<?php
include ("header.php");
?>
<main class="bg-light">
<div class="jumbotron">
<div class="container">
<h1>Read Pages</h1>
</div>
</div>
<div class="container py-3">
<!-- PHP code will be here -->
</div>
</main>
<?php
include ("footer.php");
?>
Then run the following address to make sure everything is in place.
http://127.0.0.1/database/admin/pages_read.php
This page is supposed to be the page where we see the pages that we previously added to the pages table in the database. There is currently no PHP code on this page. To read the information from the database, replace the following code with <!-- PHP code will be here -->
<?php
// include database connection
include '../config/db.php';
// delete codes will be here
// button for create page
echo '<a href="pages_create.php" class="btn btn-primary float-right mb-2">Create New Page</a>';
// select all data
$query = "SELECT id, title FROM pages ORDER BY id DESC";
$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) {
// data from database will be here
echo '<table class="table table-hover table-bordered">';//start table
//creating our table heading
echo '<tr>';
echo '<th>ID</th>';
echo '<th>Title</th>';
echo '<th>Action</th>';
echo '</tr>';
// retrieve our table contents
while ($row = $stmt->fetch(PDO::FETCH_ASSOC)){
// creating new table row per record
echo '<tr>';
echo '<td>'.$row['id'].'</td>';
echo '<td>'.$row['title'].'</td>';
echo '<td>';
// read one record
echo '<a href="pages_read_one.php?id='.$row['id'].'" class="btn btn-info btn-sm mr-1 mb-1">Read</a>';
// update one record
echo '<a href="pages_update.php?id='.$row['id'].'" class="btn btn-primary btn-sm mr-1 mb-1">Edit</a>';
// delete one record but with confirmation warning
echo '<a href="#" data-href="pages_read.php?delete='.$row['id'].'" data-toggle="modal" data-target="#confirm-delete" class="btn btn-danger btn-sm mb-1">Delete</a>';
echo '</td>';
echo '</tr>';
}
// end table
echo '</table>';
} else { // if no records found
echo '<div class="alert alert-danger">No records found.</div>';
}
?>
Run this file again. As you can see, a list of the information we previously entered in the pages table is displayed here. That is, we see the two pages About Us and Contact us that we created earlier, and we have buttons to view, edit, and delete each one. Of course, these buttons do not work at the moment, and we will continue to teach them to do their job properly.
First, let's look at what happened to the PHP code we entered in the file. First, as usual, because we want to use the database, we included the db.php file. Then we wrote a query that asks the database to read all the id and title values from the pages table in the database and return the result. If you remember, we also had a body in the pages table, but we didn't want to read the values here. We'll talk about that later. Then we prepared and execute. In the next line, we used rowCount(). This method actually returns the number of rows of the database read by our command. This means that the value of this method is always a number that gives us the number of returned lines. We put this number in the $num variable. Then we have a conditional structure that checks the $num. if the value was greater than 0 then it does some job for us, and if not, show the message "No records found". Now if our page table was empty, this message would be displayed, but here the $num value is greater than 0 because we had 2 rows in the table, one for About us and the other for Contact us. Therefore, the written commands are executed in the conditional structure. But what are these commands?
These commands first create an HTML table for us and set the table header value. Then we have a While loop. The loops have different modes in PHP. You are already familiar with the For loop. Here we used the While loop. This loop model repeats the command for us until a condition is met, and the loop ends when that condition is no longer met. The condition we used here is:
($row = $stmt->fetch(PDO::FETCH_ASSOC))
This is a command that we should always use to retrieve data from the database so that we can access each of the rows returned in the table. Each time we repeat this loop, we have a row of tables. Since we want to show the rows of the table, all we have to do is use echo inside the loop and display the information in the database rows by creating the middle part of the HTML table. The values in the table rows are in the $row variable, which is an array, and the access key for it is the name of the table columns, id and title, which we wanted to return to in the query. First, we display the id and title for each row, and then we create the value for a column called Action, which we created in the HTML table, with 3 buttons for Read, Edit, and Delete the same row of data read from the database.
Note the links we created for the Read and Edit buttons. It refers to PHP files that do not yet exist, and we will create these files later. The important thing is that we have sent the id value of each row with GET method to these new files that we are going to create. This is exactly the key to accessing the data in the same row from the database table, and in the new pages, we use this key to access related data.
In the Delete link, however, it is slightly different. The reason for this difference is that if we click on the Delete button, before deleting the line, first ask us again if we are sure of deleting? Because the deletion operation is irreversible, and if a line is deleted from the database, its information can no longer be recovered. We do this with Modal, which is one of the Bootstrap tools that you will see below.
Then we exit the while loop, and here the only thing we did was close our HTML table with </table>. In this way, we created a dynamic HTML table that has as many rows as the number of rows returned from our database, and you can see the result. Now, why didn't we read the body from the database table from the beginning? In fact, we could do that, and like id and title, display the body value in the HTML table. But it does require a lot of data from the database, which slows us down a bit. We just want to show a list of the data in the database table, so id and title are enough for us here. We will receive the text in the body for each line that we want later in the Read One or Update section in the following tutorial because we need it there, but there is no reason to display it here. Whenever you want to display a list of database data, keep in mind to increase the performance of your application.
Always try to read only what you need from the database.
Delete a record from the pages table in the database
In this part of the tutorial we want to make our Delete button work properly. To do this, replace the following code with: // delete codes will be here
if (isset($_GET['delete'])) {
try {
// delete query
$query = "DELETE FROM pages WHERE id = :id";
$stmt = $con->prepare($query);
// posted values
$delete = htmlspecialchars(strip_tags($_GET['delete']));
// bind the parameters
$stmt->bindValue(':id', $delete);
if($stmt->execute()) {
// redirect to read records page and
// tell the user record was deleted
header('Location: pages_read.php?message=deleted');
} else {
echo "<div class='alert alert-danger'>Unable to delete record.</div>";
}
}
// show error
catch(PDOException $exception){
die('ERROR: ' . $exception->getMessage());
}
}
if (isset($_GET['message']) && $_GET['message'] == 'deleted') {
echo "<div class='alert alert-success'>Record was deleted.</div>";
}
This code is two conditional structures in a row. In the first case, we say that if a value named delete with GET method is sent to this page, do the following commands. If you remember, in the Delete link a little higher, we set this value with the id of each row we took from the database and send it to our file. I mean:
echo '<a href="#" data-href="pages_read.php?delete='.$row['id'].'" data-toggle="modal" data-target="#confirm-delete" class="btn btn-danger btn-sm mb-1">Delete</a>';
Note this part:
pages_read.php?delete='.$row['id'].'
In fact, the loop says that if the user clicks on any of the Delete keys, follow the instructions below. In the commands, we have a Try and Catch structure in which we run a query. The query says that from the pages table in the database, delete the line whose id value is equal to the value sent by the delete. This is the same line that the user clicks on the Delete button at that level. Note that in the posted values section we have received the delete value as GET. In this way, we identify which button the user clicks on and delete the corresponding row from the database. That is, we use the id key registered in the pages table to identify.
Finally, we have said that if the deletion is done successfully, redirect us to a new address by the Header command. The new address is:
pages_read.php?message=deleted
In the next if, we have said that if a message called GET with the GET method is sent to this page and this value is equal to "deleted", display the message "Record was deleted". This means that if the above address is called, display the message "Record was deleted". We saw that the above address is called only when a record has been deleted.
These codes were for deleting from the database but it was still part of the work. We defined the Delete link in such a way that after the user clicks, the command is not executed directly and first receives a confirmation from the user and we want to do this with Modal. If you don't know what Modal is, see the link below:
https://getbootstrap.com/docs/4.4/components/modal
The modal displays a beautiful interactive message to the user. We designed a Modal to get Confirm from the user using the tips in the above URL. You need to put the code at the bottom of the page after </main> and before including footer.php.
<!-- Modal is here-->
<div class="modal fade" id="confirm-delete">
<div class="modal-dialog">
<div class="modal-content">
<div class="modal-header">
Confirm Delete
</div>
<div class="modal-body">
<p>Are you sure?</p>
</div>
<div class="modal-footer">
<button type="button" class="btn btn-secondary" data-dismiss="modal">Cancel</button>
<a class="btn btn-danger btn-ok">Delete</a>
</div>
</div>
</div>
</div>
We also need a jQuery code snippet that helps our Modal work properly. This small code snippet must be placed in the footer.php file just before </body>.
<script>
$('#confirm-delete').on('show.bs.modal', function(e) {
$(this).find('.btn-ok').attr('href', $(e.relatedTarget).data('href'));
});
</script>
That's it. Now let's try the Delete button does it work properly? First, add a test data because we don't want our previous data to be deleted. Go to this page and add a test data:
http://127.0.0.1/database/admin/pages_create.php
Now go back to our page.
http://127.0.0.1/database/admin/pages_read.php
Click the Delete button on the test data you added. If everything is correct, you should see the following page:
A message will be displayed that is your Modal. If you click on the red Delete button in this message, your record will be deleted from the database.
Read a record from the pages table in the database
In the following, we want you to make the Read button work correctly for each record. Create a new file named pages_read_one.php in the admin folder and put the following code in it.
<?php
include ("header.php");
?>
<main class="bg-light">
<div class="jumbotron">
<div class="container">
<h1>Read One Page</h1>
</div>
</div>
<div class="container py-3">
<?php
// isset() is a PHP function used to verify if a value is there or not
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);
// values to fill up our table
$title = $row['title'];
$body = $row['body'];
}
// show error
catch(PDOException $exception){
die('ERROR: ' . $exception->getMessage());
}
} else {
die ("ERROR: ID not set.");
}
?>
<a href="pages_read.php" class="btn btn-secondary float-right mb-2">Back to read pages</a>
<table class="table table-hover table-bordered">
<tr>
<td>ID</td>
<td><?php echo $id;?></td>
</tr>
<tr>
<td>Title</td>
<td><?php echo $title;?></td>
</tr>
<tr>
<td>Body</td>
<td><?php echo nl2br($body);?></td>
</tr>
</table>
</div>
</main>
<?php
include ("footer.php");
?>
Let's see what happens in these codes. First, there is a conditional structure that checks whether data called id has been sent to this file by GET. If it is sent, some code will be executed and if not, an error "ERROR: ID not set." Displayed by the die() function. The die() function is one of the PHP functions that can display a text and then stop running the rest of the code written from that line onwards.
If the id is sent, db.php is included first. Then we have a query that tells the database to return the title and body values of a row of pages whose id value is ":id". LIMIT 0.1 actually limits the number of lines to be read from the database and says to return only one line with these specifications. Since the id of each line is unique and not duplicate, the result will not be more than one line and we could not write this, but we wrote it for your acquaintance. We will need it later in the next tutorial. Then prepare and bind the id with the value sent by the GET method and finally execute. Here again we used
row = $stmt->fetch(PDO::FETCH_ASSOC)
structure as before.
It means we stored the returned values of the database in the $row variable, but since we know that our return value will be only one line, there was no need to use the while loop. Now we can read the return values from $row. As we said before, $row will be an array, and the key to accessing that data is the name of the columns in our table that we requested in the query. So we took this data and put each one in a variable called $title and $body. Finally, you can see that using an HTML table and using echo, we displayed the values of these variables. Now see the following address and click on one of the Read buttons to see the result.
http://127.0.0.1/database/admin/pages_read.php
The result will display the data stored in the same row. In this way, we obtained a one-line data from the pages table using its id. This will be used later on the product screen in our store, which you will see.
Edit a record from the pages table in the database
The last thing we need to do is make the Edit button work for each page. With the Edit button, we can edit the values we previously entered in the pages table. This means, for example, if our email address changes, we can easily edit the Contact us page and change our email. This section is the same as the Update in CURD. If you think about it, you will find that you need to read the recorded information from the database first to edit what was previously recorded. That's exactly what we did in the previous section. However, this information is loaded into an HTML form that can be modified and re-registered. To start, create a new file named pages_update.php in the admin folder and put the following code in it:
<?php
include ("header.php");
?>
<main class="bg-light">
<div class="jumbotron">
<div class="container">
<h1>Edit Page</h1>
</div>
</div>
<div class="container py-3">
<?php
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);
// values to fill up our form
$title = $row['title'];
$body = $row['body'];
}
// show error
catch(PDOException $exception){
die('ERROR: ' . $exception->getMessage());
}
} else {
die ("ERROR: ID not set.");
}
if($_POST){
try{
// insert query
$query = "UPDATE pages SET title=:title, body=:body WHERE id = :id";
// prepare query for execution
$stmt = $con->prepare($query);
// posted values
$title=htmlspecialchars(strip_tags($_POST['title']));
$body=htmlspecialchars(strip_tags($_POST['body']));
// bind the parameters
$stmt->bindValue(':title', $title);
$stmt->bindValue(':body', $body);
$stmt->bindValue(':id', $id);
// Execute the query
if($stmt->execute()){
echo "<div class='alert alert-success'>Record was updated.</div>";
}else{
echo "<div class='alert alert-danger'>Unable to updated record.</div>";
}
}
// show error
catch(PDOException $exception){
die('ERROR: ' . $exception->getMessage());
}
}
?>
<a href="pages_read.php" class="btn btn-secondary float-right mb-2">Back to read pages</a>
<!-- html form here where the product information will be entered -->
<form action="pages_update.php?id=<?php echo $id;?>" method="post">
<table class="table table-hover table-bordered">
<tr>
<td>Title</td>
<td><input type="text" name="title" class="form-control" value="<?php echo $title;?>" /></td>
</tr>
<tr>
<td>Body</td>
<td><textarea name="body" class="form-control"><?php echo $body;?></textarea></td>
</tr>
<tr>
<td></td>
<td>
<input type="submit" value="Edit" class="btn btn-primary" />
</td>
</tr>
</table>
</form>
</div>
</main>
<?php
include ("footer.php");
?>
In this code, we first have a conditional structure that is exactly the copy of the previous series, I mean it takes an id through the GET method and reads and returns the corresponding data from the database. Instead of putting this data in a table, we display it in an HTML form. Then we have another conditional structure that checks whether data is sent to the page as a POST.
POST data is sent only when the user has submitted the form on the page. If this data is available, it executes a query that asks the database to update the pages table and change the title and body values of the row whose id is equal to :id to the new values. Then we prepare the query and bind : title, :body and :id. $title and $body are the same new values that were sent by post by filling out the form, and $id was initially received as a GET in the first conditional structure. Then we execute and if the result is successful, the "Record was updated" message is shown. Finally, when we have the same HTML form and display the data by echo, we have embedded a button for submit the form.
Now see the following address and click on one of the Edit buttons to see the result.
http://127.0.0.1/database/admin/pages_read.php
The result is that the data in the same row will be displayed in a form, and if we make a change in the fields and click on Edit button, we will edit the data.
All the work related to the pages table has been done and now we can easily add, edit and delete any data we want in this table. Now we have to do all this for the products table so that our admin panel is complete and ready to use.
Read all the records of the Products table from the database
If you've noticed, we want to do something almost repetitive. Our products table is very similar to the pages table and only has a few additional fields. We don't need to code from scratch here and we can get help from the code we wrote earlier and edit it wherever necessary. This is very common in programming, and programmers always use pieces of code they've written before and will edit them to suit their needs. That's why when you become a professional programmer and have done a lot of projects before, by getting a new project, you will remember what you need to do much faster and you can estimate the framework of the project. Secondly, you will be able to do large projects with thousands of code lines much faster in a short period of time.
Create a new file named products_read.php in the admin folder and copy all the code in pages_read.php to it. Then we need to see where we need to edit it. Let's start at the top.
First, change the word Read Pages to Read Products above line 7. Then we come to the delete code. Just change the name of the table in Query here! In line 21, change the word pages to products. If you remember, when the deletion operation is done correctly, you will be redirected to another page via Header. That's line 33 in the code, so you'll need to change this new page from pages_read.php?message=deleted to products_read.php?message=deleted
Then in line 60 find the phrase // button for create page.
Below this line, we created a button named Create New Page that was linked to pages_create.php. Naturally, this button should now be linked to products_create.php and its name should be changed to Create New Product. Then we read all the data from the table. In the corresponding query, change
SELECT id, title FROM pages ORDER BY id DESC
To
SELECT id, title, price FROM products ORDER BY id DESC
Here in Query, we changed the name of the table and wanted to return the price column information to us because we want to display it. So, the columns in the table also change and the Price column is added to the query. That’s mean:
//creating our table heading
echo '<tr>';
echo '<th>ID</th>';
echo '<th>Title</th>';
echo '<th>Price</th>';
echo '<th>Action</th>';
echo '</tr>';
Also, inside the while loop, we need to change the following:
// creating new table row per record
echo '<tr>';
echo '<td>'.$row['id'].'</td>';
echo '<td>'.$row['title'].'</td>';
echo '<td>$'.$row['price'].'</td>';
echo '<td>';
Then we have the Read, Edit and Delete buttons. We need to make their links because they are now linked to pages. So it turns out:
// read one record
echo '<a href="products_read_one.php?id='.$row['id'].'" class="btn btn-info btn-sm mr-1 mb-1">Read</a>';
// update one record
echo '<a href="products_update.php?id='.$row['id'].'" class="btn btn-primary btn-sm mr-1 mb-1">Edit</a>';
// delete one record but with confirmation warning
echo '<a href="#" data-href="products_read.php?delete='.$row['id'].'" data-toggle="modal" data-target="#confirm-delete" class="btn btn-danger btn-sm mb-1">Delete</a>';
The rest of the page does not need to be changed. Now see the following link:
http://127.0.0.1/database/admin/products_read.php
Isn’t it interesting? All the products we've already added to the products table are listed here, and the Read, Edit, and Delete buttons are ready. Even the Delete button works properly! This means that all the effort we put into the Delete section and building the Modal in the previous file is easily reused here. You can add a test product and delete it to see if everything works properly. Of course, the Read and Edit buttons don't work at the moment, and we have to make the files related to them in a similar way above, with some copy&paste and a little editing. If your file does not work properly, you have made a mistake in editing. Pay attention to the above or you can download the source code from the end of this tutorial and see where you went wrong.
Read a record from the products table in the database
In the next part, we want to make the Read button work for each record. Create a new file named products_read_one.php in the admin folder and copy the pages_read_one.php code exactly to it, and then edit it like the previous file.
First, we change the title Read One Page to Read One Product. In the following, we have a conditional structure that checks the sending of id. Then we have a query that takes the information from the pages table based on the id sent. So all we have to do is change the table name in the query, and of course, the names of the columns we receive will change a bit.
$query = "SELECT title, description, price, photo FROM products WHERE id = :id LIMIT 0,1";
In // values to fill up our table we also need to add the received information to the variables, so it changes as follows:
// values to fill up our table
$title = $row['title'];
$description = $row['description'];
$price = $row['price'];
$photo = $row['photo'];
In the HTML code section of the page, we first modify the back button:
<a href="products_read.php" class="btn btn-secondary float-right mb-2">Back to read products</a>
And then we change the HTML table based on the received data.
<table class="table table-hover table-bordered">
<tr>
<td>ID</td>
<td><?php echo $id;?></td>
</tr>
<tr>
<td>Title</td>
<td><?php echo $title;?></td>
</tr>
<tr>
<td>Description</td>
<td><?php echo nl2br($description);?></td>
</tr>
<tr>
<td>Price</td>
<td><?php echo $price;?></td>
</tr>
<tr>
<td>Photo</td>
<td><img src="../uploads/<?php echo $photo;?>" width="30%"></td>
</tr>
</table>
Now click on one of the Read buttons on the product page. Our job was easily done with this file!
Edit a record from the products table in the database
Like we did in the previous steps, create a new file called products_update.php and copy the contents of pages_update.php right into it. Let's start editing.
First, we change the page title to Edit Product. Then in the first conditional structure in the query that reads the information, we edit the table name and the required columns.
$query = "SELECT title, description, price, photo FROM products WHERE id = :id LIMIT 0,1";
Now we should define variables correctly in section // values to fill up our form.
// values to fill up our form
$title = $row['title'];
$description = $row['description'];
$price = $row['price'];
$photo = $row['photo'];
We come to the second conditional structure, which is related to the information received after the Submit form. There is a fundamental difference here. We have a photo field that the user must be able to edit and replace with a new photo. We must first check if the user has selected a new photo in the editing form. If he has selected, we must first upload the new photo in the way we learned in the products_create.php file in the previous tutorial. Then replace the photo value in the database with the name of the newly uploaded photo. If the user did not select a new photo, the name of the photo in the database must remain the same and not change. This means that the user did not intend to change the photo. So first we need to add the following code snippet to the second conditional structure.
// check and upload photo first if new photo selected
// include uploader class
include '../libs/class.upload.php';
if ($_FILES["photo"]["error"] == 0) {
$handle = new Upload($_FILES['photo']);
if ($handle->uploaded) {
$handle->allowed = array('image/*');
$handle->Process('../uploads/');
if ($handle->processed) {
$photo = $handle->file_dst_name; // set $photo with file name
} else {
echo '<div class="alert alert-info">'.$handle->error.'</div>';
$photo = $photo; // set $photo to the current photo
}
$handle-> Clean();
} else {
echo '<div class="alert alert-info">'.$handle->error.'</div>';
$photo = $photo; // set $photo to the current photo
}
} else {
// this means photo field not selected, so we use current photo
$photo = $photo; // set $photo to the current photo, It's not necessary actually! just to see what's happened
}
We almost copied the above code from the products_create.php file and changed it a bit. So here we used the previously written code. (Try comparing those codes.) Then change the update query as follows:
$query = "UPDATE products SET title=:title, description=:description, price=:price, photo=:photo WHERE id = :id";
Then correct the posted values and bind the parameters.
// posted values
$title=htmlspecialchars(strip_tags($_POST['title']));
$description=htmlspecialchars(strip_tags($_POST['description']));
$price=htmlspecialchars(strip_tags($_POST['price']));
// $photo defined before
// bind the parameters
$stmt->bindValue(':title', $title);
$stmt->bindValue(':description', $description);
$stmt->bindValue(':price', $price);
$stmt->bindValue(':photo', $photo);
$stmt->bindValue(':id', $id);
We're done with the PHP section, and now we need to edit the HTML section. First we edit the return button:
<a href="products_read.php" class="btn btn-secondary float-right mb-2">Back to read products</a>
Then we go to the HTML form section. We need to change it as follows:
<form action="products_update.php?id=<?php echo $id;?>" method="post" enctype="multipart/form-data">
<table class="table table-hover table-bordered">
<tr>
<td>Title</td>
<td><input type="text" name="title" class="form-control" value="<?php echo $title;?>" /></td>
</tr>
<tr>
<td>Description</td>
<td><textarea name="description" class="form-control"><?php echo $description;?></textarea></td>
</tr>
<tr>
<td>Price</td>
<td><input type="text" name="price" class="form-control" value="<?php echo $price;?>" /></td>
</tr>
<tr>
<td>New Photo</td>
<td><input type="file" name="photo" /></td>
</tr>
<tr>
<td>Current Photo</td>
<td><img src="../uploads/<?php echo $photo;?>" width="30%"></td>
</tr>
<tr>
<td></td>
<td>
<input type="submit" value="Edit" class="btn btn-primary" />
</td>
</tr>
</table>
</form>
Try to see the changes we made in the form line by line accurately and understand exactly what happened. We're done, and now we have to try. Click one of the Edit buttons on the Products page. If everything is correct, you will see the product editing page and you can edit everything. Try it. Especially changing the product photo.
If your code doesn't work properly, do the above steps again to see where you went wrong. You can also download the source code from the bottom of the page and compare it with your code.
Our job with the admin panel is almost done and it's ready for use. Just let's make the links at the top of the admin page so that our panel can be used. Open the header.php file in the admin folder and change the Navbar links like below.
<ul class="navbar-nav mr-auto">
<li class="nav-item active">
<a class="nav-link" href="index.php">Dashboard</a>
</li>
<li class="nav-item">
<a class="nav-link" href="products_read.php">Products</a>
</li>
<li class="nav-item">
<a class="nav-link" href="pages_read.php">Pages</a>
</li>
<li class="nav-item">
<a class="nav-link" href="#">Setting</a>
</li>
</ul>
The Dashboard link refers to the index.php file in the admin folder, but such a file does not exist yet. Then create a file named index.php in the admin folder and put the following code in it.
<?php
include ("header.php");
?>
<main class="bg-light">
<div class="jumbotron">
<div class="container">
<h1>Dashboard</h1>
</div>
</div>
<div class="container py-3">
<h3>Welcome to Admin Panel</h3>
<p>Here you can manage your online shop. To start, select from the top menu.</p>
</div>
</main>
<?php
include ("footer.php");
?>
This creates a simple welcome page and is designed to be the home page of your admin panel. We may add things to this page later. For example, the daily traffic statistics of your store ...
Also, the Setting link does not work at the moment. This link is supposed to be the settings of your admin panel, such as changing the password for accessing the admin panel, which we will cover in future tutorials.