PHP CRUD Tutorial for Beginners – Step By Step Guide!
https://www.codeofaninja.com/2011/12/php-and-mysql-crud-tutorial.html
Previously, we learned how to run your first PHP script. This time, we we learn CRUD operations with PHP and MySQL. CRUD stands for Create, Read, Update and Delete database records.
We will cover the following topics
1.0 Overview
2.0 Program output
3.0 File structure
4.0 Prepare the database
4.1 Create the database
4.2 Create the database table
4.3 Dump sample data on the table
4.4 Create database connection file
4.5 Output
5.0 Create or insert record in PHP
5.1 HTML5 boilerplate for create.php
5.2 HTML form to input new record data
5.3 Code create a new record
5.4 Output
6.0 Read records in PHP
6.1 Basic HTML code for index.php
6.2 Read records from the database
6.3 Add HTML table with heading
6.4 Add HTML table body
6.5 Output
7.0 Read one record in PHP
7.1 Basic HTML code for read_one.php
7.2 Read records from the database
7.3 Display record details
7.4 Output
8.0 Update record in PHP
8.1 Basic HTML code for update.php
8.2 Read a record by ID parameter
8.3 HTML form to update a record
8.4 Code to update the record
8.5 Output
9.0 Delete record in PHP
9.1 Tell the user if record was deleted
9.2 JavaScript to confirm record deletion
9.3 Delete record from the database
10.0 Pagination in PHP
10.1 Set pagination variables
10.2 Add LIMIT clause in SELECT query
10.3 Count total number of records
10.4 Include paging file
10.5 Create paging.php
10.6 Add first page button
10.7 Add clickable page numbers
10.8 Add last page button
10.9 Output
11.0 File upload in PHP
11.1 Add HTML "file" field
11.2 Add "image" field
11.3 Set variables for file upload
11.4 Make sure submitted file is a real image
11.5 Make sure certain file types are allowed
11.6 Make sure file does not exist
11.7 make sure submitted file is not too large
11.8 Make sure the 'uploads' folder exists
11.9 Try to upload the file
11.10 Output
12.0 Show uploaded image in PHP
12.1 Add image field in query
12.2 Add HTML image tag
12.3 Output
13.0 How to run the source code?
14.0 Download LEVEL 1 source code
15.0 Download LEVEL 2 source code
16.0 Download LEVEL 3 source code
17.0 Download ALL LEVELS
18.0 Online resources
19.0 What's next?
20.0 Related tutorials
21.0 Notes
Before we start, we want to let you know that your feedback is important to us!
If you have a positive feedback about our work, please let us know. If there's a section in this tutorial that is confusing or hard to understand, we consider it as a problem. Please let us know as well.
Write your positive feedback or detailed description of the problem in the comments section below. Before you write a comment, please read this guide and our code of conduct. Thank you!
1.0 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:
Using the MySQL extension.
Using the MySQLi extension.
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.
2.0 PHP CRUD TUTORIAL PROGRAM OUTPUT
We usually have three LEVELS of source code output. But WHY? Because I believe in "Learning Progression" to ensure efficient learning. Learn more
Below are some screenshots of our script's output. You can click an image to view the larger version of it. Use the left and right arrow to navigate through the screenshots.
Please note that the following images are just output previews. New features might be added already the time you are reading this.
2.1 LEVEL 1 source code output
#5 Product Was Deleted (After Clicking OK On Pop Up)#1 Read Products#2 Read One Product#3 Create Product
#4 Update Product
#5 Product Was Deleted (After Clicking OK On Pop Up)#1 Read Products
2.2 LEVEL 2 source code output
#7 Delete Selected Records (Pop Up When No Records Were Checked)#1 Read Records#2 Read One Record#3 Search Records
#4 Create Record
#5 Update Record#6 Delete Record#7 Delete Selected Records (Pop Up When No Records Were Checked)#1 Read Records
2.3 LEVEL 3 source code output
#15 Search Category#1 Read Products#2 Read One Product#3 Read Product By Category
#4 Search Products
#5 Search Products By Date Created#6 Sort Products By Price#7 Create Product#8 Update Product#9 Delete Product#10 Delete Selected (Pop Up When No Records Were Checked)#11 Read Categories#12 Create Category#13 Update Category#14 Delete Category#15 Search Category#1 Read Products
The LEVEL 2 and LEVEL 3 source code outputs proves that you can add and customize more features. It's easier and faster if you will learn by following our tutorial below.
Downloading our source codes is your huge advantage as well. For now, let's proceed to the step by step tutorial of our LEVEL 1 source code. Enjoy!
3.0 PROJECT FILE STRUCTURE
Our PHP CRUD tutorial will contain the following main files.
dev/products.sql – contains the database table structure and sample data used in this project. Once you created your database in PhpMyAdmin, you can import this file.
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.
4.0 PREPARE THE DATABASE
4.1 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.
4.2 Create the database table
Next, run the following SQL code. This is to create our products database table. If you're not sure how to do this, take a look at this resource.
--
-- Table structure for table `products`
--
CREATE TABLE IF NOT EXISTS `products` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(128) NOT NULL,
`description` text NOT NULL,
`price` double NOT NULL,
`created` datetime NOT NULL,
`modified` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP,
PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=9 ;
4.3 Dump sample data on the table
Again, run the following SQL code on your PhpMyAdmin. This will insert the sample data or record on our products database table.
--
-- Dumping data for table `products`
--
INSERT INTO `products` (`id`, `name`, `description`, `price`, `created`, `modified`) VALUES
(1, 'Basketball', 'A ball used in the NBA.', 49.99, '2015-08-02 12:04:03', '2015-08-06 06:59:18'),
(3, 'Gatorade', 'This is a very good drink for athletes.', 1.99, '2015-08-02 12:14:29', '2015-08-06 06:59:18'),
(4, 'Eye Glasses', 'It will make you read better.', 6, '2015-08-02 12:15:04', '2015-08-06 06:59:18'),
(5, 'Trash Can', 'It will help you maintain cleanliness.', 3.95, '2015-08-02 12:16:08', '2015-08-06 06:59:18'),
(6, 'Mouse', 'Very useful if you love your computer.', 11.35, '2015-08-02 12:17:58', '2015-08-06 06:59:18'),
(7, 'Earphone', 'You need this one if you love music.', 7, '2015-08-02 12:18:21', '2015-08-06 06:59:18'),
(8, 'Pillow', 'Sleeping well is important.', 8.99, '2015-08-02 12:18:56', '2015-08-06 06:59:18');
As you may have noticed, steps 1 and 2 are both SQL queries. Yes, they can run at the same time. But I wanted it to be on separate steps to emphasize those SQL queries' purpose.
4.4 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.
getMessage();
}
?>
4.5 Output
We have set up the database successfully! The only output we have so far is the database, database table and sample records we setup via PhpMyAdmin.
Let's proceed to the next section below.
5.0 CREATE OR INSERT RECORD IN PHP
5.1 HTML5 boilerplate for create.php
We use 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.
PDO - Create a Record - PHP CRUD Tutorial
Create Product
5.2 HTML form to input new record data
Now we are going to start answering the question: how to create a record with PDO?
The code below will create an HTML form with input fields that matches the fields in the database. Replace comment of the previous section with the following code.
5.3 Code to create a new record
We are still working in the create.php file. Once the user filled out the form and clicked the save button, the code below will save it to the MySQL database.
Replace comment of the previous section with the following code.
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 "
Record was saved.
";
}else{
echo "
Unable to save record.
";
}
}
// show error
catch(PDOException $exception){
die('ERROR: ' . $exception->getMessage());
}
}
?>
5.4 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 user fill out the form.
When user submitted the form.
New record added to database.
6.0 READ RECORDS IN PHP
6.1 Basic HTML code for index.php
Create 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.
PDO - Read Records - PHP CRUD Tutorial
Read Products
6.2 Read records from the database
This time we will read records from the database.
Replace comment of the previous section with the following code.
prepare($query);
$stmt->execute();
// this is how to get number of rows returned
$num = $stmt->rowCount();
// link to create record form
echo "Create New Product";
//check if more than 0 record found
if($num>0){
// data from database will be here
}
// if no records found
else{
echo "
No records found.
";
}
?>
6.3 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.
echo "
";
6.4 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 "
";
echo "
{$id}
";
echo "
{$name}
";
echo "
{$description}
";
echo "
${$price}
";
echo "
";
// read one record
echo "Read";
// we will use this links on next part of this post
echo "Edit";
// we will use this links on next part of this post
echo "Delete";
echo "
";
echo "
";
}
6.5 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.
7.0 READ ONE RECORD IN PHP
7.1 Basic HTML code for read_one.php
Create 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 read_one.php file.
PDO - Read One Record - PHP CRUD Tutorial
Read Product
7.2 Read one record from the database
The following code is how we retrieve a single database record.
Replace comment of the previous section with the following code.
prepare( $query );
// this is the first question mark
$stmt->bindParam(1, $id);
// execute our query
$stmt->execute();
// store retrieved row to a variable
$row = $stmt->fetch(PDO::FETCH_ASSOC);
// values to fill up our form
$name = $row['name'];
$description = $row['description'];
$price = $row['price'];
}
// show error
catch(PDOException $exception){
die('ERROR: ' . $exception->getMessage());
}
?>
7.3 Display record details
The following HTML table will hold and display the details of a single database record.
Open read_one.php file.
Replace comment with the following code.
7.4 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.
8.0 UPDATE RECORD IN PHP
8.1 Basic HTML code for udpate.php
Create 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.
PDO - Update a Record - PHP CRUD Tutorial
Update Product
8.2 Read a record by ID parameter
We have to get the record ID and store it in the $id variable. We access the $_GET['id'] variable to do it.
What we are trying to do here is to get the related data based on the given record ID. This is the way to auto-fill the HTML form with existing data from the database.
Replace comment of the previous section with the following code.
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());
}
?>
8.3 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 given ID parameter.
Open update.php file. Replace comment with the following code.
8.4 Code to update the record
The following code will save the changes in the database.
That is if the user change some value on the form and hit the Save Changes button.
Replace comment of the previous section with the following code.
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 "
Record was updated.
";
}else{
echo "
Unable to update record. Please try again.
";
}
}
// show errors
catch(PDOException $exception){
die('ERROR: ' . $exception->getMessage());
}
}
?>
8.5 Output
To update a database record, run 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.
9.0 DELETE RECORD IN PHP
9.1 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 "
Record was deleted.
";
}
9.2 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 comment with the following code.
9.3 Delete record from the database
The code below will delete a record from the database using the given ID parameter.
This answers the question: how to delete a record with PDO?
Create new delete.php file, place the following code and save it.
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());
}
?>
9.4 Output
Once user clicks any Delete button, it will show a confirmation pop up.
If user clicks the "OK" button, record will be deleted from the database. It will tell the user via message prompt that the record was deleted.
Record is gone in the database as well.
10.0 PAGINATION IN PHP
Please note that this is a bonus section and is not included in the LEVEL 1 source code download. We will have to add or remove some codes we've done above so that pagination will work.
10.1 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;
10.2 Add LIMIT clause in SELECT query
This will enable paginated requests to database. Still on 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();
10.3 Count total number of records
Counting the total number of records will help calculate the correct pagination numbers.
Below the closing table tag in 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'];
10.4 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 section 10.1 to 10.3 when you use paging.php file.
10.5 Create paging.php
Create new paging.php file. Place the following code and save it.
";
// first page button will be here
// clickable page numbers will be here
// last page button will be here
echo "";
?>
10.6 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 "
";
}
10.7 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 "
";
}
}
}
10.8 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 "
";
}
10.9 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.
11.0 FILE UPLOAD IN PHP
Now we are going to add a file upload feature when creating a record.
11.1 Add HTML "file" field
Open create.php file and scroll down to the form. Find the opening "form tag and enable the file upload by changing it to:
No comments:
Post a Comment