PHP CRUD Tutorial for Beginners – Step By Step Guide!

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

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

Download LEVEL 1 Source Code

FEATURES LEVEL 1
PDO extension used YES
Create product YES
Read product YES
Update product YES
Delete product YES
Price display with dollar sign YES
SQL file in “dev” folder YES
LEVEL 1 – Buy to download now

Download LEVEL 2 Source Code

FEATURES LEVEL 2
All features of LEVEL 1 above YES
HTML5 (font-end) validation for create product YES
HTML5 (font-end) validation for update product YES
Category selection for create and update product. YES
Bootstrap UI YES
Buttons with Glyphicons YES
Pagination YES
Search products by name or description YES
HTML5 (font-end) validation for search product YES
Pagination in search YES
Allow user to input page number (read and search list) YES
Export / download records to CSV YES
Price display with dollar sign, comma and decimal point YES
Multiple delete YES
Create & update record with file upload YES
LEVEL 2 – Buy to download now

Download LEVEL 3 Source Code

FEATURES LEVEL 3
All features of LEVEL 1 and 2 above YES
Bootstrap navigation bar YES
Select category in navigation YES
Higlight category in navigation YES
Create category YES
Read category YES
Update category YES
Delete category YES
View products by category YES
Pagination for category YES
Search category YES
Pagination for category search YES
Server side validation for create product & category YES
Server side validation for update product & category YES
Sorting by fields YES
Pagination for sorting by fields YES
jQuery UI enabled YES
Search product by date range – record date created YES
Pagination for earch product by date range YES
jQuery UI calendar for picking date YES
LEVEL 3 – Buy to download now

Why download?

Do you need more reasons to download it?

MORE REASONS TO DOWNLOAD THE CODE ALL
Use new skills for your multiple projects YES
Save huge amount of time learning Bootstrap and PHP YES
Code examples are direct to the point YES
Well explained and commented source code YES
Fast and friendly email support YES
Free source code updates YES

How To Run The Source Code?

We highly recommend you follow and study our well-detailed, step-by-step tutorial above first. Nothing beats experience when it comes to learning.

But we believe you will learn faster if you’ll see the final source code as well. We consider it as your additional guide.

Imagine the value or skill upgrade it can bring you. The additional income you can get from your work, projects, or business. The precious time you save. Isn’t that what you want?

By now, you need to download our source codes. To do it, use any download buttons in the next few sections below.

Once you downloaded the source codes, here’s how you can run them.

  1. Extract the files to your server directory.
  2. Create your database using PhpMyAdmin, database name is “php_beginner_crud_3” – if you downloaded our LEVEL 3 source code.
  3. Import the SQL file called “php_beginner_crud_3.sql” located in the “dev” folder.
  4. You run index.php on the browser.

If you have any more questions, please feel free to contact me now. You can do it by sending a message to our email [email protected]

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

Online Resources

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!

Related Tutorials

We listed all our high quality full-stack web development tutorials here: Click here.

Notes

Found An Issue?


If you found a problem with this code, please send us an email. Before you send an email, please read our our code of conduct. Our team's email address is [email protected]


Please be descriptive about your issue. Please provide the error messages, screenshots (or screen recording) and your test URL. Thanks!


Subscribe to CodeOfaNinja


Receive valuable web development tutorials to your email. Subscribe now for FREE!



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!

By Mike Dalisay

I'm Mike Dalisay, a pro web developer since 2010. I love web development. Improving our tutorials and source codes makes me happy. Do you want to suggest an edit to our tutorial? Got something to ask about our source codes? You may use our comments section below or email our team at [email protected]

