CRUD Operations in PHP, PDO and MySQL

In this article, you will learn how to create CRUD Operations in PHP, PDO and MySQL. This CRUD Application in PHP discussion comes with examples and free source code which will help you create your own.

We’ll use PHP, PDO, and MySQL to create a comprehensive Create, Read, Update, and Delete application. We’ll build the app from the ground up, with no other frameworks necessary.

A CRUD application in PHP interacts with records in a table and is frequently used in connection with a database. In our program, we’ll be using MySQL as our database management system.

We’ll establish a database with a contacts table, which we’ll be able to manipulate in our CRUD application in PHP, PDO and MySQL. The contacts table will have names, emails, phone numbers, and other information.

Step 1. Getting Started for CRUD Application in PHP

We need to install our local web server and set up our app before we can start developing our CRUD application in PHP.

1.1 What This Tutorial Will Teach You

  • Create MySQL Records – Add new Contacts records to the table.
  • Reading MySQL Records and displaying them in an HTML table.
  • Update MySQL Records – In the Contacts table, update existing MySQL records.
  • Delete MySQL Records — Confirm and delete Contacts table records.
  • GET and POST Requests – Use an HTML form and URL parameters to send data to our program.
  • Prepared Statements – Prepared statements let us protect our SQL statements.

1.2 Requirements

  • Web Server – I recommend installing XAMPP on your local computer system since it contains MySQL, PHP, phpMyAdmin, and the PDO extension.
  • PHP – The most recent version of PHP is recommended, although earlier versions should operate just fine (skip if you installed XAMPP).
  • PDO Extension — Should be enabled by default if you’re using XAMPP, but if it’s not you’ll need to enable/install it.

1.3 File Structure & Setup

Navigate to C:\xampp\htdocs (XAMPP) and create the below directories and files.

phpcrudtutorial folder

What will be in each file:

  • index.php — Our CRUD app’s home page.
  • create.php — Use an HTML form to create new records and a POST request to deliver data to the server.
  • read.php — This file displays records from our database table and allows you to traverse through them using pagination.
  • update.php — Use an HTML form to update existing records and a POST request to deliver data to the server.
  • delete.php — Confirm and remove records based on their ID (GET request to get the ID).
  • functions.php — Basic templating and MySQL connection routines (so we don’t have to repeat code in each file).
  • style.css — This is the stylesheet for our app, and it will affect the way it looks.

Step 2. Crud Application in PHP, PDO and MYSQL

Creating the Database and setting-up Tables

We’ll store contacts in a MySQL database and retrieve them with PHP. If you’re using XAMPP, follow the steps below.

  • Go to http://localhost/phpmyadmin/
  • Under Create database, input phpcrud and select utf8_general_ci as the collation
  • Create by clicking the Create button.
  • Choose the recently created database.
  • Select the SQL tab and run the following SQL:

The above SQL will construct the contacts table, which we’ll utilize in our page. Also included in the SQL is sample data, which will be used for testing reasons to ensure everything is working well, and you may delete it afterwards.

The contacts database has six columns: id, name, email, phone, title, and created. The title column represents the role of each contact; you may alter it to anything you like; the sample data will use work roles as an example.

In phpMyAdmin, the database should look like the following:

Contact table structure

Step 3. Creating the Stylesheet (CSS3)

The stylesheet will change the appearance of our app, edit the style.css file and add the following code:

Change the design if you like, but this is what I came up with to make the CRUD app more attractive.

Step 4. Creating the CRUD Application in PHP Page

Finally, we can begin coding the CRUD page in PHP. Make sure you’ve completed the preceding steps and have the MySQL database available before we begin.

4.1 Creating the Functions

This file will include functions that we can use in all of our PHP files, allowing us to avoid writing the same code in each one. After all, the less code we have to write, the better, right? We’ll construct three functions: one to connect to the database, and the other two to generate header and footer templates that will display on every page we make and include the HTML layout.

Add the following code to the functions.php file:

Make sure to replace the MySQL connection details with your own; we’ll be using PDO to connect to MySQL, and PDO will make it easier to communicate with our MySQL database.

4.2 Creating the Home Page

The index.php file will be served when you go to http://localhost/phpcrud/; this will be our home page.

Add the following code to the index.php file:

This will provide a simple home page from which we can browse to the other pages. As you can see, we include the functions.php file and run the template functions we developed, which will add the header and footer code to our home page.

If we go to http://localhost/phpcrud/ now, we’ll see something like this:

CRUD Operations in PHP, PDO & MySQL – home.php

That’s pretty much all for the home page; feel free to add your own material; this page is only a portal to the rest of the site.

4.3 Creating the Read Page

This page will create an HTML table with records from our contacts table.

Add the following code to the read.php file:

