Sometimes, your users might want to delete more than one record at a time; this tutorial will show you how to do so. Using PHP and a MySQL database, I’ll take you step-by-step and even throw in a simple zebra striping technique using jQuery.
Overview
- Program: PHP & MySQL
- Difficulty:Beginner/Intermediate
- Estimated Completion Time: 15-25 mins
- Live Demo
- Download the Source Code
Step 1 – Setting Up The Database
First things first, we need some data in our MySQL database. I’m going to use the same ‘products’ table I used in my previous tutorial – Rotating Product Listings with PHP & jQuery. For those following from scratch, I’ll repeat the process below:
Open up PHPMyAdmin and create a new database called ‘sampledb’. Within this database, we need to add a table called ‘products’.
We’ll add the following fields:
- id – INT – Primary Key – Auto Increment
- product_title – VARCHAR(150)
- product_price DECIMAN (6, 2)
- product_img – VARCHAR(150)
We need some data to populate this database. I already have ten random images that I’ll be using in the product_img field, so you’ll either need to use phpMyAdmin to add individual records yourself or if you want to follow this tutorial, you can use the SQL below to do this for you:
INSERT INTO `sampledb`.`products`
(`id` ,`product_title` ,`product_price` ,`product_img`)
VALUES
(NULL , ‘Some Awesome Band’, ‘6.99’, ‘product_images/001.jpg’),
(NULL , ‘Greatest Hits’, ‘8.99’, ‘product_images/002.jpg’),
(NULL , ‘Brilliant Band’, ‘11.99’, ‘product_images/003.jpg’ ),
(NULL , ‘Super Duper’, ‘9.99’, ‘product_images/004.jpg’ ),
(NULL , ‘Random Band’, ‘8.99’, ‘product_images/005.jpg’),
(NULL , ‘Guitar Heroes’, ‘7.99’, ‘product_images/006.jpg’),
(NULL , ‘Some Randomers’, ‘4.99’, ‘product_images/007.jpg’),
(NULL , ‘Could Be Anyone’, ‘8.99’, ‘product_images/008.jpg’),
(NULL , ‘Super Band ‘, ‘5.99’, ‘product_images/009.jpg’),
(NULL , ‘The Amazing Greats’, ‘12.99’, ‘product_images/010.jpg’);
Step 2 – Connecting to the Database
We have or products listed in the database, and now we need a way to access them. Create a file called ‘database.php’ and in between your opening/closing PHP tags, we use the following to connect to the database.
$db_name = “sampledb”; // The database we created earlier in phpMyAdmin.
$db_server = “localhost”; // Change if you have this hosted.
$db_user = ‘root’; // Your USERNAME
$db_pass = ”; // Your PASSWORD. Working locally, mine is blank.
$mysqli = new MySQLi($db_server, $db_user, $db_pass, $db_name)
or die(mysqli_error());
We’re assigning all our database credentials to variables, which I’ve commented on.
We’re also creating a variable called “$mysqli” and are setting it to a new instance of the “MySQLi” object. We need to pass in four parameters – these are what we’ve assigned above it:
- database name
- the server
- username
- password
That’s it for the ‘database.php’ file, we can go ahead and close this after saving it of course.
Step 3 – Displaying Records from the Database
So far we’ve gone over how we create a database, populate it with some random products so that we can get on with the real guts of this tutorial – multiple deletions. In order for that to happen, we need to first pull the data from the database and display them on the page.
What we’re going to do now is go back to our ‘index.php’ file and, in between the body tags, we insert the following code:
So what we need to do now is create our get_products.php file and then complete the following steps:
- Require our ‘database.php’ file
- Create a query to pull out all our products
- Loop through each record and put into a table row
- Include a checkbox for each record
We need to find our products. We do this by connecting to the database and creating a query. Now we’ve already created our database.php file which does the connections file for us, right? So we can go ahead and just require this file, and then write our query as follows:
require ‘database.php’;
$query = “SELECT id, product_title, product_price, product_img FROM Products”;
// run the query and store the results in the $result variable.
$result = $mysqli->query($query) or die(mysqli_error($mysqli));
This is a basic SQL statement that selects the fields we want from our table, and then runs the query storing the results in a variable.
After that, we’re going to loop through the records of the database and insert it into a table. I’m using tables here because I want to display a list of data, and so it’s a good use of tables. I’m sure you guys can be creative if you want to get rid of tables completely, one popular methods I have seen is using the display: block method in your CSS.
Here is the rest of the code for get_products.php.
if ($result) {
// create a new form and then put the results
// into a table.
echo “<form method=‘post’ action=‘delete.php’>“;
echo “<table cellspacing=‘0’ cellpadding=’15’>
<th width=‘15%’>Image</th>
<th width=‘55%’>Title</th>
<th width=‘15%’>Price</th>
<th width=‘15%’>Delete</th>
“;
while ($row = $result->fetch_object()) {
$title = $row->product_title;
$price = $row->product_price;
$image = $row->product_img;
$id = $row->id;
//put each record into a new table row with a checkbox
echo “<tr>
<td><img src=‘$image’ /></td>
<td>$title</td><td>$price</td>
<td><input type=‘checkbox’ name=‘checkbox[]’ id=‘checkbox[]’ value=$id />
</tr>“;
}
// when the loop is complete, close off the list.
echo “</table><p><input id=‘delete’ type=‘submit’ class=‘button’ name=‘delete’ value=‘Delete Selected Items’/></p></form>“;
}
That’s quite a big chunk of code; so let’s take a closer look.
We start by creating the html for the form and the opening part of the table. We’re then looping through the records and assigning specific fields to a variable – this will allow us to use these easily later on.
I’ve had to physically set the widths of each table column, hopefully if you have a more elegant method you can ignore this as part of your solution. The important part here is the name we’re giving to the checkbox (checkbox[]) as we’re going to hook into this later on with our ‘delete.php’.
One of the big parts of this script is where the form is sent to – ‘delete.php’. This is important as it tells the form that when the user hits the submit button, to head over to our delete file.
So if you save and preview in the browser it looks like rubbish correct? That’s because we need to add styling. Now since this isn’t a CSS tutorial, I’m going to do give you the CSS that will be included in a ’styles.css’ file. This should be included in your ‘index.php’ within the header. The code for this would be:
<link href=“css/styles.css” rel=“stylesheet” type=“text/css” />
So that goes in our head section of index.php, and the file itself will contain the following CSS:
body {
background-color: #e5e5e5;
font-family: Arial, Helvetica, sans-serif;
}
#listing {
width: 400px;
margin-top: 10px;
margin-right: auto;
margin-bottom: 50px;
margin-left: auto;
background-color: #FFFFFF;
padding: 20px;
border: 4px solid #999999;
-moz-border-radius: 9px; //this gives us the rounded corners
}
h1 {
font-size: 20px;
border-bottom-width: 2px;
border-bottom-style: solid;
border-bottom-color: #000033;
margin-top: 0px;
margin-right: 0px;
margin-bottom: 5px;
margin-left: 0px;
padding: 0px 0px 5px 0px;
}
img {
height: 50px;
width: 50px;
border: 1px solid #000033;
}
th {
text-align: left;
font-size: 15px;
}
td {
font-size: 13px;
}
.even {
background-color: #CCCCCC;
}
.button {
padding: 10px; height: 50px;
width: 100%;
color: #FFFFFF;
background-color: #666666;
border: 1px solid #000000;
font-weight: bold;
cursor:pointer;
-moz-border-radius: 5px; //this gives us the rounded corners
}
<script type=“text/javascript” src=“http://ajax.googleapis.com/ajax/libs/jquery/1.3.2/jquery.min.js”></script>
We have one more thing to do before we can preview in the browser, and that’s to add in our zebra striping to our table. If you review the CSS, you’ll notice that I’ve included a class called .even. We will use jQuery to basically look for even rows in our table and apply this class to those rows.
We need to include jQuery in our head section of index.php.
<script type=“text/javascript” src=“http://ajax.googleapis.com/ajax/libs/jquery/1.3.2/jquery.min.js”></script>
Amazingly, we only need one line of code to achieve this effect, so straight after the opening body tag, insert the following code:
<script>
$(document).ready(function() {
$(“tr:nth-child(even)”).addClass(“even”);>
});
</script>
All we are doing here is saying “Hey, when the document has finished loading, listen for all the even rows and add the even class from our CSS file.”
Let’s save our file and preview it in the browser. To reiterate, so far, we’ve done the following:
- Create a database
- Connect to a database
- Retrieve records from a database
- Use php to loop through and output into our index.php file
- Style using CSS
- Add zebra stripes using jQuery
Step 4 – Delete the Records
So now comes the harder part of the tutorial – deleting multiple records.
In a nutshell, our aim is to find out which products have been checked and then loop through the database and delete the corresponding records. Okay, so let’s get cracking!
We need to require our ‘database.php’ file again as we’re going to be using the connection to run a delete query on our MySQL database.
First we check whether the submit button was clicked.
We then use two variables
- $checkbox = which checkbox was clicked
- $countCheck = this gets the count, and since we used the [] in the name we’re able to access this as an array.
Say we have five boxes checked, what we’re going to do is while the number is under five, we’ll grab the id of the checkbox and run a delete query.
In the delete query, you’ll notice we’re using a variable called $del_id which is assigned to $checkbox[$i]. In other words, this will be the id of the record in the database that is to be deleted.
Once this is all done, we’re going to refresh the page (send them to index.php). We could easily send them somewhere else at this point e.g. a success page. For simplicity, I’m going to refresh the page so you can see that the records have been deleted. Here is a visual of what we’re doing:
If you haven’t already create the delete.php file, enter the following code:
<?php
require ‘database.php’;
if($_POST[‘delete’]) // from button name=”delete”
{
$checkbox = $_POST[‘checkbox’]; //from name=”checkbox[]”
$countCheck = count($_POST[‘checkbox’]);
for($i=0;$i<$countCheck;$i++)
{
$del_id = $checkbox[$i];
$sql = “DELETE from Products where id = $del_id“;
$result = $mysqli->query($sql) or die(mysqli_error($mysqli));
}
if($result)
{
header(‘Location: index.php’);
}
else
{
echo “Error: “.mysql_error();
}
}
?
And that’s it! You can save your files and preview in the browser.
Step 4 – Complete!
That’s it, pretty easy huh? The hardest part was working out how to go about deleting the records. Let’s have a look at what we’ve achieved:
- We’ve created a database and populated it with random data.
- We’ve used PHP to retrieve the records from the database and display them in table.
- Implemented simple CSS and a jQuery striped table effect.
- We’ve used another PHP script to check whether a user has checked a checkbox, and if they have delete the record from the database.
Comments are always welcome!