PHP CRUD Tutorial for Beginners – Step By Step Guide!

Previously, we learned how to make our application look good using the Bootstrap CSS framework. This time, we will learn CRUD operations with PHP and MySQL. CRUD stands for Create, Read, Update and Delete database records.

Table of Contents

Overview

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 in PHP and MySQL.
  • You are 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.

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.
  • 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.
  • index.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 index.php page.

Prepare the database

Create the database

On your PhpMyAdmin, create a database named "php_beginner_crud_level_1".

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

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 ;

Dump sample data on the table

Again, run the following SQL code on your PhpMyAdmin. This will insert the sample data or record it 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.

Create database connection file

This section will answer the question: how to connect to MySQL database with PDO?

  • Create php-beginner-crud-level-1 folder and open it.
  • Create config folder and open it.
  • Create database.php file.
  • Place the following code inside it.
<?php
// used to connect to the database
$host = "localhost";
$db_name = "php_beginner_crud_level_1";
$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();
}
?>

Output

We have set up the database successfully! The only output we have so far is the database, database table, and sample records we set up via PhpMyAdmin.

Let's proceed to the next section below.

Create or insert record in PHP

HTML5 boilerplate for create.php

We use the Bootstrap user interface for this project. If you are not familiar with Bootstrap, please learn our Bootstrap Tutorial for Beginners.

  • Go back to php-beginner-crud-level-1 folder.
  • Create a new create.php file.
  • Place the code following code inside the create.php file.
<!DOCTYPE HTML>
<html>
<head>
    <title>PDO - Create a Record - PHP CRUD Tutorial</title>
    <!-- Latest compiled and minified Bootstrap CSS -->
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" />
</head>
<body>
    <!-- container -->
    <div class="container">
        <div class="page-header">
            <h1>Create Product</h1>
        </div>
    <!-- html form to create product will be here -->
    </div> <!-- end .container -->
<!-- jQuery (necessary for Bootstrap's JavaScript plugins) -->
<script src="https://code.jquery.com/jquery-3.2.1.min.js"></script>
<!-- Latest compiled and minified Bootstrap JavaScript -->
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
</body>
</html>

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. Replace <!-- html form to create product will be here --> comment of the previous section with the following code.

<!-- PHP insert code will be here -->
<!-- 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='index.php' class='btn btn-danger'>Back to read products</a>
			</td>
		</tr>
	</table>
</form>

Code to create a new record

We are still working on the create.php file. Once the user filled out the form and clicked the save button, the code below will save it to the MySQL database.

Replace <!-- PHP insert code will be here --> comment of the previous section with the following code.

<?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());
	}
}
?>

Output

Congrats! For the first time, we can now see an output on a web page. Go to this URL:

http://localhost/php-beginner-crud-level-1/create.php

You will see the output that looks like the following images.

When the user fills out the form.

When the user submitted the form.

A new record added to the database.

Read records in PHP

Basic HTML code for index.php

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

Place the following code inside the index.php file.

<!DOCTYPE HTML>
<html>
<head>
	<title>PDO - Read Records - PHP CRUD Tutorial</title>
	<!-- Latest compiled and minified Bootstrap CSS -->
	<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" />
	<!-- custom css -->
	<style>
	.m-r-1em{ margin-right:1em; }
	.m-b-1em{ margin-bottom:1em; }
	.m-l-1em{ margin-left:1em; }
	.mt0{ margin-top:0; }
	</style>
</head>
<body>
    <!-- container -->
    <div class="container">
        <div class="page-header">
            <h1>Read Products</h1>
        </div>
        <!-- PHP code to read records will be here -->
    </div> <!-- end .container -->
<!-- jQuery (necessary for Bootstrap's JavaScript plugins) -->
<script src="https://code.jquery.com/jquery-3.2.1.min.js"></script>
<!-- Latest compiled and minified Bootstrap JavaScript -->
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
<!-- confirm delete record will be here -->
</body>
</html>

Read records from the database

This time we will read records from the database.

Replace <!-- PHP code to read records will be here --> comment of the previous section with the following code.

<?php
// include database connection
include 'config/database.php';
// delete message prompt will be here
// 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){
	// data from database will be here
}
// if no records found
else{
	echo "<div class='alert alert-danger'>No records found.</div>";
}
?>

Add HTML table with heading

This is the HTML table that will hold and display data from the database.

Replace // data from database will be here comment of the previous section with the following code.

//start table
echo "<table class='table table-hover table-responsive table-bordered'>";
	//creating our table heading
	echo "<tr>
		<th>ID</th>
		<th>Name</th>
		<th>Description</th>
		<th>Price</th>
		<th>Action</th>
	</tr>";
	// table body will be here
