PHP CRUD with MySQL

by Vincy. Last modified on July 15th, 2022.

In this tutorial, we are going to see an example program to learn how to do database CRUD operations using PHP and MySQL. CRUD tends to Create, Read, Update and Delete operations with database table records.

In the previous tutorial, we have seen how to access the MySQL database via PHP.

In this example, we are going to create an interface as a database front end to handle these operations. We have users table containing user information like name, password and more. With this table, we have to perform CRUD using MySQL.

Creating New Row in MySQL Database

The code below is to provide the user interface for the database insert. This HTML form contains input fields to enter user data to be inserted into the table.

PHP Create Read Update Delete

Database script

Create a database ‘php-crud’ and run this SQL script to have the database structure created for this example.

The source code download contains an SQL script with the same SQL script. You can also use it to import.

--
-- Database: `php_crud`
--

-- --------------------------------------------------------

--
-- Table structure for table `users`
--

CREATE TABLE `users` (
  `userId` int(8) NOT NULL,
  `signup_name` varchar(55) NOT NULL,
  `userName` varchar(55) NOT NULL,
  `firstName` varchar(255) NOT NULL,
  `lastName` varchar(255) NOT NULL
);

--
-- Indexes for table `users`
--
ALTER TABLE `users`
  ADD PRIMARY KEY (`userId`);

--
-- AUTO_INCREMENT for table `users`
--
ALTER TABLE `users`
  MODIFY `userId` int(8) NOT NULL AUTO_INCREMENT;

Create a user with an HTML add form

A simple add form in HTML collects users’ data to create a new user record in the database.

This form uses the HTML5 required attribute to let the field be mandatorily entered before submitting.

The add_users.php has both HTML form and the backend PHP logic to perform the add action.

On the form submit action, it executes the PHP script written at the beginning of this add_users.php file.

add_user.php

<form name="frmUser" method="post" action="">
	<div class="message"><?php if(isset($message)) { echo $message; } ?></div>
	<p>
		<a href="index.php" class="font-bold">List User</a>
	</p>
	<h1>Add New User</h1>
	<div>
		<div class="row">
			<label for="signup-name">Name <span class="error-color"
				id="signup-name_error"></span>
			</label><input type="text" name="signup-name" id="signup-name"
				required>
		</div>
		<div class="row">
			<label>Username</label><input type="text" name="userName"
				class="txtField" required>
		</div>
		<div class="row">
			<label>First Name</label><input type="text" name="firstName"
				class="txtField">
		</div>
		<div class="row">
			<label>Last Name</label><input type="text" name="lastName"
				class="txtField">
		</div>
		<div class="row">
			<input type="submit" name="submit" value="Add" class="btnSubmit">
		</div>
	</div>
</form>

On submitting this form the following PHP code. It prepares the INSERT query and bundles the user data from the form.

It passes the query and the related parameters to the PHP DataSource class.

The DataSource PHP class is created for preparing SQL statement objects. Then, it executes the database operations to perform the CRUD actions.

add_user.php (PHP code to process form submit)

<?php
if (count($_POST) > 0) {
    require_once __DIR__ . '/lib/DataSource.php';
    $database = new DataSource();
    $sql = "INSERT INTO users (signup_name,userName,firstName, lastName) VALUES (?,?,?,?)";
    $paramType = 'ssss';
    $paramValue = array(
        $_POST["signup-name"],
        $_POST["userName"],
        $_POST["firstName"],
        $_POST["lastName"]
    );
    $database->insert($sql, $paramType, $paramValue);
}
?>

PHP MySQL Read

Once records are inserted into the database, let us learn how to read and render them in the UI.

The following code shows how to fetch all the records from the database and list them on the list page.

index.php (PHP code to read MySQL records)

<?php
require_once __DIR__ . '/lib/DataSource.php';
$database = new DataSource();
$sql = "SELECT * FROM users ORDER BY userId DESC";
$result = $database->select($sql);
?>

index.php (HTML table to render database results)