We include the functions file once more, but this time we use the function pdo connect mysql to connect to our MySQL database; if the connection is successful, we may utilize the $pdo variable to perform queries.

We also create two more variables: the $page variable will determine which page the user is currently on, and the $records_per_page variable will be used to limit the number of records to display on each page. For example, if we limit the number of records to 5 and our contacts table has 10 records, there will only be 2 pages with 5 records on each page, and the user will be able to navigate between pages.

To the read.php file, add the following code:

The above code will choose data from the contacts table, which will be decided by the current page the user is on. The records will be arranged by the id column, but we can easily change it to created to sort the records by the create date instead.

For the aforementioned query, we’re also utilizing a prepared statement, which ensures that our query is safe (escapes user input data).

To the read.php file, add the following code:

Because the query does not involve user input variables, we don’t need to utilize a prepared statement in the above SQL query to acquire the total number of records in the contacts table.

To the read.php file, add the following code:

This is the read page template; the code iterates the contacts and adds them to the HTML table; when we browse to the read page, we’ll be able to read the entries in a table format.

Pagination has been added to the read page so that we may move between pages if you have more than 5 records (page 1, page 2, etc).

Font Awesome is used for the icons, therefore make sure it’s included in the header template function else the icons won’t show up.

Now, if we go to http://localhost/phpcrud/read.php, we can see the results. Here’s what we’ll see:

crud operations in php - read.php
CRUD Operations in PHP, PDO & MySQL – read.php

Now that we know how to show entries from our MySQL database, keep in mind that the buttons in the table (new, modify, and delete) will not operate since those pages have not yet been developed.

You may also get to the read page by clicking the Contacts link in the top bar.

4.4 Creating the Create Page

New records will be created and inserted into our Contacts database using the create page.

Add the following to the create.php file:

The preceding code checks if the POST array (form data) is not empty; if it is, it signifies the user has completed the form and pressed the submit button, which will create a new entry in our Contacts table.

After that, add:

This is the template for our create page; as you can see, we’ve created a form and named each input field appropriately; the name of the input field is how we’ll get the POST variable in our PHP code; for example, if we name an input field “zip code,” we can get the value of that input field in PHP using $_POST['zip code'] (assuming the form’s method is set to post).

Now, if we go to http://localhost/phpcrud/create.php, we can see the results. We’ll see the following if we click the Create button on the read page:

crud operations in php - create.php
CRUD Operations in PHP, PDO & MySQL – create.php

4.5 Creating the Update Page

The update page will be used to edit records in our Contacts database; it is identical to the create page, except that instead of entering a new record, we will be changing existing records. With an Acquire request, we’ll be able to get the record ID.

Add the following to the update.php file:

The above code will look for the contact ID, which will be a URL parameter, such as http://localhost/phpcrud/update.php?id=1. will return the contact with ID 1, and we can then process the request using the GET method and run a MySQL query to get the contact by ID.

After that, add:

This is the update page template; the input values are already defined in the contact fields, and the MySQL query we generated earlier will get them.

We should be able to click the update symbol next to a record on the read page (Contacts) and update it, and we should see something like this:

crud operations in php - update.php
CRUD Operations in PHP, PDO & MySQL – update.php

4.6 Creating the Delete Page

The Contacts table’s records will be deleted using the delete page. A user must confirm the deletion of a record before it can be deleted. This prevents inadvertent deletion.

Add the following to the delete.php file:

To delete a record, the code will check whether the GET request variable “id” exists, if it does, then check if the record exists in the Contacts database and ask the user if they want to delete the contact or not (Yes or No).

After that, add:

The template for the delete page is shown above, which includes the Yes and No buttons (delete confirmation) as well as the output message. The Yes and No buttons will launch a fresh GET request to confirm the user’s selection.

Click the delete button on one of the records on the read page (Contacts), and you should see something like this:

crud operations in php - delete.php
CRUD Operations in PHP, PDO & MySQL – delete.php

Download Source Code Below

Anyway, if you want to level up your programming knowledge, especially PHP, try this new article I’ve made for you Best PHP Projects With Source Code Free Download 2021.

Conclusion

Congratulations! What happens now that you’ve constructed a CRUD operations with PHP and MySQL? Consider modifying the Contacts database and the code to include your own columns.

If you liked this tutorial, please share it using the social media buttons below and visit our website for more instructions.

And always remember that here in ITSourceCode, Source Code is not a problem.

Have fun coding!

Inquiries

If you have any questions or suggestions about  CRUD Operations In PHP, PDO and MySQL please feel free to contact me at our contact page or simply leave a comment below.

By the way if you are interested in learning Java, I have here listed the Best Java tutorial for beginners with an example that can be used in practical ways.

You can try this Responsive Gallery using PHP CSS and Javascript

Leave a Comment