// end table
echo "</table>";

Add HTML table body

This part is where we will loop through the list of records from the database. This loop will create the rows of data on our HTML table.

Replace // table body will be here comment of the previous section with the following code.

// 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>
		<td>{$id}</td>
		<td>{$name}</td>
		<td>{$description}</td>
		<td>${$price}</td>
		<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>";
}

Output

Go to this URL:

http://localhost/php-beginner-crud-level-1/index.php

You will see the records retrieved from the database. It will look like the image below.

Read one record in PHP

Basic HTML code for read_one.php

Create a new read_one.php file. This is where we will read and display the details of a single database record.

Place the following code inside the read_one.php file.

<!DOCTYPE HTML>
<html>
<head>
	<title>PDO - Read One Record - PHP CRUD Tutorial</title>
    <!-- Latest compiled and minified Bootstrap CSS -->
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" />
</head>
<body>
    <!-- container -->
    <div class="container">
        <div class="page-header">
            <h1>Read Product</h1>
        </div>
        <!-- PHP read one record will be here -->
        <!-- HTML read one record table will be here -->
	</div> <!-- end .container -->
<!-- jQuery (necessary for Bootstrap's JavaScript plugins) -->
<script src="https://code.jquery.com/jquery-3.2.1.min.js"></script>
<!-- Latest compiled and minified Bootstrap JavaScript -->
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
</body>
</html>

Read one record from the database

The following code is how we retrieve a single database record.

Replace <!-- PHP read one record will be here --> comment of the previous section with the following 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';
// 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());
}
?>

Display record details

The following HTML table will hold and display the details of a single database record.

Open read_one.php file.

Replace <!-- HTML read one record table will be here --> comment with the following code.

<!--we have our html table here where the record 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='index.php' class='btn btn-danger'>Back to read products</a>
		</td>
	</tr>
</table>

Output

To read one record from the database, try to click any Read button from our index.php file.

You can also go to this URL:

http://localhost/php-beginner-crud-level-1/read_one.php?id=9

You will see an output like the image below.

Update record in PHP

Basic HTML code for udpate.php

Create a new 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?

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

<!DOCTYPE HTML>
<html>
<head>
	<title>PDO - Update a Record - PHP CRUD Tutorial</title>
    <!-- Latest compiled and minified Bootstrap CSS -->
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/css/bootstrap.min.css" />
</head>
<body>
    <!-- container -->
    <div class="container">
        <div class="page-header">
            <h1>Update Product</h1>
        </div>
        <!-- PHP read record by ID will be here -->
        <!-- HTML form to update record will be here -->
    </div> <!-- end .container -->
<!-- jQuery (necessary for Bootstrap's JavaScript plugins) -->
<script src="https://code.jquery.com/jquery-3.2.1.min.js"></script>
<!-- Latest compiled and minified Bootstrap JavaScript -->
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.7/js/bootstrap.min.js"></script>
</body>
</html>

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 the way to auto-fill the HTML form with existing data from the database.

Replace <!-- PHP read record by ID will be here --> comment of the previous section with the following 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';
// 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());
}
?>

HTML form to update a record

This form will show the data retrieved using the previous section's code.

We read a single record from the database, based on the given ID parameter.

Open update.php file. Replace <!-- HTML form to update record will be here --> comment with the following code.

<!-- PHP post to update record will be here -->
<!--we have our html form here where new record information can be updated-->
<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='index.php' class='btn btn-danger'>Back to read products</a>
			</td>
		</tr>
	</table>
</form>

Code to update the record

The following code will save the changes in the database.

That is if the user changes some value on the form and hits the Save Changes button.

Replace <!-- PHP post to update record will be here --> comment of the previous section with the following code.

<?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());
	}
}
?>

Output

To update a database record, run the index.php file and click any Edit button.

Or, go to this URL:

http://localhost/php-beginner-crud-level-1/update.php?id=9

You will see the result like the images below.

Update record form.

Submitted form.

Changes in the database.

Delete record in PHP

Tell the user if record was deleted

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

Open index.php file. Replace // delete message prompt will be here comment with the following code.

$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>";
}

JavaScript to confirm record deletion

The user clicks on the Delete button in index.php.

Next, he will verify the deletion by clicking OK on the pop-up.

That user activity will execute the following JavaScript code.

Open index.php file. Replace <!-- confirm delete record will be here --> comment with the following code.

<script type='text/javascript'>
// confirm record deletion
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>

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 a new delete.php file, place the following code and save 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: index.php?action=deleted');
	}else{
		die('Unable to delete record.');
	}
}
// show error
catch(PDOException $exception){
	die('ERROR: ' . $exception->getMessage());
}
?>

