CRUD Operations with PHP and MySQL (full tutorial for beginners)

CRUD tutorial with PHP and MySQL

Hey there! Some links on this page may be affiliate links which means that, if you choose to make a purchase, I may earn a small commission at no extra cost to you. I greatly appreciate your support!

In this tutorial, you will learn how to do basic CRUD operations with PHP and a MySQL database. If you are not familiar, CRUD is a database acronym that stands for create, read, update, and delete.

By the end of this tutorial, you will have a working web page in which you can add, display, update, and delete records. Please understand that this is just a demonstration of how CRUD works and should not be used in a live website since we have not sanitized the user input.

Note: I’ll assume that you have root access to an Ubuntu or Debian server with a web server, PHP, and MySQL installed. I’m using Ubuntu 20.04 and Nginx in this tutorial.

1. Create a Database and Table

This tutorial will execute MySQL commands on the command line; however, feel free to use a tool like phpMyAdmin to create your database and table.

Execute the following MySQL commands to create your database and table. Please use values that meet your requirements.

create database php_demo;
use php_demo;
create table demo_table(id int not null auto_increment primary key, name varchar(50) not null, score int);

These three MySQL commands simply create a database called php_demo and a table called demo_table.

Next, you’ll want to create a PHP file with your database credentials. In the directory above your website root, create a file called db.php with your database credentials like this.

<?php
  $conn = new mysqli("localhost", "tony", "thisismypassword", "php_demo");
?>

My website root is at /var/www/html/, so this file lives at /var/www/db.php. The reason for this is a security measure so that this file is not accessible by the public.

We will use this file later in the tutorial.

2. Design the Layout

Create an index.php file in the root of your web directory.

<!DOCTYPE html>
<html>
<head>
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
</head>
<body>

<div class="container">

  <h1>PHP + MySQL CRUD Demo</h1>
  <p>Create, read, update, and delete records below</p>

  <form class="form-inline m-2" action="create.php" method="POST">
    <label for="name">Name:</label>
    <input type="text" class="form-control m-2" id="name" name="name">
    <label for="score">Score:</label>
    <input type="number" class="form-control m-2" id="score" name="score">
    <button type="submit" class="btn btn-primary">Add</button>
  </form>

</div>
</body>
</html>

This HTML layout includes the Boostrap stylesheet in the header and defines a form element in the body. The form contains two fields and a button. The two form fields are to collect a name and a score.Empty page layout for CRUD example

The page doesn’t do much of anything right now. Let’s fix that.

Notice how the form action references create.php. Next, we’ll add some PHP code to handle when a user clicks on the Add button.

3. Handle Add Button Clicks (CREATE)

Assuming your website root directory is /var/www/html/, create the file /var/www/html/create.php with the following PHP code.

<?php
  include '../db.php';
  $name = $_POST["name"];
  $score = $_POST["score"];
  $sql = "insert into demo_table (name, score) values ('$name', '$score')";
  $conn->query($sql);
  $conn->close();
  header("location: index.php");
?>

This block of PHP code inserts the values that originated from the form fields into the database table called demo_table. Notice how we are including our database credentials file on line 2.

At this point, when you click on the Add button, a record will be created in the database; however, there will be no indication of this from the user’s perspective. That’s what we’ll work on in the next section.

4. Display Database Values in a Table (READ)

Let’s first create a table to display the values that we add to the database. Edit index.php with the following highlighted changes.

<!DOCTYPE html>
<html>
<head>
  <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/4.5.2/css/bootstrap.min.css">
</head>
<body>

<div class="container">

  <h1>PHP + MySQL CRUD Demo</h1>
  <p>Create, read, update, and delete records below</p>

  <table class="table">
    <tbody>
      <?php include 'read.php'; ?>
    </tbody>
  </table>

  <form class="form-inline m-2" action="create.php" method="POST">
    <label for="name">Name:</label>
    <input type="text" class="form-control m-2" id="name" name="name">
    <label for="score">Score:</label>
    <input type="number" class="form-control m-2" id="score" name="score">
    <button type="submit" class="btn btn-primary">Add</button>
  </form>