<form method="post" action="">
	<div id="message"><?php if(isset($message)) { echo $message; } ?></div>
	<p>
		<a href="add_user.php" class="font-bold">Add User</a>
	</p>
	<table class="striped">
		<thead>
			<tr>
				<th>Username</th>
				<th>First Name</th>
				<th>Last Name</th>
				<th>Actions</th>
			</tr>
		</thead>
<?php
if (is_array($result) || is_object($result)) {
    foreach ($result as $key => $value) {
        ?>
         <tr>

			<td><?php echo $result[$key]["userName"];?></td>
			<td><?php echo $result[$key]["firstName"];?></td>
			<td><?php echo $result[$key]["lastName"];?></td>
			<td><a
				href="edit_user.php?userId=<?php echo $result[$key]["userId"]; ?>"
				class="mr-20">Edit</a> <a
				href="delete_user.php?userId=<?php echo $result[$key]["userId"]; ?>">Delete</a></td>
		</tr>
 <?php
    }
}
?>
		</table>
</form>

MySQL Update via PHP

First, we fetch the record by id and populate the values in the edit form. On submitting edited user information we form an update query to edit the record with the reference of its id. The code is,

edit_user.php (to read and pre-populate the edit form)

<?PHP 
require_once __DIR__ . '/lib/DataSource.php';
$sql = "select * from users where userId=? ";
$paramType = 'i';
$paramValue = array(
    $_GET["userId"]
);
$result = $database->select($sql, $paramType, $paramValue);
?>
<form name="frmUser" method="post" action="">
    <p>
        <a href="index.php" class="font-bold"> List User</a>
    </p>
    <h1>Edit User</h1>
    <div>
        <div class="row">
            <label for="signup-name">Name <span
                class="error-color" id="signup-name_error"></span>
            </label><input type="text" name="signup-name"
                id="signup-name"
                value="<?php echo $result[0]['signup_name']; ?>">
        </div>
    </div>
    <div class="row">
        <label>Username</label> <input type="hidden"
            name="userId" class="txtField"
            value="<?php echo $result[0]['userId']; ?>"><input
            type="text" name="userName" class="txtField"
            value="<?php echo $result[0]['userName']; ?>">
    </div>
    <div class="row">
        <label>First Name</label> <input type="text"
            name="firstName" class="txtField"
            value="<?php echo $result[0]['firstName']; ?>">
    </div>
    <div class="row">
        <label>Last Name</label> <input type="text"
            name="lastName" class="txtField"
            value="<?php echo $result[0]['lastName']; ?>">
    </div>
    <div class="row">
        <input type="submit" name="submit" value="Save"
            class="btnSubmit">
    </div>
</form>

edit_user.php (to process database update on edit-form submit)

<?php
require_once __DIR__ . '/lib/DataSource.php';
$database = new DataSource();

if (count($_POST) > 0) {
    $sql = "UPDATE users set signup_name=?, userName=? ,firstName=? ,lastName=? WHERE userId=?";
    $paramType = 'ssssi';
    $paramValue = array(
        $_POST["signup-name"],
        $_POST["userName"],
        $_POST["firstName"],
        $_POST["lastName"],
        $_GET["userId"]
    );
    $database->execute($sql, $paramType, $paramValue);
    $message = "Record Modified Successfully";
}
?>

Delete Record from MySQL Table

The following code is used to delete a record from the database by sending the record id in the page URL.

<?php
require_once __DIR__ . '/lib/DataSource.php';
$database = new DataSource();
$sql = "DELETE FROM users WHERE userId =? ";
$paramType = "i";
$paramValue = array(
    $_GET["userId"]
);
$database->delete($sql, $paramType, $paramValue);
header("Location:index.php");
exit();
?>

Download

Vincy
Written by Vincy, a web developer with 15+ years of experience and a Masters degree in Computer Science. She specializes in building modern, lightweight websites using PHP, JavaScript, React, and related technologies. Phppot helps you in mastering web development through over a decade of publishing quality tutorials.

Comments to “PHP CRUD with MySQL”

Leave a Reply

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

↑ Back to Top

Share this page