Home PHP

PHP CRUD Tutorial for Beginners – Step By Step Guide!

Previously, we learned how to run your first PHP script. This time, we we learn CRUD operations with PHP and MySQL. CRUD stands for Create, Read, Update and Delete database records.

This tutorial is for your if:

  • You need a high quality and updated reference for a PHP CRUD tutorial.
  • You need to learn how to do CRUD operations with PHP and MySQL.
  • You a beginner in this kind of PHP web programming.

Coding CRUD with PHP and MySQL is one of the basics. PHP web programmers must be able to code it with less effort. We can perform this task using any of the three PHP Database extensions:

  1. Using the MySQL extension.
  2. Using the MySQLi extension.
  3. Using the PDO extension.

PHP 5.5 deprecated the MySQL extension. It is not recommended to use these days.

If you are programming with PHP, you’ll have to use either MySQLi (i means improved) or PDO extension.

With that in mind, we will use the PDO extension. It is the newest and actively developed way of programming these CRUD grids.

What will you learn? We will cover the following topics or contents.

1.0 Program Output
2.0 File Structure

3.0 Prepare The Database
3.1 Create the Database
3.2 Create the Database Table
3.3 Dump Sample Data On The Table
3.4 Create Database Connection PHP File

4.0 Create or Insert Record in PHP
4.1 Basic HTML Code For create.php
4.2 HTML Form To Input New Record Data
4.3 Code Create A New Record

5.0 Read Records in PHP
5.1 Basic HTML Code For read.php
5.2 Read Records From The Database

6.0 Read One Record in PHP
6.1 Basic HTML Code For read_one.php
6.2 Read Records From The Database
6.3 Display Record Details

7.0 Update Record in PHP
7.1 Basic HTML Code For update.php
7.2 Read A Record By ID Parameter
7.3 HTML Form To Update A Record
7.4 Code To Update The Record

8.0 Delete Record in PHP
8.1 Tell The User If Record Was Deleted
8.2 JavaScript To Verify Record Deletion
8.3 Delete Record From The Database

9.0 Pagination in PHP
9.1 Set Pagination Variables
9.2 Add LIMIT Clause in SELECT Query
9.3 Count Total Number Of Records
9.4 Include Paging File
9.5 Create paging.php

10.0 File Upload in PHP
10.1 Add HTML “file” field
10.2 Add “image” field
10.3 Set variables for file upload
10.4 Make sure submitted file is a real image
10.5 Make sure certain file types are allowed
10.6 Make sure file does not exist
10.7 make sure submitted file is not too large
10.8 Make sure the ‘uploads’ folder exists
10.9 Try to upload the file
10.10 Show image in “read one” page

11.0 Download LEVEL 1 Source Code
12.0 Download LEVEL 2 Source Code
13.0 Download LEVEL 3 Source Code
14.0 Download ALL LEVELS

15.0 Online Resources
16.0 What’s Next?
17.0 Related Source Codes
18.0 Some Notes

1.0 PHP CRUD Tutorial Program Output

We usually have three LEVELS of source code output. But WHY? Because I believe in "Learning Progression" to ensure efficient learning. Learn more

Below are some screenshots of our script’s output. You can click an image to view the larger version of it. Use the left and right arrow to navigate through the screenshots.

Please note that the following images are just output previews. New features might be added already the time you are reading this.

1.1 LEVEL 1 Source Code Output


1.2 LEVEL 2 Source Code Output


1.3 LEVEL 3 Source Code Output


The LEVEL 2 and LEVEL 3 source code outputs proves that you can add and customize more features. It’s easier and faster if you will learn by following our tutorial below.

Downloading our source codes is your huge advantage as well. For now, let’s proceed to the step by step tutorial of our LEVEL 1 source code. Enjoy!

2.0 Project File Structure

Our PHP CRUD tutorial will contain the following main files.

  • dev/products.sql – contains the database table structure and sample data used in this project. Once you created your database in PhpMyAdmin, you can import this file.
  • libs/ – is where our Bootstrap CSS framework and jQuery library is located.
  • config/database.php – used for database connection and configuration.
  • create.php – used for creating a new record. It contains an HTML form where the user can enter details for a new record.
  • read.php – used for reading records from the database. It uses an HTML table to display the data retrieved from the MySQL database.
  • read_one.php – used for reading one or single record from database. It uses an HTML table to display the data retrieved from the MySQL database.
  • update.php – used for updating a record. It uses an HTML form which will be filled out with data based on the given “id” parameter.
  • delete.php – used for deleting a record. It accepts an “id” parameter and deletes the record with it. Once it execute the delete query, it will redirect the user to the read.php page.

3.0 Prepare The Database

3.1 Create the Database

On your PhpMyAdmin, create a database named “1phpbeginnercrudlevel1”.

If you’re not sure how to do it, please take a look at the following example. Follow only the “create database” part.

3.2 Create the Database Table

Next, run the following SQL code. This is to create our “products” database table. If you’re not sure how to do this, take a look at this resource.