</div>

</body>
</html>

Notice how we define a table element, but inside the tbody tag, we include a file called read.php.

Let’s create that file at /var/www/html/read.php with the following PHP code.

<?php
  include '../db.php';
  $sql = "select * from demo_table";
  $result = $conn->query($sql);
  while($row = $result->fetch_assoc()) {
    echo "<tr>";
    echo "<td>" . $row['name'] . "</td>";
    echo "<td>" . $row['score'] . "</td>";
    echo '<td><a class="btn btn-primary" href="index.php?id=' . $row['id'] . '" role="button">Update</a></td>';
    echo '<td><a class="btn btn-danger" href="delete.php?id=' . $row['id'] . '" role="button">Delete</a></td>';
    echo "</tr>";
  }
  $conn->close();
?>

Again, you can see that we are including our database credentials on line 2. We then proceed to query the database for all records in the demo_table.

Notice the series of echo statements. What we are essentially doing for the rest of the file is generating dynamic HTML.

We define the table body outside of the for loop and then for each record in the database, we add a row to the table with four columns:

  1. Name
  2. Score
  3. Update button
  4. Delete button

Later on in the tutorial, we will implement the logic for update and delete, but for now just know that they exist.

Finally, we close the connection to the database on the second to last line.

You can test the read functionality be reloading your web page. When you add new records, the page will update and display those records.

CRUD table with update and delete buttons

5. Edit Existing Records (UPDATE)

Let’s add the ability to update a record in the table. This one is more complicated, so pay attention.

Recall from the previous section that there is an Update button in each row. Right now, the button doesn’t do anything other than load the same page except with a GET parameter for the id of the row tacked on to the URL. For example, if the id of the Tony row is 2, the Update button for Tony’s row will link to http://site2.xyz/?id=2.

This GET parameter serves as distinction so we can generate an editable form for this row.

We can make this distinction by modifying /var/www/html/read.php with the following highlighted changes.

<?php
  include '../db.php';
  $sql = "select * from demo_table";
  $result = $conn->query($sql);
  while($row = $result->fetch_assoc()) {
    echo "<tr>";
    if ($row['id'] == $_GET['id']) {
      echo '<form class="form-inline m-2" action="update.php" method="POST">';
      echo '<td><input type="text" class="form-control" name="name" value="'.$row['name'].'"></td>';
      echo '<td><input type="number" class="form-control" name="score" value="'.$row['score'].'"></td>';
      echo '<td><button type="submit" class="btn btn-primary">Save</button></td>';
      echo '<input type="hidden" name="id" value="'.$row['id'].'">';
      echo '</form>';
    } else {
      echo "<td>" . $row['name'] . "</td>";
      echo "<td>" . $row['score'] . "</td>";
      echo '<td><a class="btn btn-primary" href="index.php?id=' . $row['id'] . '" role="button">Update</a></td>';
    }
    echo '<td><a class="btn btn-danger" href="delete.php?id=' . $row['id'] . '" role="button">Delete</a></td>';
    echo "</tr>";
  }
  $conn->close();
?>

Notice how we check to see if the GET id value matches the current row on line 7. If it does, we display a form that allows the user to edit the current row. Otherwise, we display the table row like normal.

Update a record in an HTML table

There is one one other aspect to implement and that is the actual database update logic when the Save button is clicked. Create a new file at /var/www/html/update.php like this.

<?php
  include '../db.php';
  $id = $_POST['id'];
  $name = $_POST['name'];
  $score = $_POST['score'];
  $sql = "update demo_table set name='$name', score='$score' where id=$id";
  $result = $conn->query($sql);
  $conn->close();
  header("location: index.php");
?>

From the form in the table, the server receives the id, name, and score as POST parameters. With those values, the row matching the id is updated.

The last line in this code ensures we remain on the same page by modifying the header location value.

