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.
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:
- Name
- Score
- Update button
- 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.
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.
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.
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.
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
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.
I have the same error.
echo ”;
echo ”;
echo ”;
echo ‘Save’;
echo ”;
echo ”;
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.
Sorry, I don’t have experience with those.
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