Post
Ask Question

CRUD Operation in PHP using MySQL

Introduction

The CURD Operations are typically performed on databases; hence, in this PHP CRUD Operations tutorial, you will implement CRUD techniques on MYSQL databases with the help of PHP.

The CRUD acronym comprises all the major operations that are performed on a relations database. It stands for: 

  1. C (Create): You will insert records into MySQL table Using PHP and MySQLi Procedural.   
  2. R (Read): You will fetch records from the MySQL table and display them in HTML Table Using PHP and MySQLi Procedural.
  3. U (Update): You will update records in the MySQL table Using PHP and MySQLi Procedural.   
  4. D (Delete): You will delete records from the MySQL table Using PHP and MySQLi Procedural.

You will now understand the different operations in detail.

Create databse and tables in MySQL using the XAMPP server

Step 1. Launch the XAMPP control panel and start the Apache and MySQL services.

Open phpMyAdmin:

CRUD Operation in PHP using MySQL

Note. For more information, please see the following article: https://www.c-sharpcorner.com/article/how-to-run-a-php-code-using-xampp-server/

Step 2. Open your web browser and navigate to http://localhost/phpmyadmin. This will open the phpMyAdmin interface.

CRUD Operation in PHP using MySQL

Step 3. In the phpMyAdmin interface, click on the "Databases" tab. Enter a name for your database (e.g., "student") in the "Create database" field.

Click the "Create" button to create the database.

CRUD Operation in PHP using MySQL

Step 4. After creating the database, click on its name in the left sidebar to select it. Click on the "SQL" tab to run SQL queries. Create a "students" Table: In the SQL tab, enter the following SQL query to create the "students" table:

CREATE TABLE students (
    id INT PRIMARY KEY AUTO_INCREMENT,
    name VARCHAR(50) NOT NULL,
    age INT,
    email VARCHAR(50),
    created_at TIMESTAMP DEFAULT CURRENT_TIMESTAMP
);

Click the "Go" button to execute the query and create the table.

That's it! You have created a student database with a "students" table. You can now perform CRUD operations on these tables using PHP and MySQL.

CRUD operations on these tables using PHP and MySQL

You can create a  project folder like the following structure.

CRUD Operation in PHP using MySQL

How to Create a MySQL Database Connection?

The following code acts as the connection between the webpage and the database where the data from the webpage will be stored.

The name of the file is dbconfig.php.

<?php
$servername = "localhost";
$username = "root"; 
$password = ""; 
$dbname = "student"; 
$conn = new mysqli($servername, $username, $password, $dbname);
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}
?>

How to Create Records?

The name of the file is create-student.php.

<!DOCTYPE html>
<html>
<title>Student Database</title>
<body>
<h2>Student Form</h2>
<form action="" method="POST">
  <fieldset>
    <legend>Student information:</legend>
    Name:<br>
    <input type="text" name="name"> <br>
    Age:<br>
    <input type="text" name="age"> <br>
    Email:<br>
    <input type="email" name="email"><br>
    <br><br>
    <input type="submit" name="submit" value="submit">
  </fieldset>
</form>
</body>
</html>

<?php 
include "dbconfig.php";
  if (isset($_POST['submit'])) {
    $name = $_POST['name'];
    $age = $_POST['age'];
    $email = $_POST['email'];
    $sql = "INSERT INTO `students`(`name`, `age`, `email`) VALUES ('$name','$age','$email')";
    $result = $conn->query($sql);
    if ($result == TRUE) {
      echo "New record created successfully.";
      header('Location: view-student.php');
    }else{
      echo "Error:". $sql . "<br>". $conn->error;
    } 
    $conn->close(); 
  }
?>

CRUD Operation in PHP using MySQL

How to View Records?

The name of the file is view-student.php.

<?php 
include "dbconfig.php";
?>
<!DOCTYPE html>
<html>
<head>
    <title>Student Database</title>
<link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.0/css/bootstrap.min.css">
</head>
<body>

    <div class="container">
        <h2>Student Details</h2>
<table class="table">
    <thead>
        <tr>
        <th>ID</th>
        <th>Name</th>
        <th>Age</th>
        <th>Email</th>
        <th>Action</th>

    </tr>
    </thead>
    <tbody> 
        <?php
                $sql = "SELECT * FROM students";
                $result = $conn->query($sql);
                if ($result->num_rows > 0) {
                while ($row = $result->fetch_assoc()) {
        ?>
                    <tr>
                    <td><?php echo $row['id']; ?></td>
                    <td><?php echo $row['name']; ?></td>
                    <td><?php echo $row['age']; ?></td>
                    <td><?php echo $row['email']; ?></td>
                    <td><a class="btn btn-info" href="update-student.php?id=<?php echo $row['id']; ?>">Edit</a>
                     &nbsp; 
                     <a class="btn btn-danger" href="delete-student.php?id=<?php echo $row['id']; ?>">Delete</a> 
                    </td>
                    </tr>                       
        <?php       }
            }
        ?>                
    </tbody>
</table>
    </div> 
</body>
</html>

How to Update Records?

The name of the file is update-student.php.

<?php 
include "dbconfig.php";
    if (isset($_POST['update'])) {
        $stu_id = $_POST['stu_id'];
        $name = $_POST['name'];
        $age = $_POST['age'];
        $email = $_POST['email'];
        $sql = "UPDATE `students` SET `name`='$name',`age`='$age',`email`='$email' WHERE `id`='$stu_id'"; 
        $result = $conn->query($sql); 
        if ($result == TRUE) {
            echo "Record updated successfully.";
            header('Location: view-student.php');
        }else{
            echo "Error:" . $sql . "<br>" . $conn->error;
        }
      
    } 

if (isset($_GET['id'])) {
    $stu_id = $_GET['id']; 
    $sql = "SELECT * FROM students WHERE id='$stu_id'";
    $result = $conn->query($sql); 
    if ($result->num_rows > 0) {        
        while ($row = $result->fetch_assoc()) {
            $id = $row['id'];
            $name = $row['name'];
            $age = $row['age'];
            $email = $row['email'];
        } 
    ?>

        <h2>Student details Update Form</h2>
        <form action="" method="post">
          <fieldset>
            <legend>Personal information:</legend>
            Name:<br>
            <input type="text" name="name" value="<?php echo $name; ?>">
            <input type="hidden" name="stu_id" value="<?php echo $id; ?>">
            <br>
            Age:<br>
            <input type="text" name="age" value="<?php echo $age; ?>">
            <br>
            Email:<br>
            <input type="email" name="email" value="<?php echo $email; ?>">
            <br><br>
            <input type="submit" value="Update" name="update">
          </fieldset>
        </form> 
        </body>
        </html> 


    <?php
    } else{ 
        header('Location: view-student.php');
    } 
}
?>

CRUD Operation in PHP using MySQL

How to Delete Records?

The name of the file is delete-student.php.

​<?php 
include "dbconfig.php"; 
if (isset($_GET['id'])) {
    $stu_id = $_GET['id'];
    $sql = "DELETE FROM students WHERE id ='$stu_id'";
     $result = $conn->query($sql);
     if ($result == TRUE) {
        echo "Record deleted successfully.";
        header('Location: view-student.php');
    }else{
        echo "Error:" . $sql . "<br>" . $conn->error;
    }
} 
?>

Conclusion

This concludes the tutorial on "PHP CRUD Operations." You have learned how to use PHP to conduct CRUD activities on a database by creating, reading, updating, and removing records using various web pages. Finally, you developed a dbconfig.php file to connect the web pages to the database so that the actions could be performed.


Similar Articles