Output

Once the user clicks any Delete button, it will show a confirmation pop-up.

If the user clicks the "OK" button, the record will be deleted from the database. It will tell the user via message prompt that the record was deleted.

The record is gone in the database as well.

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.

Set pagination variables

The following variables are used to calculate the correct numbers for the LIMIT clause of our SELECT query.

We will see how our SELECT query will change later.

Place the following code below include 'config/database.php'; line of index.php file.

// 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;

Add LIMIT clause in SELECT query

This will enable paginated requests to the database. Still on the index.php file, 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();

Count total number of records

Counting the total number of records will help calculate the correct pagination numbers.

Below the closing table tag in the index.php file, 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'];

Include paging file

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

// paginate records
$page_url="index.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 will need to do:

$page_url="read_categories.php?";

You will have to follow the code pattern of sections 10.1 to 10.3 when you use the paging.php file.

Create paging.php

Create a new paging.php file. Place the following code and save it.

<?php
echo "<ul class='pagination pull-left margin-zero mt0'>";
// first page button will be here
// clickable page numbers will be here
// last page button will be here
echo "</ul>";
?>

Add first page button

Replace // first page button will be here comment of the previous section with the following code.

// first page button
if($page>1){
	$prev_page = $page - 1;
	echo "<li>
		<a href='{$page_url}page={$prev_page}'>
			<span style='margin:0 .5em;'>«</span>
		</a>
	</li>";
}

Add clickable page numbers

Open paging.php file.

Replace // clickable page numbers will be here comment with the following code.

// clickable page numbers
// 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'>
				<a href='javascript::void();'>{$x}</a>
			</li>";
		}
		// not current page
		else {
			echo "<li>
				<a href='{$page_url}page={$x}'>{$x}</a> 
			</li>";
		}
	}
}

Add last page button

Open paging.php file.

Replace // last page button will be here comment with the following code.

// last page button
if($page<$total_pages){
	$next_page = $page + 1;
	echo "<li>
		<a href='{$page_url}page={$next_page}'>
			<span style='margin:0 .5em;'>»</span>
		</a>
	</li>";
}

Output

Run index.php file on the browser:

http://localhost/php-beginner-crud-level-1/index.php

You should see the pagination buttons like the images below.

Read records page 1.

Read records page 2.

File upload in PHP

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

Add HTML “file” field

Open the create.php file and scroll down to the form. Find the opening "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>

Add “image” field

Still in create.php file. Scroll up and change the insert SQL query.

It should look like the following code. The new image field will store the file name of the submitted file.

// 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);

Using PhpMyAdmin, add an "image" field in the products table as well.

Set variables for file upload

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

echo "<div class='alert alert-success'>Record was saved.</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="";
}

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.

Place 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>";
}

Make sure certain file types are allowed

The following code will limit the allowed file types. Place 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>";
}

Make sure file does not exist

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

But just in case there's a file with the same name, tell the user. Place 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>";
}

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. Place 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>";
}

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. Place 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);
}

Try to upload the file

The move_uploaded_file built-in PHP function will place the uploaded file on the server directory.

Place 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'>
			<div>Unable to upload photo.</div>
			<div>Update the record to upload photo.</div>
		</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'>
		<div>{$file_upload_error_messages}</div>
		<div>Update the record to upload photo.</div>
	</div>";
}

Output

Form to create a product with file upload field.

When the form was submitted.

The "uploads" folder was created, with the uploaded file inside.

The file name was saved in the database.

Show uploaded image in PHP

Add image field in query

Open the 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);

Add HTML image tag

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>

Output

Click the Read One button of the record we created with a file upload.

You should see something like the images below.

Record with an image.

Record without image.

Download Source Codes

Unlock the full potential of your PHP and MySQL CRUD skills with our source code downloads.

Choose your download

Whether you're just starting or looking to take your knowledge to the next level, our BASIC and PRO options have everything you need to learn PHP CRUD operations.

Download our source codes today and accelerate your learning process. See the list of features below.

FEATURESBASICPRO
PDO extension used
Create product
Read product
Update product
Delete product
Bootstrap UI
Price display with a dollar sign
SQL file in the “dev” folder
HTML5 (font-end) validation for creating and updating a product-
Category selection for creating and updating a product.-
Buttons with Glyphicons-
Pagination-
Search products by name or description-
HTML5 (font-end) validation for search product-
Pagination in search-
Allow the user to enter the page number (read and search list)-
Export/download records to CSV-
Price display with comma and decimal point-
Multiple delete-
Create and update product records with file upload-
Bootstrap navigation bar-
Select and highlight a category in the navigation-
CRUD of product categories-
View products by category-
Search a category with pagination-
Server-side validation for creating and updating products & categories-
Sorting by fields with pagination-
Search product by date range with pagination-
jQuery UI calendar for picking a date-
Use the buttons below to download. ↓BASICPRO