--
-- Table structure for table `products`
--

CREATE TABLE IF NOT EXISTS `products` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(128) NOT NULL,
  `description` text NOT NULL,
  `price` double NOT NULL,
  `created` datetime NOT NULL,
  `modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;

3.3 Dump Sample Data On The Table

Again, run the following SQL code on your PhpMyAdmin. This will insert the sample data or record on our ‘products’ database table.

--
-- Dumping data for table `products`
--

INSERT INTO `products` (`id`, `name`, `description`, `price`, `created`, `modified`) VALUES
(1, 'Basketball', 'A ball used in the NBA.', 49.99, '2015-08-02 12:04:03', '2015-08-06 06:59:18'),
(3, 'Gatorade', 'This is a very good drink for athletes.', 1.99, '2015-08-02 12:14:29', '2015-08-06 06:59:18'),
(4, 'Eye Glasses', 'It will make you read better.', 6, '2015-08-02 12:15:04', '2015-08-06 06:59:18'),
(5, 'Trash Can', 'It will help you maintain cleanliness.', 3.95, '2015-08-02 12:16:08', '2015-08-06 06:59:18'),
(6, 'Mouse', 'Very useful if you love your computer.', 11.35, '2015-08-02 12:17:58', '2015-08-06 06:59:18'),
(7, 'Earphone', 'You need this one if you love music.', 7, '2015-08-02 12:18:21', '2015-08-06 06:59:18'),
(8, 'Pillow', 'Sleeping well is important.', 8.99, '2015-08-02 12:18:56', '2015-08-06 06:59:18');


As you may have noticed, steps 1 and 2 are both SQL queries. Yes, they can run at the same time. But I wanted it to be on separate steps to emphasize those SQL queries’ purpose.

3.4 Create Database Connection PHP File

Create database.php file and put the following code inside it. It answers the question: how to connect to MySQL database with PDO?

<?php
// used to connect to the database
$host = "localhost";
$db_name = "1phpbeginnercrudlevel1";
$username = "root";
$password = "";

try {
	$con = new PDO("mysql:host={$host};dbname={$db_name}", $username, $password);
}

// show error
catch(PDOException $exception){
	echo "Connection error: " . $exception->getMessage();
}
?>

4.0 Create or Insert Record in PHP

4.1 Basic HTML Code For create.php

Create the create.php file. We will use it to create a new record to the database. Put the code following code inside the create.php file.

We use Bootstrap user interface for this project. Make sure you downloaded a copy and put it inside the “libs” folder.

If you are not familiar with Bootstrap, please learn our Bootstrap Tutorial for Beginners real quick.

<!DOCTYPE HTML>
<html>
<head>
	<title>PDO - Create a Record - PHP CRUD Tutorial</title>
	
	<!-- Bootstrap -->
	<!-- Latest compiled and minified CSS -->
	<link rel="stylesheet" href="libs/bootstrap-3.3.6/css/bootstrap.min.css" />
 
	<!-- HTML5 Shiv and Respond.js IE8 support of HTML5 elements and media queries -->
	<!-- WARNING: Respond.js doesn't work if you view the page via file:// -->
	<!--[if lt IE 9]>
	<script src="https://oss.maxcdn.com/libs/html5shiv/3.7.0/html5shiv.js"></script>
	<script src="https://oss.maxcdn.com/libs/respond.js/1.4.2/respond.min.js"></script>
	<![endif]-->
		
</head>
<body>

    <!-- container -->
    <div class="container">
 
        <div class="page-header">
            <h1>Create Product</h1>
        </div>
	
	<!-- dynamic content will be here -->
		
    </div> <!-- end .container -->
	
<!-- jQuery (necessary for Bootstrap's JavaScript plugins) -->
<script src="libs/jquery-3.0.0.min.js"></script>
 
<!-- Include all compiled plugins (below), or include individual files as needed -->
<script src="libs/bootstrap-3.3.6/js/bootstrap.min.js"></script>

</body>
</html>

4.2 HTML Form To Input New Record Data

Now we are going to start answering the question: how to create a record with PDO?

The code below will create an HTML form with input fields that matches the fields in the database. Put it inside the “container” div of create.php of section 4.1 above.

<!-- html form here where the product information will be entered -->
<form action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]);?>" method="post">
	<table class='table table-hover table-responsive table-bordered'>
		<tr>
			<td>Name</td>
			<td><input type='text' name='name' 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></td>
			<td>
				<input type='submit' value='Save' class='btn btn-primary' />
				<a href='read.php' class='btn btn-danger'>Back to read products</a>
			</td>
		</tr>
	</table>
</form>

4.3 Code To Create A New Record

We are still working in the create.php file. Once the user filled out the form and clicked the save button in section 4.2, the code below will save it to the MySQL database. Put it above the “form” tag of section 4.2 above.

<?php
if($_POST){

	// include database connection
	include 'config/database.php';

	try{
	
		// insert query
		$query = "INSERT INTO products SET name=:name, description=:description, price=:price, created=:created";

		// prepare query for execution
		$stmt = $con->prepare($query);

		// posted values
		$name=htmlspecialchars(strip_tags($_POST['name']));
		$description=htmlspecialchars(strip_tags($_POST['description']));
		$price=htmlspecialchars(strip_tags($_POST['price']));

		// bind the parameters
		$stmt->bindParam(':name', $name);
		$stmt->bindParam(':description', $description);
		$stmt->bindParam(':price', $price);
		
		// specify when this record was inserted to the database
		$created=date('Y-m-d H:i:s');
		$stmt->bindParam(':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());
	}
}
?>

5.0 Read Records in PHP

5.1 Basic HTML Code For read.php

Create the read.php file. We prepare this to read records from the database. It answers the question: how to read records with PDO?

Put the following code inside the read.php file.

<!DOCTYPE HTML>
<html>
<head>
	<title>PDO - Read Records - PHP CRUD Tutorial</title>
	
	<!-- Bootstrap -->
	<!-- Latest compiled and minified CSS -->
	<link rel="stylesheet" href="libs/bootstrap-3.3.6/css/bootstrap.min.css" />
 
	<!-- HTML5 Shiv and Respond.js IE8 support of HTML5 elements and media queries -->
	<!-- WARNING: Respond.js doesn't work if you view the page via file:// -->
	<!--[if lt IE 9]>
	<script src="https://oss.maxcdn.com/libs/html5shiv/3.7.0/html5shiv.js"></script>
	<script src="https://oss.maxcdn.com/libs/respond.js/1.4.2/respond.min.js"></script>
	<![endif]-->
		
	<!-- custom css -->
	<style>
	.m-r-1em{ margin-right:1em; }
	.m-b-1em{ margin-bottom:1em; }
	.m-l-1em{ margin-left:1em; }
	</style>

</head>
<body>

    <!-- container -->
    <div class="container">
 
        <div class="page-header">
            <h1>Read Products</h1>
        </div>
	
	<!-- dynamic content will be here -->
		
    </div> <!-- end .container -->
	
<!-- jQuery (necessary for Bootstrap's JavaScript plugins) -->
<script src="libs/jquery-3.0.0.min.js"></script>
 
<!-- Include all compiled plugins (below), or include individual files as needed -->
<script src="libs/bootstrap-3.3.6/js/bootstrap.min.js"></script>

</body>
</html>

5.2 Read Records From The Database

This time we will read records from the database. Put the following code inside the “container” div tags in section 5.1 above.

<?php
// include database connection
include 'config/database.php';

// select all data
$query = "SELECT id, name, description, price FROM products ORDER BY id DESC";
$stmt = $con->prepare($query);
$stmt->execute();

// this is how to get number of rows returned
$num = $stmt->rowCount();

// link to create record form
echo "<a href='create.php' class='btn btn-primary m-b-1em'>Create New Product</a>";

//check if more than 0 record found
if($num>0){

	echo "<table class='table table-hover table-responsive table-bordered'>";//start table
	
		//creating our table heading
		echo "<tr>";
			echo "<th>ID</th>";
			echo "<th>Name</th>";
			echo "<th>Description</th>";
			echo "<th>Price</th>";
			echo "<th>Action</th>";
		echo "</tr>";
		
		// retrieve our table contents
		// fetch() is faster than fetchAll()
		// http://stackoverflow.com/questions/2770630/pdofetchall-vs-pdofetch-in-a-loop
		while ($row = $stmt->fetch(PDO::FETCH_ASSOC)){
			// extract row
			// this will make $row['firstname'] to
			// just $firstname only
			extract($row);
			
			// creating new table row per record
			echo "<tr>";
				echo "<td>{$id}</td>";
				echo "<td>{$name}</td>";
				echo "<td>{$description}</td>";
				echo "<td>&#36;{$price}</td>";
				echo "<td>";
					// read one record 
					echo "<a href='read_one.php?id={$id}' class='btn btn-info m-r-1em'>Read</a>";
					
					// we will use this links on next part of this post
					echo "<a href='update.php?id={$id}' class='btn btn-primary m-r-1em'>Edit</a>";

					// we will use this links on next part of this post
					echo "<a href='#' onclick='delete_user({$id});'  class='btn btn-danger'>Delete</a>";
				echo "</td>";
			echo "</tr>";
		}
	
	// end table
	echo "</table>";
	
}

// if no records found
else{
	echo "<div class='alert alert-danger'>No records found.</div>";
}
?>

The code above shows:

  • The inclusion of database.php file from section 3.4
  • The SELECT SQL query.
  • The HTML table where the retrieved data will be put.

6.0 Read One Record in PHP

6.1 Basic HTML Code For read_one.php

Create a PHP file and name it read_one.php – this is where we will read and display the details of a single database record. Put the following basic HTML code.

<!DOCTYPE HTML>
<html>
<head>
	<title>PDO - Read One Record - PHP CRUD Tutorial</title>

	<!-- Bootstrap -->
	<!-- Latest compiled and minified CSS -->
	<link rel="stylesheet" href="libs/bootstrap-3.3.6/css/bootstrap.min.css" />
 
	<!-- HTML5 Shiv and Respond.js IE8 support of HTML5 elements and media queries -->
	<!-- WARNING: Respond.js doesn't work if you view the page via file:// -->
	<!--[if lt IE 9]>
	<script src="https://oss.maxcdn.com/libs/html5shiv/3.7.0/html5shiv.js"></script>
	<script src="https://oss.maxcdn.com/libs/respond.js/1.4.2/respond.min.js"></script>
	<![endif]-->

</head>
<body>


    <!-- container -->
    <div class="container">
 
        <div class="page-header">
            <h1>Read Product</h1>
        </div>
		
		<!-- dynamic content will be here -->

	</div> <!-- end .container -->
	
<!-- jQuery (necessary for Bootstrap's JavaScript plugins) -->
<script src="libs/jquery-3.0.0.min.js"></script>
 
<!-- Include all compiled plugins (below), or include individual files as needed -->
<script src="libs/bootstrap-3.3.6/js/bootstrap.min.js"></script>

</body>
</html>

6.2 Read One Record From The Database

The following code is how we retrieve a single database record. Put it inside the “container” div tags.

<?php
// get passed parameter value, in this case, the record ID
// isset() is a PHP function used to verify if a value is there or not
$id=isset($_GET['id']) ? $_GET['id'] : die('ERROR: Record ID not found.');

//include database connection
include 'config/database.php';

// read current record's data
try {
	// prepare select query
	$query = "SELECT id, name, description, price FROM products WHERE id = ? LIMIT 0,1";
	$stmt = $con->prepare( $query );

	// this is the first question mark
	$stmt->bindParam(1, $id);

	// execute our query
	$stmt->execute();

	// store retrieved row to a variable
	$row = $stmt->fetch(PDO::FETCH_ASSOC);

	// values to fill up our form
	$name = $row['name'];
	$description = $row['description'];
	$price = $row['price'];
}

// show error
catch(PDOException $exception){
	die('ERROR: ' . $exception->getMessage());
}
?>

6.3 Display Record Details

The following HTML table will hold and display the details of a single database record. Put the following code after the code in section 6.2 above.

<!--we have our html table here where new user information will be displayed-->
<table class='table table-hover table-responsive table-bordered'>
	<tr>
		<td>Name</td>
		<td><?php echo htmlspecialchars($name, ENT_QUOTES);  ?></td>
	</tr>
	<tr>
		<td>Description</td>
		<td><?php echo htmlspecialchars($description, ENT_QUOTES);  ?></td>
	</tr>
	<tr>
		<td>Price</td>
		<td><?php echo htmlspecialchars($price, ENT_QUOTES);  ?></td>
	</tr>
	<tr>
		<td></td>
		<td>
			<a href='read.php' class='btn btn-danger'>Back to read products</a>
		</td>
	</tr>
</table>

7.0 Update Record in PHP

7.1 Basic HTML Code For udpate.php

Create the update.php file. We are preparing to update a selected record from the database. This will answer the question: how to update a record with PDO?

Put the following code inside the new update.php file.

<!DOCTYPE HTML>
<html>
<head>
	<title>PDO - Update a Record - PHP CRUD Tutorial</title>
	
	<!-- Bootstrap -->
	<!-- Latest compiled and minified CSS -->
	<link rel="stylesheet" href="libs/bootstrap-3.3.6/css/bootstrap.min.css" />
 
	<!-- HTML5 Shiv and Respond.js IE8 support of HTML5 elements and media queries -->
	<!-- WARNING: Respond.js doesn't work if you view the page via file:// -->
	<!--[if lt IE 9]>
	<script src="https://oss.maxcdn.com/libs/html5shiv/3.7.0/html5shiv.js"></script>
	<script src="https://oss.maxcdn.com/libs/respond.js/1.4.2/respond.min.js"></script>
	<![endif]-->
		
</head>
<body>

    <!-- container -->
    <div class="container">
 
        <div class="page-header">
            <h1>Update Product</h1>
        </div>
	
	<!-- dynamic content will be here -->
		
    </div> <!-- end .container -->
	
<!-- jQuery (necessary for Bootstrap's JavaScript plugins) -->
<script src="libs/jquery-3.0.0.min.js"></script>
 
<!-- Include all compiled plugins (below), or include individual files as needed -->
<script src="libs/bootstrap-3.3.6/js/bootstrap.min.js"></script>

</body>
</html>

7.2 Read A Record By ID Parameter

We have to get the record ID and store it in the $id variable. We access the $_GET[‘id’] variable to do it.

What we are trying to do here is to get the related data based on the given record ID. This is a way to auto-fill the HTML form (on section 7.3) with existing row data.

Put the following code inside the “container” div tag of update.php in section 7.1 above.

<?php
// get passed parameter value, in this case, the record ID
// isset() is a PHP function used to verify if a value is there or not
$id=isset($_GET['id']) ? $_GET['id'] : die('ERROR: Record ID not found.');

//include database connection
include 'config/database.php';

// read current record's data
try {
	// prepare select query
	$query = "SELECT id, name, description, price FROM products WHERE id = ? LIMIT 0,1";
	$stmt = $con->prepare( $query );
	
	// this is the first question mark
	$stmt->bindParam(1, $id);
	
	// execute our query
	$stmt->execute();
	
	// store retrieved row to a variable
	$row = $stmt->fetch(PDO::FETCH_ASSOC);
	
	// values to fill up our form
	$name = $row['name'];
	$description = $row['description'];
	$price = $row['price'];
}

// show error
catch(PDOException $exception){
	die('ERROR: ' . $exception->getMessage());
}
?>

7.3 HTML Form To Update A Record

The following code will be under the code on section 7.2. This is no ordinary HTML form like the section 4.2 above. This form shows data retrieved from section 7.2 where we read a single record based on given ID parameter.

<!--we have our html form here where new user information will be entered-->
<form action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"] . "?id={$id}");?>" method="post">
	<table class='table table-hover table-responsive table-bordered'>
		<tr>
			<td>Name</td>
			<td><input type='text' name='name' value="<?php echo htmlspecialchars($name, ENT_QUOTES);  ?>" class='form-control' /></td>
		</tr>
		<tr>
			<td>Description</td>
			<td><textarea name='description' class='form-control'><?php echo htmlspecialchars($description, ENT_QUOTES);  ?></textarea></td>
		</tr>
		<tr>
			<td>Price</td>
			<td><input type='text' name='price' value="<?php echo htmlspecialchars($price, ENT_QUOTES);  ?>" class='form-control' /></td>
		</tr>
		<tr>
			<td></td>
			<td>
				<input type='submit' value='Save Changes' class='btn btn-primary' />
				<a href='read.php' class='btn btn-danger'>Back to read products</a>
			</td>
		</tr>
	</table>
</form>

7.4 Code To Update The Record

The following code will save the changes in the database.

That is if the user change some value on the form and hit the “Save Changes” button. Put the following code before the previous section’s code.

<?php
// get passed parameter value, in this case, the record ID
// isset() is a PHP function used to verify if a value is there or not
$id=isset($_GET['id']) ? $_GET['id'] : die('ERROR: Record ID not found.');

//include database connection
include 'config/database.php';

// check if form was submitted
if($_POST){
	
	try{
	
		// write update query
		// in this case, it seemed like we have so many fields to pass and 
		// it is better to label them and not use question marks
		$query = "UPDATE products 
					SET name=:name, description=:description, price=:price 
					WHERE id = :id";

		// prepare query for excecution
		$stmt = $con->prepare($query);

		// posted values
		$name=htmlspecialchars(strip_tags($_POST['name']));
		$description=htmlspecialchars(strip_tags($_POST['description']));
		$price=htmlspecialchars(strip_tags($_POST['price']));

		// bind the parameters
		$stmt->bindParam(':name', $name);
		$stmt->bindParam(':description', $description);
		$stmt->bindParam(':price', $price);
		$stmt->bindParam(':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 update record. Please try again.</div>";
		}
		
	}
	
	// show errors
	catch(PDOException $exception){
		die('ERROR: ' . $exception->getMessage());
	}
}
?>

8.0 Delete Record in PHP

8.1 Tell The User If Record Was Deleted

Put the following code right after the code include ‘config/database.php’; in read.php file.

This will tell the user if there is a deleted record after clicking the delete button and “ok” in the pop up.

$action = isset($_GET['action']) ? $_GET['action'] : "";

// if it was redirected from delete.php
if($action=='deleted'){
	echo "<div class='alert alert-success'>Record was deleted.</div>";
}

8.2 JavaScript To Verify Record Deletion

The user clicks on the “Delete” button in read.php. Next, he will verify the deletion by clicking “OK” on the pop up.

That user activity will execute the following JavaScript code. Put it before the end “body” tag in read.php file.

<script type='text/javascript'>
function delete_user( id ){
	
	var answer = confirm('Are you sure?');
	if (answer){
		// if user clicked ok, 
		// pass the id to delete.php and execute the delete query
		window.location = 'delete.php?id=' + id;
	} 
}
</script>

8.3 Delete Record From The Database

The code below will delete a record from the database using the given ID parameter.

This answers the question: how to delete a record with PDO? Create delete.php file and put the following code inside it.

<?php
// include database connection
include 'config/database.php';

try {
	
	// get record ID
	// isset() is a PHP function used to verify if a value is there or not
	$id=isset($_GET['id']) ? $_GET['id'] : die('ERROR: Record ID not found.');

	// delete query
	$query = "DELETE FROM products WHERE id = ?";
	$stmt = $con->prepare($query);
	$stmt->bindParam(1, $id);
	
	if($stmt->execute()){
		// redirect to read records page and 
		// tell the user record was deleted
		header('Location: read.php?action=deleted');
	}else{
		die('Unable to delete record.');
	}
}

// show error
catch(PDOException $exception){
	die('ERROR: ' . $exception->getMessage());
}
?>

9.0 Pagination in PHP

Please note that this is a bonus section and is not included in the LEVEL 1 source code download. We will have to add or remove some codes we’ve done above so that pagination will work.

9.1 Set Pagination Variables

Add the following code below include ‘config/database.php’; code of section 5.2 above.

// PAGINATION VARIABLES
// page is the current page, if there's nothing set, default is page 1
$page = isset($_GET['page']) ? $_GET['page'] : 1;

// set records or rows of data per page
$records_per_page = 5;

// calculate for the query LIMIT clause
$from_record_num = ($records_per_page * $page) - $records_per_page;

9.2 Add LIMIT Clause in SELECT Query

Stil on section 5.2, change the following code from:

$query = "SELECT id, name, description, price FROM products ORDER BY id DESC";
$stmt = $con->prepare($query);
$stmt->execute();

to:

// select data for current page
$query = "SELECT id, name, description, price FROM products ORDER BY id DESC
	LIMIT :from_record_num, :records_per_page";

$stmt = $con->prepare($query);
$stmt->bindParam(":from_record_num", $from_record_num, PDO::PARAM_INT);
$stmt->bindParam(":records_per_page", $records_per_page, PDO::PARAM_INT);
$stmt->execute();

9.3 Count Total Number Of Records

Below the closing ‘table’ tag in section 5.2, add the following code.

// PAGINATION
// count total number of rows
$query = "SELECT COUNT(*) as total_rows FROM products";
$stmt = $con->prepare($query);

// execute query
$stmt->execute();

// get total rows
$row = $stmt->fetch(PDO::FETCH_ASSOC);
$total_rows = $row['total_rows'];

9.4 Include Paging File

Add the following code after the previous section’s code.

// paginate records
$page_url="read.php?";
include_once "paging.php";

Why a $page_url variable is needed? Because we made paging.php re-usable. You can use it for other objects you want to paginate.

For example you’re trying to paginate your read_categories.php, you do $page_url=”read_categories.php?”;

You will have to follow the code pattern of section 9.1 to 9.3 when you use paging.php file.

9.5 Create paging.php

Create new file and name it paging.php, it should have the following code.

<?php
// *************** <PAGING_SECTION> ***************
echo "<nav class='overflow-hidden'>";
	echo "<ul class='pagination pull-left margin-zero'>";

	// ***** for 'first' and 'previous' pages
	if($page>1){

		// ********** show the previous page
		$prev_page = $page - 1;
		echo "<li>";
			echo "<a href='{$page_url}page={$prev_page}'>";
				echo "<span style='margin:0 .5em;'>&laquo;</span>";
			echo "</a>";
		echo "</li>";
	}


	// ********** show the number paging

	// find out total pages
	$total_pages = ceil($total_rows / $records_per_page);

	// range of num links to show
	$range = 1;

	// display links to 'range of pages' around 'current page'
	$initial_num = $page - $range;
	$condition_limit_num = ($page + $range)  + 1;

	for ($x=$initial_num; $x<$condition_limit_num; $x++) {

		// be sure '$x is greater than 0' AND 'less than or equal to the $total_pages'
		if (($x > 0) && ($x <= $total_pages)) {

			// current page
			if ($x == $page) {
				echo "<li class='active'>";
					echo "<a href='javascript::void();'>{$x}</a>";
				echo "</li>";
			}

			// not current page
			else {
				echo "<li>";
					echo " <a href='{$page_url}page={$x}'>{$x}</a> ";
				echo "</li>";
			}
		}
	}


	// ***** for 'next' and 'last' pages
	if($page<$total_pages){
		// ********** show the next page
		$next_page = $page + 1;

		echo "<li>";
			echo "<a href='{$page_url}page={$next_page}'>";
				echo "<span style='margin:0 .5em;'>&raquo;</span>";
			echo "</a>";
		echo "</li>";
	}

	echo "</ul>";

echo "</nav>";
// *************** </PAGING_SECTION> ***************
?>

10.0 File Upload in PHP

Now we are going to add a “file upload” feature when creating a record.

10.1 Add HTML “file” field

Open create.php file and scroll down to the form. Find the form tag and enable the file upload by changing it to:

<form action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]);?>" method="post" enctype="multipart/form-data">

Find the closing “tr” tag of the “price” field. Once found, add the following code after it.

<tr>
	<td>Photo</td>
	<td><input type="file" name="image" /></td>
</tr>

10.2 Add “image” field

Still in create.php file. Scroll up and change the “insert” code. It should look like the following code. The new “image” field will store the file name of the submitted file.

Using PhpMyAdmin, add an “image” field in the products database table as well.

// insert query
$query = "INSERT INTO products
			SET name=:name, description=:description,
				price=:price, image=:image, created=:created";

// prepare query for execution
$stmt = $con->prepare($query);

$name=htmlspecialchars(strip_tags($_POST['name']));
$description=htmlspecialchars(strip_tags($_POST['description']));
$price=htmlspecialchars(strip_tags($_POST['price']));

// new 'image' field
$image=!empty($_FILES["image"]["name"])
		? sha1_file($_FILES['image']['tmp_name']) . "-" . basename($_FILES["image"]["name"])
		: "";
$image=htmlspecialchars(strip_tags($image));

// bind the parameters
$stmt->bindParam(':name', $name);
$stmt->bindParam(':description', $description);
$stmt->bindParam(':price', $price);
$stmt->bindParam(':image', $image);

// specify when this record was inserted to the database
$created=date('Y-m-d H:i:s');
$stmt->bindParam(':created', $created);

10.3 Set variables for file upload

We will start the code for the “file upload” feature. Find the following line in create.php file.

echo "<div class='alert alert-success'>Record was saved to database.</div>";

Under the code above, we will add the following code. The “if($image){” code will verify if there’s an uploaded image. If there is, inside the “if” statement, we will set the initial variables needed for the file upload.

// now, if image is not empty, try to upload the image
if($image){

	// sha1_file() function is used to make a unique file name
	$target_directory = "uploads/";
	$target_file = $target_directory . $image;
	$file_type = pathinfo($target_file, PATHINFO_EXTENSION);

	// error message is empty
	$file_upload_error_messages="";

}

10.4 Make sure submitted file is a real image

Now we will start validating the submitted file. The code below will identify if the submitted file is a real or fake image.

Put the following code under $file_upload_error_messages=””; of the previous section.

// make sure that file is a real image
$check = getimagesize($_FILES["image"]["tmp_name"]);
if($check!==false){
	// submitted file is an image
}else{
	$file_upload_error_messages.="<div>Submitted file is not an image.</div>";
}

10.5 Make sure certain file types are allowed

The following code will limit the allowed file types. Put it under the code of the previous section.

// make sure certain file types are allowed
$allowed_file_types=array("jpg", "jpeg", "png", "gif");
if(!in_array($file_type, $allowed_file_types)){
	$file_upload_error_messages.="<div>Only JPG, JPEG, PNG, GIF files are allowed.</div>";
}

10.6 Make sure file does not exist

There’s a very small chance that the submitted file name will be the same with the one that exists in the server. This is because of the sha1_file() method we used on section 10.2 above.

But just in case there’s a file with the same name, tell the user. Put the following code after the previous section’s code.

// make sure file does not exist
if(file_exists($target_file)){
	$file_upload_error_messages.="<div>Image already exists. Try to change file name.</div>";
}

10.7 Make sure submitted file is not too large

Uploading a very large photo is not recommended in this case. So we will set the file size limit to less than 1 MB. Put the following code after the code of the previous section.

// make sure submitted file is not too large, can't be larger than 1 MB
if($_FILES['image']['size'] > (1024000)){
	$file_upload_error_messages.="<div>Image must be less than 1 MB in size.</div>";
}

10.8 Make sure the ‘uploads’ folder exists

The “uploads” folder is where we will put the submitted file. Make sure it exists by using the following code. Put it under the code of the previous section.

// make sure the 'uploads' folder exists
// if not, create it
if(!is_dir($target_directory)){
	mkdir($target_directory, 0777, true);
}

10.9 Try to upload the file

Put the following code under the previous section’s code.

// if $file_upload_error_messages is still empty
if(empty($file_upload_error_messages)){
	// it means there are no errors, so try to upload the file
	if(move_uploaded_file($_FILES["image"]["tmp_name"], $target_file)){
		// it means photo was uploaded
	}else{
		echo "<div class='alert alert-danger'>";
			echo "<div>Unable to upload photo.</div>";
			echo "<div>Update the record to upload photo.</div>";
		echo "</div>";
	}
}

// if $file_upload_error_messages is NOT empty
else{
	// it means there are some errors, so show them to user
	echo "<div class='alert alert-danger'>";
		echo "<div>{$file_upload_error_messages}</div>";
		echo "<div>Update the record to upload photo.</div>";
	echo "</div>";
}

10.10 Show image in “read one” page

Open read_one.php file and apply the following changes to the code.

Add ‘image’ field to the $query variable. It should look like the following.

$query = "SELECT id, name, description, price, image FROM products WHERE id = ? LIMIT 0,1";

Add ‘$image’ variable after the ‘$price’ variable.

$image = htmlspecialchars($row['image'], ENT_QUOTES);

Find the closing ‘tr’ tag of the ‘Price’ field in the HTML table and put the following code after it. It will show the uploaded image or ‘No image found.’ if no image was uploaded.

<tr>
	<td>Image</td>
	<td>
	<?php echo $image ? "<img src='uploads/{$image}' style='width:300px;' />" : "No image found.";  ?>
	</td>
</tr>

11.0 Download LEVEL 1 Source Code

You can get the source code by following the whole, well detailed PHP CRUD tutorial above. But isn’t it more convenient if you can just download the complete source code we used, and play around it?

There’s a small fee in getting the complete source code, it is small compared to the:

✔ Value or skill upgrade it can bring you, orYES
✔ Income you can get from your website project or business.YES
✔ Precious time you save.YES

For a limited time, I will give you the source code for a low price. DOWNLOAD THE SOURCE CODE LEVEL you desire by clicking its the BUY button below.

FEATURESLEVEL 1
PDO extension usedYES
Create productYES
Read productYES
Update productYES
Delete productYES
Price display with dollar signYES
SQL file in “dev” folderYES
Download Now

12.0 Download LEVEL 2 Source Code

FEATURESLEVEL 2
All features of LEVEL 1 aboveYES
HTML5 (font-end) validation for create productYES
HTML5 (font-end) validation for update productYES
Category selection for create and update product.YES
Bootstrap UIYES
Buttons with GlyphiconsYES
PaginationYES
Search products by name or descriptionYES
HTML5 (font-end) validation for search productYES
Pagination in searchYES
Allow user to input page number (read and search list)YES
Export / download records to CSVYES
Price display with dollar sign, comma and decimal pointYES
Multiple deleteYES
Create & update record with file uploadYES
Download Now

13.0 Download LEVEL 3 Source Code

FEATURESLEVEL 3
All features of LEVEL 1 and 2 aboveYES
Bootstrap navigation barYES
Select category in navigationYES
Higlight category in navigationYES
Create categoryYES
Read categoryYES
Update categoryYES
Delete categoryYES
View products by categoryYES
Pagination for categoryYES
Search categoryYES
Pagination for category searchYES
Server side validation for create product & categoryYES
Server side validation for update product & categoryYES
Sorting by fieldsYES
Pagination for sorting by fieldsYES
jQuery UI enabledYES
Search product by date range – record date createdYES
Pagination for earch product by date rangeYES
jQuery UI calendar for picking dateYES
Download Now

14.0 Download ALL LEVELS

This means you will download the LEVEL 1, 2 and 3 source codes in separate packages. All check-boxes below must be checked and then you click the “Download Now” button.

Download Now

Do you need more reasons to get it?

MORE REASONS TO DOWNLOAD THE CODEALL
Use new skills for your multiple projectsYES
Save huge amount of time learning Bootstrap and PHPYES
Code examples are direct to the pointYES
Well explained and commented source codeYES
Fast and friendly email supportYES
Free source code updatesYES

If you have any more questions, please feel free to contact me now. You can do it by sending a message on our official Facebook page, or via my email mike@codeofaninja.com

Thanks for supporting our website and projects here at codeofaninja.com!

15.0 Online Resources

16.0 What’s Next?

After learning from this PHP CRUD tutorial, we can go one step higher. We can do this by learning object-oriented programming (OOP) in PHP.

Object-oriented programming (OOP) is a programming language model organized around objects rather than “actions” and data rather than logic. This is very exciting.

To learn more, we created the next tutorial just for you! Let’s go and learn:
PHP, MySQL and OOP CRUD Tutorial – Step by Step Guide!

17.0 Related Source Codes

The following related source code tutorials can be very useful to further improve your skills.

PHP CRUD Tutorials
1.0 PHP & MySQL CRUD Tutorial for Beginners
2.0 PHP, MySQL & OOP CRUD Tutorial
3.0 PHP, MySQL & AJAX CRUD Tutorial
4.0 PHP, MySQL & AngularJS CRUD Tutorial
5.0 PHP, MySQL & React CRUD Tutorial
PHP Shopping Cart Tutorials
1.0 Shopping Cart Tutorial using SESSIONS
2.0 Shopping Cart Tutorial using COOKIES
3.0 Shopping Cart Tutorial using MySQL
PHP Web Application Source Codes
PHP Shopping Cart System
PayPal Integration In PHP
PHP Login System

18.0 Some Notes

#1 Found An Issue?

If you found a problem with this code, we can solve it faster via Email or FB message, please send me a message via email mike@codeofaninja.com, or via our official Facebook page!

Please be more detailed about your issue. Best if you can provide an error message and your test or page URL. Thanks!

Please feel free to comment if you have any questions, suggestions, found something wrong or want to contribute to this code.

#2 Become a true Ninja!

We constantly add new tutorials and improve our existing tutorials and source codes. Be one of the first to know an update by subscribing to our FREE newsletter. Get a FREE EBOOK as well. CLICK HERE TO SUBSCRIBE FOR FREE!

#3 Thank You!

Please share this post if you think this is a useful PHP CRUD Tutorial. We hope you mastered how to create, read, update and delete database records and more. Thanks for learning here in codeofaninja.com!