BASIC CRUD OPERATION IN PHP AND MySQL.

Contents: Click for quick access

Getting Started File Structure Php Files config.php index.php create.php update.php delete.php

Getting Started

In this article, we’re going to explore how to perform basic operations on data using PHP. We’ll build a web application that allows us to add, read, update, and delete information. Specifically, we’ll focus on a simple category section within our web app. For the database, we’ll use MySQL. It’s important to have a basic understanding of MySQL queries before you start.

The code we’ll use is versatile – you can adapt it for different purposes like managing posts, blogs, comments, or contact forms. Before we get started, make sure you have XAMPP (laragon) open. Once XAMPP(Laragon) is running, with both Apache and MySQL started, you’re good to go. Just a heads up, XAMPP(Laragon) needs to be open before we begin any coding. Let’s make sure XAMPP is up and running smoothly!

File Structure

Database Creation:

We start by creating a database.

2. Config.php

We create a file named Config.php to write the code for connecting to the MySQL database.
This file needs to be included in other PHP files for database connection.

3. index.php

We create a file named index.php to display all the data.
In this file, we plan to include links for adding, updating, and deleting data.
The primary focus here is to accomplish the reading functionality of CRUD operations.

4. create.php

We create a file named create.php to add new categories to the database.
This addition of categories can be viewed from the index.php file.

5. update.php

We create a file named update.php to modify existing contents in the database.

6. delete.php

We create a file named delete.php specifically for deleting content from the database.
This file is dedicated to the deletion functionality of CRUD operations.

In summary, after setting up the database, we create five different PHP files, each serving a distinct purpose in implementing CRUD operations: connecting to the database, displaying data, adding new categories, updating existing content, and deleting content.

Php Files

config.php

Before anything else, let’s start by creating the config.php file. This is where we write the essential code to establish a connection between the database and our PHP file. Connecting PHP to MySQL can be done in two ways: through PDO (PHP Data Objects) or MYSQLi connect. For our purposes, we’ll use MYSQLi connect, a reliable method for handling database connections in PHP.

So, kindly insert the provided code into the config.php file.

We use the mysqli_connect method to link the PHP file with the MySQL database. This method needs three details:

The database hostname, which can be either ‘localhost’ or an external server.
The database username, typically set as ‘root’ by default.
The database password, which, in our case, is empty.

Importantly, there’s no need to specify the table name in the config.php file.

<?php

/**
 *using mysqli_connect to connect php file to mysql database and takes four parameter
 * 1. the database host name: either it is localhost or remote host
 * 2. database username. The default database username is root 
 * 3. database password. The default database password is empty
 */

$dbHost = 'localhost';
$dbUname = 'root';
$dbPassword = '';

$mysqli = mysqli_connect($dbHost, $dbUname, $dbPassword); 

// Check connection
if (!$mysqli) {
    die("Connection failed: " . mysqli_connect_error());
}
else{
echo "Connected Successfully");
}


// Create the database
$sqlCreateDatabase = "CREATE DATABASE IF NOT EXISTS crud";
if (mysqli_query($mysqli, $sqlCreateDatabase)) {
    echo "Database 'crud' created successfully\n";
} else {
    echo "Error creating database: " . mysqli_error($mysqli);
}

// Select the 'crud' database
mysqli_select_db($mysqli, "crud");

// Create the 'category' table
$sqlCreateTable = "
CREATE TABLE IF NOT EXISTS category (
    category_id int(11) NOT NULL auto_increment,
    category_name varchar(100) NOT NULL,
    status varchar(100) NOT NULL,
    description varchar(100) NOT NULL,
    PRIMARY KEY (category_id)
)";
if (mysqli_query($mysqli, $sqlCreateTable)) {
    echo "Table 'category' created successfully\n";
} else {
    echo "Error creating table: " . mysqli_error($mysqli);
}

?>

The mysqli_connect function is used to establish a connection to the MySQL server, without specifying a particular database because we will create database later with the help of php code.

index.php