102 comments

  1. Absolutely a fantastic tutorial. Most comprehensive but still easy to follow. Well thought out and implemented. Many thanks.

    How would I edit/change the image when the user clicks the Edit button please?

    Reply

  4. Hi @samyakjhaveri, thanks for the kind words! To answer your question. those tables have their own fields or properties. So you need to create separate objects for each of your tables. You just follow the pattern on the tutorial above.

    Reply

  6. Hi @benecio, thank you for appreciating our efforts! About the upload file code, which file did you try to encode? Your can try to change $allowed_file_types to restrict the file types. Also, would you tell us the error message you encountered?

    Reply

  7. The crud prject. It fit right into a codeigniter page! 1st time. I do have a q. Right now the screen is full width. Where can I look to decrease this in horizontal size?
    Also noticed there is no responsive code- might be time for an updat.

    Reply

    1. Hi @disqus_ELFKtsxWwF, you can try to play your CSS to decrease the horizontal size. The code above is designed for desktop dashboards. It is not recommended for mobile viewing.

      Reply

  9. Hi @disqus_USvrYi8gaj, thanks for the kind words! about your question, if you were able to save the image file name and you see the file in your directory, it should work. Did you add the “image” filed in your query?

    Reply

  10. Hi @disqus_TxsjcmmY1N, thanks for the kind words! I’m unable to replicate the issue. Which section did you follow that your code stopped working? 11.2? 11.4?

    Reply

  11. Mike Dalisay, thanks to your great and brilliant idea. This ultimate website will come a long way for my programming passion. More power to your programming career. Hoping for more updates!

    Reply

  12. In update.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’;
    ===
    Codes already declared at the start where it needs to replace . so, i don’t think it need’s to re-declare it again in section.

    You have a very nice Tutorials here. Thanks

    Reply

    1. Hello @vrsotto, thanks for the catch! I updated the tutorial. Thanks for the kind words about our tutorials as well. Please share our site to your friends if you have time. :)

      Reply

    1. Hi @footroot, that is not normal but we made this tutorial as simple as possible for beginners. In the real world, you need to make input validations before saving anything in the database.

      One tip I can give is you make use of the “required” attribute for input fields. For example, instead of this:

      use this:

      Reply

  16. Hello John,

    First i have to thank you for your effort on this tutorial, it’s really a great job. But then, each time I try to add a new record, this is the error message I get “Warning: PDOStatement::execute() [function.PDOStatement-execute]: SQLSTATE[HY093]: Invalid parameter number: number of bound variables does not match number of tokens in C:wampwwwCRUDcreate.php on line 85.” Followed by this “Unable to save record”. Please kindly help me to trace where I’m getting it all wrong.

    I would’t if you can reply me via my e-mail direct: [email protected].

    Looking forward to hearing from you.

    Paul

    Reply

      1. Thank you, but I’m sure its from that database credentials area where there is something like “port:8889” in the config.php file. I’m using phpMyAdmin and MySQL and I have changed the remaining stuffs like the username, password, and the name of the database to according to my own settings here, but if I’m to change that port number, what am I to change it to? Thank you for your usual care as I humbly await your response.

      3. But, sorry, this is not about the CRUD, its about the blog (creating blog from scratch)

    1. Hi @disqus_DsDA6V5ewK, you can add that feature on our source codes. Currently, we don’t have that feature but I’m sure our source code will get you started.

      You just have to add an extra query when the form was submitted. This extra query will check whether a value exists or not in the database.

      Reply

  18. Advice: When you create new record, you need redirect to read page with message to avoid insert auto records when refresh page. :)

    Reply

  21. Thank you Mike, tutorials are excellent.
    I have two questions.
    1. I do not see index.php. How to start example ?.
    2. Why can not save a picture in the form of an Update Product?

    Thank you.

    Reply

    1. Hello @edovoki , you’re welcome and thanks for the kind words!

      To answer your questions:

      1. We use read.php instead of index.php file.

      2. That feature is part of our LEVEL 2 source code. See section 12.0 above.

      Reply

  22. I just copied and pasted it and everything working perfectly well. Now study and study and study. Thank you very much.
    Odilon Faustino, São Paulo, Brazil.

    Reply

    1. Hello @odilonfaustino , glad to know it worked perfectly well for you! You are right, we’ll need to study more than copy and paste. You’re welcome and thanks for sharing your thoughts!

      Reply

  23. In the 7.2, you need to include the following after the start of the 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

    >>>>>>>>>include ‘config/database.php’; <<<<<<<<<<<<<<<<<<<<<<

    $id=isset($_GET['id']) ? $_GET['id'] : die('ERROR: Record ID not found.');

    Reply

  26. Hello I have just resend an email I sent in january which i did not get any reply. I bought this tutorial but lost the files and would like to get the download link again, could you please check my email please. Thank you.

    Reply

    1. Hello @disqus_haQAcKkKml, I searched your name and email on two of my emails but I got no search result. Are you sure you sent it in the correct email? My email is [email protected], please forward your Sellfy thank you email so I can send you the download link. Thank you!

      Reply

  27. Hello! Thanks for the code! Exactly what I was looking for.

    Only one problem, I can’ t edit the product list:
    ERROR MESSAGE: Notice: Undefinded index: id in F:/wamp/www/…

    I paid for and downloaded level 3.

    Thanks for your help!

    Reply

      1. I was able to figure it out. There was a “s” at the end of product. Once removed the code work fine. Thanks for getting back to me

        I do have another question though… Is there a way to have paging above and below the product list. I’ve been messing around and can’t seam to figure that out.

  28. $query = “SELECT id, name, price FROM products WHERE id IN ({$ids}) order by name”;

    whent the WHERE clause is remove then it works but the unsetting the array variable fails and dont remove the variable. kindly help

    Reply

  29. the foreach function in cart.php does’nt work

    $ids = “”;
    foreach($_SESSION[‘cart_items’] as
    $id=>$value){
    $ids = $ids . $id;
    }

    Reply

  30. Hello Asım Sinan Yüksel, thanks for bringing this to our attention. But we are unable to replicate your issue. Which source code level are you referring to?

    We have the following for categories table:


    CREATE TABLE IF NOT EXISTS `categories` (
    `id` int(11) NOT NULL AUTO_INCREMENT,
    `name` varchar(128) NOT NULL,
    `description` text 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=10 ;

    Reply

  31. Categories section is not working because description column is missing. In current db, there are id,name,created,modified. However it should be id,name,description,created

    Reply

  32. This is a great tutorial, would it be possible to duplicate the CRUD features without having to duplicate the code? or would it be better to use a framework like symfony?

    Reply

  33. A couple of days ago I posted:

    I purchased the all three levels. In Level 3, when I enter a search
    parameter “test” in search_products.php, I get the following. Please
    advise:

    Notice: Undefined variable: order in C:xampphtdocsphp-beginner-crud-level-3read_products_template.php on line 196

    Notice: Undefined index: total_rows in C:xampphtdocsphp-beginner-crud-level-3read_products_template.php on line 203

    Now I am learning that I can’t DELETE A CATEGORY even though you stated that this was fixed in a post below. Can you give me the code update to address the two errors I have posted above. i am using your tutorial code for something another project and would like to make the updates manually.

    By the way I have having these issues in Level 3 CRUD.

    Thanks

    Reply

  34. I purchased the all three levels. In Level 3, when I enter a search parameter “test” in search_products.php, I get the following. Please advise:

    Notice: Undefined variable: order in C:xampphtdocsphp-beginner-crud-level-3read_products_template.php on line 196

    Notice: Undefined index: total_rows in C:xampphtdocsphp-beginner-crud-level-3read_products_template.php on line 203

    Reply

  35. Also is the file create.php or add.php?

    This is very confusing. Nothing is working because the file names are mixed up.

    Please explain step 13.

    $action = isset($_GET[‘action’]) ? $_GET[‘action’] : “”;

    // if it was redirected from delete.php
    if($action==’deleted’){
    echo “Record was deleted.”;
    }

    Isn’t this in step 8. Do we need it twice?

    In update.php description should be a textarea like in create.php

    Changed update.php to textarea

    Description

    I finally figured it out and got it working! Please respond to my comments.

    Thank you.

    Reply

  36. Mike,

    Thank you for the great tutorial!

    I’m confused. In step 7 is the file index.php or read.php?

    2.0 Project File Structure shows read.php, step 7 shows both, Step 13 refers to index.php

    Is it read.php and you referred to it as index.php by mistake?

    Reply

    1. Hello @irarabinowitz , thanks for bringing this to our attention. We’ve made some mistakes when we updated the tutorial recently.

      I apologize for the confusion, we’ve fixed the mixed up file names.

      add.php should be create.php, read.php should be index.php, edit.php should be update.php.

      We’ve updated the tutorial above. Thanks again!

      Reply

      1. Thank you for the quick response.

        We don’t need step 13 it is already in step 8.

        Also update.php on description should be a textarea

        Description

        This is a great tutorial!

      2. Ira Rabinowitz, I removed the ‘action’ part of the code in step 8 so that step 13 will be needed. I think it has a better context that way.

        I’ve updated step 11 to change ‘description’ input type to a text area.

        Thanks for your help and kind words!

  38. Hello All,
    I am new at this and it is my very first project. I got most of it working but keep geting this error whne I save an updated record:

    PDOStatement::execute(): SQLSTATE[HY093]: Invalid parameter number: parameter was not defined

    // Execute the query
    if($stmt->execute()){
    echo “Record was updated.”;
    }else{
    echo ‘Unable to update record. Please try again.’;
    }

    Any help would be appreciated.

    Reply

  39. Solved the problem of fatal error in code below

    include ‘libs/db_connect.php’;

    // delete sql query

    $sql = “DELETE FROM users WHERE id = “. htmlspecialchars($_GET[“id”]);

    // prepare the sql statement

    if($stmt = $con->prepare($sql)){

    // execute the delete statement

    if($stmt->execute()){

    // redirect to index page

    // parameter “action=deleted” is used to show that something was deleted

    header(‘Location: index.php?action=deleted’);

    } else {

    die(“Unable to delete.”);

    }

    }

    Reply

  40. Solving the Fatal error in line 8 in the delete.php

    include ‘libs/db_connect.php’;
    // delete sql query
    $sql = “DELETE FROM users WHERE id = ?”;
    // prepare the sql statement
    if($stmt = $con->prepare($sql)){
    // bind the id of the record to be deleted
    $stmt->bindParam(‘:id’, $_GET[‘id’]);
    // execute the delete statement
    if($stmt->execute()){
    // redirect to index page
    // parameter “action=deleted” is used to show that something was deleted
    header(‘Location: index.php?action=deleted’);
    } else {
    die(“Unable to delete.”);
    }
    }

    Reply

  41. Hi John.

    The CRUD project is working except the Delete function. I noticed the Delete in PDO is the same with MySQLI procedure. I can’t able to find the bug for successful deletion of selected record.

    Can you update that? Thank you

    Reply

    1. Hello guys, thanks for reporting this to me, I’m gonna update this soon, I will let you know here, thanks for your patience!

      Reply

  42. i cant delete a row with delete.php
    i get the following error :
    Notice: Undefined variable: mysqli in C:xampphtdocscoba_pdodelete.php on line 8

    Fatal error: Call to a member function prepare() on a non-object in C:xampphtdocscoba_pdodelete.php on line 8

    Reply

  45. Hi, I am already bought your script source code, I uploaded the pdo bootstrap version, config the database in config file and import your sql file, but, is giving me 500 Internal Server Error, can you help me?? Thank you

    Reply

  46. Hello,

    I’m having trouble getting ‘Delete’ to work. (PDO) when I click delete, nothing happens. Opening localhost/delete.php redirects to index.php so that seems to work like it should, right?
    The tutorial has an if statement calling $result in delete.php while that variable is never declared, is that the problem?

    If so, how would I go about fixing this?
    This is my very first PHP project, so I apologize if I’m doing something wrong.
    Kind regards,

    Tom

    Reply

Leave a comment

Your email address will not be published.

This site uses Akismet to reduce spam. Learn how your comment data is processed.

Back to top