Why download?

Our source code offers many benefits to help you master CRUD operations and take your skills to the next level. Here are a few of the many benefits you can expect:

  • Versatility: Use the skills you learn from our source code for multiple projects, saving you time and effort in the long run.
  • Time-saving: Forget spending hours learning Bootstrap and PHP on your own. Our code examples are direct and to the point, making them easy to understand and implement.
  • Clarity: Our source code is well explained and commented on, making it easy to follow and understand.
  • Support: Our fast and friendly email support is always available to help you with any questions.
  • Up-to-date: Our source code is constantly updated to ensure you always have access to the latest and most advanced features.

How To Run The Source Code?

After downloading and extracting the zip file, you will see a README.txt file. Open and follow the exact instructions on how to run the code.

If you have more questions, don't hesitate to contact our support team at [email protected].

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

What’s Next?

After learning from this PHP and MySQL CRUD tutorial for beginners, we can go one step higher. We can learn object-oriented programming (OOP) in PHP and MySQL.

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 following tutorial just for you! Let's go and learn: PHP OOP CRUD Tutorial: Object-oriented programming with PHP & MySQL

Online Resources

What students say?

Don't just take our word for it. See what our students have to say about our tutorials and source codes. We are proud to have helped many individuals and businesses to build their own applications. Here are a few of the testimonials from our satisfied students.

★★★★★ “Wow, I love you guys! The best web programming tutorial I’ve ever seen. So comprehensive, yet easy to follow. I love how you combine all necessary elements in such a neat structure.” ~ Olaug Nessa

★★★★★ “The fact that you’ve put it all together saves so much time and its worth buying the code. Makes me feel good supporting a developer like yourself. Keep up the good work!” ~ Dan Hudson

★★★★★ “Thanks for making these awesome tutorials! I bought your source codes. To be honest, it’s very readable code and helps me understand a lot of things and how it’s done in PHP. Thanks for that again.” ~ Michael Lammens

★★★★★ “Hey Mike, my name is Leonardo from Argentina. I’ve been reading your blog since like 4 months from now, and I really must say: your tutorials are very good, they has helped me in many of my works… Well, thank you very much man. I really admire your work.” ~ Leonardo

★★★★★ “Words can’t express how grateful I am for the work and the articles you post, had some troubles with doing somethings but your articles as per usual hit the hammer right on the head. They are a great way for expanding upon later too!” ~ Jeremy Smith

Got comments?

At codeofaninja.com, we strive to provide our readers with accurate and helpful PHP CRUD Tutorial for Beginners – Step By Step Guide! Your feedback is essential in helping us achieve this goal.

If you have encountered any issues with the code, have suggestions for improvement, or wish to provide praise, we welcome you to leave a comment below. Please be as descriptive as possible to address your concerns effectively and include any relevant error messages, screenshots, or test URLs.

We request that comments remain on-topic and relevant to the article above. If your question or comment pertains to a different topic, we recommend seeking assistance elsewhere.

Furthermore, we ask that you review our code of conduct before commenting to ensure that your feedback is constructive and respectful.

Thank you for taking the time to provide feedback and for supporting codeofaninja.com. Your contributions help us improve our tutorials and serve the developer community better.

Subscribe for FREE!

Improve your web development skills and stay ahead of the competition by subscribing to our tutorial series. Sign up for FREE and access exclusive, cutting-edge content delivered straight to your inbox.

Take advantage of the chance to elevate your skills and advance your web development career. Subscribe now.

Thank You!

We hope you've found our PHP CRUD Tutorial for Beginners – Step By Step Guide! helpful and informative. We understand that learning new programming concepts can be challenging, but we're glad we could make it easier for you.

Thank you for choosing to learn with us and for supporting codeofaninja.com! Consider sharing this tutorial with your friends and colleagues who may also be interested in learning about PHP CRUD Tutorial for Beginners – Step By Step Guide!

The more people know about our tutorials, the more we can help the developer community grow. Keep learning, keep coding, and keep growing as a developer. We can't wait to see what you'll create next!

Hi! I'm Mike Dalisay, the co-founder of codeofaninja.com, a site that helps you build web applications with PHP and JavaScript. Need support? Comment below or contact [email protected]

I'm also passionate about technology and enjoy sharing my experience and learnings online. Connect with me on LinkedIn, Twitter, Facebook, and Instagram.