Before you make an index.php file, we have to make an HTML file that contains form as shown in code below:

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Document</title>
</head>
<body>
    <table>
        <tr>
            <td>Category id</td>
            <td>Category name</td>
            <td>Status</td>
            <td>Description</td>
            <td>Update</td>
        </tr>
        <tr>
            <td>category_id from database</td>
            <td>category_name from database</td>
            <td>status from database</td>   
            <td>description from database</td>  
            <td><a href="#">Edit</a> |
             <a href="#" onClick= "return confirm('Are you sure you want to delete?')">Delete</a></td>
        </table>
</body>
</html>

Now, in order to connect to the server, we must include the config.php file that we previously created and execute the sql query for data retrieval using the mysqli query() method as seen in the code below. Instead of include once, you might insert database connection code here. However, if connection code is written into each file, any necessary changes require changing every file.

<?php
//fetching data from database in descending order 
$result = mysqli_query($mysqli, "SELECT * FROM category ORDER BY category_id DESC"); 
?>

Include Database Configuration: We first need to include the database configuration file to access the necessary connection details.
Create Database ‘crud’: Following that, we ensure the existence of the ‘crud’ database. If it doesn’t exist, the code creates it. This step is crucial for subsequent operations.
Select Database ‘crud’: Once the database is created, we proceed to select it. This ensures that any further actions are performed within the context of the ‘crud’ database.
Create Table ‘category’: With the ‘crud’ database selected, the script then checks for the existence of the ‘category’ table. If it doesn’t exist, the code creates it. This table is designed to store information related to different categories.
Fetch Data from ‘category’ Table: Lastly, the code fetches data from the ‘category’ table, ordering it in descending order based on the ‘category_id’ column. This retrieved data can be utilized for various purposes, depending on the application’s requirements.

We have to use mysqli_query() instead of mysql_query() because mysql_query() was deprecated in PHP 5.5.0 verison , and later it was removed in PHP 7.0.

Now we need to write a while loop to retrieve data from the database. The data must then be retrieved using the code shown below. As demonstrated in the code below, this while loop needs to be included after the first tr section of the table.

<?php
//fetching data from database in descending order 
$result = mysqli_query($mysqli, "SELECT * FROM category ORDER BY category_id DESC"); 
?>

<table>
        <tr>
            <td>Category id</td>
            <td>Category name</td>
            <td>Status</td>
            <td>Description</td>
            <td>Update</td>
        </tr>
        <tr>
        <table>
        <tr>
            <td>Category id</td>
            <td>Category name</td>
            <td>Status</td>
            <td>Description</td>
            <td>Update</td>
        </tr>
<?php 
    while($res = mysqli_fetch_array($result)) {         
        echo "<tr>";
        echo "<td>".$res['category_id']."</td>";
        echo "<td>".$res['category_name']."</td>";
        echo "<td>".$res['status']."</td>"; 
        echo "<td>".$res['description']."</td>";
        echo "<td><a href='update.php?category_id=".$res['category_id']."'>Edit</a> 
<a href='#' onclick='return confirm("Are you sure you want to delete")'>delete</a>
</td>";
echo "</tr>";
    }
    ?>
    
    </table>

This code will retrieve all the data inside the category table and we have put the data inside the table to show to the end-user. Since we don’t have any data inside that table. Once we add the data inside that table inside database, data can be seen inside the table.

create.php

Therefore, in order to display value in this table, we must first add value to the MySQL table. To do this, we must create the super global variable $_POST. After an HTML form with method=”post” has been submitted, $_POST is used to gather form data. First we have to gather data from input name and store that value into value. Here, in this example we have used variable named $name, $status, $description to store the data gathered from the input. The data will be given by the user and finally data is inserted into mysql table using mysql query as

INSERT INTO tablename(column name, column name, column name) VALUES('value', 'value','value');

When using a string in a SQL statement, special characters are escaped using the mysqli real escape string function.

<?php
include_once("config.php");