6. Delete Existing Records (DELETE)

The last aspect of CRUD is delete. Similar to update, we already have a Delete button in each table row, but right now it doesn’t do anything. Also similar to before, the Delete button points to a PHP file on the server. Again, if Tony has an id of 2, the Delete button will go to http://site2.xyz/delete.php?id=1.

Create /var/www/html/delete.php with the following PHP code.

<?php
  include '../db.php';
  $id = $_GET['id'];
  $sql = "delete from demo_table where id=$id";
  $conn->query($sql);
  $conn->close();
  header("location: index.php");
?>

This one is pretty easy. We get the id of the row that was clicked on as a GET parameter and execute a MySQL statement to delete that record.

YouTube video

At this point, you have the knowledge you need to implement your own CRUD operations in PHP and MySQL.

If you have any questions, as always, please let me know in the comments below.

Facebook
Twitter
Pinterest
LinkedIn
Reddit

Meet Tony

Tony from Tony Teaches Tech headshot

With a strong software engineering background, Tony is determined to demystify the web. Discover why Tony quit his job to pursue this mission. You can join the Tony Teaches Tech community here.

11 Responses

  1. Hi Tony

    Thanks for your CRUD Operations Tutorial with PHP and MySQL

    I have a problem if i go to index i get this error
    Undefined index: id in C:\wamp64\www\Development\curd2021\curd1\read.php on line 7

    but as soon as i have an id in like this index.php?id=0 it works i am using php 7.3.5

    Thanks

    Peter

    1. My guess is that your form does not have the hidden input tag on line 12 of read.php which defines id for POST and GET requests.

  2. Hello Tony,
    Please I need help to do my project. How do I use php and mysql to carryout the following operations
    1) Web Data Crawling
    2) Collection, and
    3) Storage
    What are the steps? I am using putty.

  3. Notice: Undefined index: id in C:\wamp64\www\cinema\read.php on line 19
    i have the exact same code as above and followed the video precisely
    also the save button just refreshes the index page without applying the changes. any help?
    the first error is showing in the index page above the entry i made with the add button

  4. Hi Tony I have been trying your tutorial all day but it will not work for me which is unusual because all of your other tutorials I’ve followed I’ve been able to complete easily!

    My problem starts after I make create.php file. When I put in values in fields then click add on website, the header(location: index.php) does not work and I am redirected to /create.php and get a 404 error from nginx.

    When I add the read.php file, my index.php gets a 404 error.

    If you have any advice on how to continue please let me know. I have been trying to do this all day to no avail.

    I even tried this tutorial, https://mariadb.com/resources/blog/developer-quickstart-php-mysqli-and-mariadb/ but I get similar results.

    I really want to move forward on this issue but I keep hitting this road block.

    What can I do to narrow down my problem? I follow the directions exactly!!! That’s what makes it very frustrating.

    The MariaDB tutorial includes an if statement that is supposed to print if the database connection is broken but it doesn’t help because I get a 404!

    It is very confusing because if I use a simpler php block such as below it runs fine with no 404!

    I am using a LEMP server I built following your guide. I also followed your php-fpm guide.

  5. Hi Tony it may be worth mentioning that they mysqli php module has to be installed and enabled.

    This is what was causing my problem and some kind souls in the Libera Chat #php IRC server helped me solve my problem.

    #Check for php-mysqli module.
    php -m | grep mysqli

    #install php mysql extension (Rocky 9)
    dnf install php-mysqlnd

  6. Hi Tony, thanks for the great tutorial!

    I have several databases in my system. The read.php file does not use the database which has my table. How can I explicitly tell it use the database which I want?

    Thanks a lot!

  7. fix for undefined array key warning:

    in read.php

    delete:

    if ($row[‘id’] == $_GET[‘id’]) {

    replace with:

    if (isset($_GET[‘id’]) && $row[‘id’] == $_GET[‘id’]) {

Leave a Reply

Your email address will not be published. Required fields are marked *