PHP CRUD with MySQL

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.

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

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.

↑ Back to Top

• PHP
  • Learn PHP
  • PHP Introduction
  • PHP Basics
  • PHP Shopping Cart
  • Payment Gateway
  • Contact Form
  • PHP Login Registration
  • Comments System
  • Types, Variables & Operators
  • PHP Strings
  • PHP Arrays
  • PHP Functions
  • PHP OOPS
  • Event Management System
  • PHP Mail
  • PHP Forms
  • Advanced
  • PHP AJAX
  • RESTful API
  • PHP Databases
    • PHP Responsive Image Gallery using CSS Media Queries
    • PHP MyAdmin
    • Prevent SQL Injection in PHP
    • How to Import SQL into a Database using PHP
    • How to Backup MySQL Database using PHP
    • Restore MySQL Database using PHP
    • Import Excel File into MySQL Database using PHP
    • PHP Database Backup Client for MySQL
    • MySQL Fetch using PHP
    • Access MySQL from PHP
    • PHP CRUD with MySQL
    • CRUD with MySQLi Prepared Statement using PHP
    • PHP PDO CRUD
    • Object-Oriented CRUD System using PHP PDO and MySQL
    • Amazon like Product Category Tree
    • PHP Input Filtering
    • Filtering MySQL Results by Applying PHP Conditions
    • Multi-Select Dropdown Filter in PHP with Database Search
    • Sorting MySQL Column using PHP
    • DataTables Server-side Processing using PHP with MySQL
    • Column Search in DataTables using Server-side Processing
    • Loading List box Dynamically
    • Update/Delete Multiple Rows using PHP
    • PHP Pagination
    • Ajax Pagination with Tabular Records using PHP and jQuery
    • MySQL BLOB using PHP
    • PHP Search and Pagination using PDO
    • Bootstrap Pagination Example in PHP
  • PHP Sessions and Cookies
  • Error and Exception Handling
  • File Upload
  • File Import Export
  • Files and Directories
  • PHP Date Time
  • PHP XML
  • PHP CSV
  • PHP JSON
  • PHP Code Samples
  • Library
  • PHP Freelancer
• jQuery
• JavaScript
• WordPress
• MySQL
• CSS

• Share
• Tweet
• Share

1. Simple PHP Shopping Cart
2. Stripe Payment Gateway Integration using PHP
3. User Registration in PHP with Login: Form with MySQL and Code Download
4. PHP Contact Form
5. How to Create Dynamic Stacked Bar, Doughnut and Pie charts in PHP with Chart.js