if(isset($_POST['submit'])){
 $name = mysqli_real_escape_string($mysqli, $_POST['name']);
 $status =mysqli_real_escape_string($mysqli,$_POST['status']);
 $description=mysqli_real_escape_string($mysqli,$_POST['description']);
 $result= mysqli_query($mysqli, 
 "INSERT INTO category(category_name, status, description) 
 VALUES('$name', '$status','$description')");
 echo" Data has been added successfully";
}
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Document</title>
</head>
<body>
    <form actions="" method="POST">
        <input type="text" name="name" placeholder="category name">
        <input type="text" name="status" placeholder="status">
        <input type="text" name="description" placeholder="description">
        <input type="submit" name="submit" value="add to database">
</form>

</body>
</html>

Include Database Configuration: The code begins by including the “config.php” file, which contains the database connection details.
Form Submission Check: The script checks if the form has been submitted using isset($_POST['submit']). If the form is submitted, it proceeds to handle the data.
Sanitize and Retrieve Form Data: Form data (category name, status, description) is retrieved using $_POST and sanitized using mysqli_real_escape_string to prevent SQL injection.
Insert Data into Database: The sanitized data is then inserted into the ‘category’ table using an SQL INSERT query.
Display Success Message: If the data insertion is successful, it echoes a success message.

HTML Form: The HTML form consists of input fields for category name, status, and description. The form is submitted to the same page (action="") using the POST method. Upon submission, the PHP script processes the data as explained above.

update.php

Now, let’s consider the edit or update option. We can use a similar code structure as the one used for adding, with only a few adjustments. The primary change lies in the MySQL query for the update operation. In this case, we need to include the category_id in the URL. For example, if the URL is localhost/update.php, we should write localhost/update.php/category_id to specify the particular category we want to update.

<?php
include_once("config.php");

if(isset($_POST['update'])){
 $category_id = mysqli_real_escape_string($mysqli, $_POST['category_id']);
                
 $name = mysqli_real_escape_string($mysqli, $_POST['name']);
 $status =mysqli_real_escape_string($mysqli,$_POST['status']);
 $description=mysqli_real_escape_string($mysqli,$_POST['description']);


 $result= mysqli_query($mysqli, 
 "UPDATE category SET category_name='$name', status='$status', description='$description' WHERE category_id=$category_id");

 echo" Data has been added successfully";
}
?>

<?php
$id=$_GET['category_id'];

$result= mysqli_query($mysqli, "SELECT * FROM category where category_id=$id");

while($r=mysqli_fetch_array($result)){
    $category_name= $r['category_name'];
    $status = $r['status'];
    $description = $r['description'];

} 

?>
<!DOCTYPE html>
<html lang="en">
<head>
    <meta charset="UTF-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Document</title>
</head>
<body>
    <form actions="update.php" method="POST">
        <input type="text" name="name" placeholder="category name" value="<?php echo $category_name;?>" >
        <input type="text" name="status" placeholder="status" value="<?php echo $status;?>">
        <input type="text" name="description" placeholder="description" value =<?php echo $description;?>"">
        <input type="hidden" name="category_id" value="<?php echo $_GET['category_id'];?>">
        <input type="submit" name="update" value="add to database">
</form>

</body>
</html>

Update Logic: We check if the ‘update’ form is submitted. If so, we sanitize and retrieve the form data. The script then updates the corresponding record in the ‘category’ table based on the provided category_id.
Fetching Data for Editing: We retrieve the category_id from the URL parameter and use it to fetch existing data from the ‘category’ table. This data is then used to pre-fill the form fields for editing.
HTML Form: The HTML form includes input fields for category name, status, and description. These fields are pre-filled with existing data for the specified category_id. When the form is submitted, the data is updated in the database.

delete.php

Finally, in order to delete the category, we can follow a similar procedure to update.php, but we must use the delete query of MySQL in place of the update query as shown in code below.

<?php 
include("config.php");

$id=$_GET['category_id'];

$result= mysqli_query($mysqli, "DELETE FROM category where category_id=$id");
?>

Include Database Configuration: We begin by including the “config.php” file, which contains the necessary database connection details.
Retrieve category_id: We retrieve the category_id from the URL parameter using $_GET['category_id']. This parameter is provided in the URL when the script is triggered, indicating the specific record we want to delete.
Execute DELETE Query: With the retrieved category_id, we execute a DELETE query on the ‘category’ table. This query removes the record with the specified category_id from the table.

In summary, when this script is triggered, we delete a record from the ‘category’ table based on the category_id provided in the URL.

Thank you for reading this article. Hope you liked this. If you face any confusion, feel free to ask question in our Codynn’s discord server: https://discord.com/invite/V3RmuWa4dS

If you want to read a bit complex project done by using mysql. You can read at:

User Authentication page in PHP using PDO(PHP DATA OBJECT)

Registration Page using PDO