Basic PHP and MYSQL CRUD

Basic PHP & MySQL CRUD

Suman Khatri's photo
Suman Khatri
·

8 min read

1. Introduction

What is CRUD?

CRUD stands for:

  • Create – Insert new records

  • Read – Fetch and display records

  • Update – Modify existing records

  • Delete – Remove records

Tech Stack

  • PHP

  • MySQL (Database)

  • Bootstrap (For styling)

2. Setting Up the Database

Create a MySQL Database & Table

Run this SQL query in phpMyAdmin or MySQL CLI:

CREATE DATABASE student_db;
USE student_db;

CREATE TABLE student (
    id INT AUTO_INCREMENT PRIMARY KEY,
    name VARCHAR(100),
    email VARCHAR(100),
    phone VARCHAR(20)
);

3. Connect PHP to MySQL

config.php

Create a config.php file to connect PHP with MySQL:

<?php

$host = "localhost";
$username = "root";
$password = "";
$db = "student_db";

$conn = mysqli_connect($host,$username,$password,$db);

if(!$conn){
    die("Connection Error".mysqli_connect_error);
}
?>

Explanation of the above code:

  1. Database Credentials

    • $host = "localhost"; → The database server is running locally.

    • $username = "root"; → The default MySQL username.

    • $password = ""; → No password set for local MySQL (change for production).

    • $db = "student_db"; → The name of the database to connect to.

  2. Connecting to MySQL

    • mysqli_connect($host, $username, $password, $db); establishes a connection to MySQL using the provided credentials.

  3. Error Handling

    • if(!$conn) { die("Connection Error".mysqli_connect_error); }

    • If the connection fails, the script stops and displays an error message.

4. Create (Insert Data)

create.php

This page will allow users to add new students.

<?php

include 'config.php';

$email = $_POST['email'];
$name = $_POST['name'];
$phone = $_POST['phone'];


// echo "$email";
// echo "<br>";
// echo "$name";
// echo "<br>";
// echo "$phone";

$sql = "INSERT INTO student(email,name,phone) values ('$email','$name','$phone')";

$result = mysqli_query($conn,$sql);

if($result){
    // echo "Added successfuly.";
    echo "<script>alert('data added successfully')
          window.location.href='index.php';
          </script>

    ";

    // header("location:index.php");
}
else{
    echo "Something went wrong.";
}

?>

Explanation of create.php

  1. Includes Database Connection

    • include 'config.php'; connects to the database.

  2. Retrieves Form Data

    • $email = $_POST['email'];

    • $name = $_POST['name'];

    • $phone = $_POST['phone'];

    • Gets user input from the submitted form.

  3. Inserts Data into Database

    • INSERT INTO student(email, name, phone) VALUES ('$email', '$name', '$phone');

    • Stores the submitted data into the student table.

  4. Checks if Data is Inserted Successfully

    • If successful, shows an alert and redirects to index.php.

    • If failed, displays an error message.

Purpose

This script handles form submissions and saves user details in the database.

index.php

<!doctype html>
<html lang="en">
  <head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <title>Bootstrap demo</title>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-QWTKZyjpPEjISv5WaRU9OFeRpok6YctnYmDr5pNlyT2bRjXh0JMhjY6hW+ALEwIH" crossorigin="anonymous">
  </head>
  <body>

  <!-- form  -->
 <div style = "width:30%; margin:auto;" class="mt-5">
 <form action = "create.php" method = "post">
  <div class="mb-3">
    <label for="exampleInputEmail1" class="form-label">Email address</label>
    <input type="email" name="email" class="form-control" id="exampleInputEmail1" aria-describedby="emailHelp">
  </div>

  <div class="mb-3">
    <label class="form-label">Name</label>
    <input  name="name" class="form-control">
  </div>

  <div class="mb-3">
    <label class="form-label">Phone</label>
    <input  name="phone" class="form-control">
  </div>

  <div class="mb-3 form-check">
    <input type="checkbox" class="form-check-input" id="exampleCheck1">
    <label class="form-check-label" for="exampleCheck1">Check me out</label>
  </div>
  <button type="submit" class="btn btn-primary">Submit</button>
</form>
 </div>

    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/js/bootstrap.bundle.min.js" integrity="sha384-YvpcrYf0tY3lHB60NNkmXc5s9fDVZLESaAA55NDzOxhy9GkcIdslK1eN7N6jIeHz" crossorigin="anonymous"></script>
  </body>
</html>

Explanation of above script:

  1. HTML Structure

    • Uses <!doctype html> for an HTML5 document.

    • Includes <head> for metadata, title, and Bootstrap CDN for styling.

  2. Form Layout

    • Wrapped in a div with width:30% and centered using margin:auto; and mt-5 for spacing.

    • Uses Bootstrap classes for a modern and responsive design.

  3. Form Fields

    • Email Field: Uses type="email" for validation.

    • Name Field: Simple text input for user names.

    • Phone Field: Plain input for entering phone numbers.

    • Checkbox: Optional selection for additional agreement.

  4. Form Submission

    • The form submits data to create.php using method="post", ensuring secure data transfer.

    • A Submit Button styled with Bootstrap (btn btn-primary) sends the form data.

  5. Bootstrap Integration

    • Uses Bootstrap components for styling, including form controls and buttons.

    • Bootstrap JavaScript included at the bottom for interactive elements.

5. Read (Display Data)

read.php

Fetch and display student records in a table.

<?php
include 'config.php';

$id = $_GET['id'];

$sql = "SELECT * FROM student where id = $id ";

$result =  mysqli_query($conn,$sql);

$row = mysqli_fetch_assoc($result);
// print_r($row);

?>

<!doctype html>
<html lang="en">
  <head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <title>Bootstrap demo</title>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-QWTKZyjpPEjISv5WaRU9OFeRpok6YctnYmDr5pNlyT2bRjXh0JMhjY6hW+ALEwIH" crossorigin="anonymous">
  </head>
  <body>



 <div class = "d-flex justify-content-center align-items-center flex-column mt-5">
 <ul class="list-group">
  <li class="list-group-item">Detail of student  <?php  echo $row['name'];    ?></li>
  <li class="list-group-item"><?php  echo $row['id'];    ?></li>
  <li class="list-group-item"><?php  echo $row['name'];    ?></li>
  <li class="list-group-item"><?php  echo $row['phone'];    ?></li>
  <li class="list-group-item"><?php  echo $row['email'];    ?></li>

</ul>

<div>
<a href="index.php" class="text-white btn btn-success btn-sm mt-2" >Return Home</a>
<!-- <a href="index.php" class="text-white btn btn-success btn-sm mt-2" >Return Home</a> -->
</div>

 </div>

    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/js/bootstrap.bundle.min.js" integrity="sha384-YvpcrYf0tY3lHB60NNkmXc5s9fDVZLESaAA55NDzOxhy9GkcIdslK1eN7N6jIeHz" crossorigin="anonymous"></script>
  </body>
</html>

Explanation of the read.php File

  1. Include Database Connection

    • include 'config.php'; connects to the database.

  2. Fetch Student Data

    • $_GET['id'] gets the student ID from the URL.

    • SELECT * FROM student WHERE id = $id; retrieves student details from the database.

    • mysqli_fetch_assoc($result); stores the student’s details in $row.

  3. Display Student Details

    • Uses a Bootstrap list-group to show student ID, name, phone, and email.

  4. Return Home Button

    • Provides a button to navigate back to index.php.

  5. Bootstrap for Styling

    • Ensures a responsive and visually appealing design.

Purpose

This page retrieves and displays a specific student's details based on their ID, fetched from the database.

6. Update (Edit Data)

update.php

This page fetches student details and allows editing.

<?php

include 'config.php';

$id = $_GET['id'];

$sql = "SELECT * FROM student WHERE id = $id;";
$output = mysqli_query($conn,$sql);
$data = mysqli_fetch_assoc($output);
// print_r($data);
// echo $data['email'];

//post

if($_SERVER['REQUEST_METHOD']=='POST'){ 
$email = $_POST['email'];
$name = $_POST['name'];
$phone = $_POST['phone'];

$query = "UPDATE student SET email = '$email', name = '$name', phone = '$phone' WHERE id = $id;";

$result = mysqli_query($conn,$query);

if($result){
    echo "<script> 
    alert('updated successfully');
    window.location.href = 'app.php';
    </script>";
}

}

?>

<!doctype html>
<html lang="en">
  <head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <title>Bootstrap demo</title>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-QWTKZyjpPEjISv5WaRU9OFeRpok6YctnYmDr5pNlyT2bRjXh0JMhjY6hW+ALEwIH" crossorigin="anonymous">
  </head>
  <body>

  <!-- form  -->
 <div style = "width:30%; margin:auto;" class="mt-5">
 <form action = "" method = "post">
  <div class="mb-3">
    <label for="exampleInputEmail1" class="form-label">Email address</label>
    <input type="email" name="email" value = "<?php  echo $data['email']; ?>" class="form-control" id="exampleInputEmail1" aria-describedby="emailHelp">
  </div>

  <div class="mb-3">
    <label class="form-label">Name</label>
    <input  name="name" value = "<?php  echo $data['name']; ?> " class="form-control">
  </div>

  <div class="mb-3">
    <label class="form-label">Phone</label>
    <input  name="phone" value = "<?php  echo $data['phone']; ?> " class="form-control">
  </div>

  <div class="mb-3 form-check">
    <input type="checkbox" class="form-check-input" id="exampleCheck1">
    <label class="form-check-label" for="exampleCheck1">Check me out</label>
  </div>
  <button type="submit" class="btn btn-primary">Submit</button>
</form>
 </div>
    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/js/bootstrap.bundle.min.js" integrity="sha384-YvpcrYf0tY3lHB60NNkmXc5s9fDVZLESaAA55NDzOxhy9GkcIdslK1eN7N6jIeHz" crossorigin="anonymous"></script>
  </body>
</html>

Here’s a brief explanation of the above code:

  1. Fetching Data:

    • The script gets the student ID from the URL ($_GET['id']).

    • It queries the database for the student’s details using the ID and stores the result in $data.

  2. Form Handling:

    • When the form is submitted via the POST method, it updates the student’s details (email, name, phone) in the database using the provided values.

  3. Update Query:

    • The SQL query updates the student’s record in the database with the new details ($email, $name, $phone).

  4. Success Message:

    • If the update is successful, a JavaScript alert is triggered, and the user is redirected to 'app.php'.

  5. Displaying Data:

    • The existing student details are displayed in the form as default values, allowing the user to edit them.

  6. Bootstrap:

    • The form is styled using Bootstrap for a clean, responsive design. The form includes fields for email, name, phone, and a checkbox.

In short, the code allows editing and updating a student's details in the database using a form interface.

7. Delete (Remove Data)

delete.php

This script will delete a student record.

<?php

include 'config.php';


$id = $_GET['id'];

$sql = "DELETE FROM student where id = $id";

$result = mysqli_query($conn,$sql);

if($result){
 echo "<script>
 alert('Deleted Successfully.');
 window.location.href='app.php';

 </script>";
  }

?>

This PHP code performs the following actions:

  1. Connection: It includes the config.php file to establish a connection to the database.

  2. Get the ID: The id of the student to be deleted is retrieved from the URL using $_GET['id'].

  3. SQL Query: It constructs a SQL DELETE query to remove the student record from the student table where the ID matches the one obtained from the URL.

  4. Execution: The query is executed using mysqli_query(), and the result is stored in the $result variable.

  5. Success Check: If the deletion is successful (i.e., $result is truthy), a JavaScript alert displays "Deleted Successfully," and the page is redirected to 'app.php'.

In short, this script deletes a student record from the database based on the id provided in the URL and then alerts the user upon successful deletion.

8 . Display

app.php

This script will display a all student record.

<?php
include 'config.php';

$sql = "SELECT * FROM student";

$result =  mysqli_query($conn,$sql);

$row = mysqli_fetch_all($result, MYSQLI_ASSOC);
// print_r($row);

// echo $row['name'];

?>

<!doctype html>
<html lang="en">
  <head>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <title>Bootstrap demo</title>
    <link href="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/css/bootstrap.min.css" rel="stylesheet" integrity="sha384-QWTKZyjpPEjISv5WaRU9OFeRpok6YctnYmDr5pNlyT2bRjXh0JMhjY6hW+ALEwIH" crossorigin="anonymous">
  </head>
  <body>

  <!-- form  -->
 <div style = "width:50%; margin:auto;" class = "mt-5">
 <table class="table">
  <thead>
    <tr>
      <th scope="col">id</th>
      <th scope="col">email</th>
      <th scope="col">name</th>
      <th scope="col">phone</th>
      <th scope="col">Action</th>
    </tr>
  </thead>
  <tbody>
<?php
  foreach($row as $student){
    echo "<tr>";
    echo "<td>".$student['id']."</td>";
    echo "<td>".$student['name']."</td>";
    echo "<td>".$student['email']."</td>";
    echo "<td>".$student['phone']."</td>";
    echo "<td>";
    echo  '<a href = "read.php?id='.$student['id'].'" class="btn btn-info btn-sm m-1">view</a>';
    echo  '<a href = "update.php?id='.$student['id'].'"  class = "btn btn-warning btn-sm ">Edit</a>';
    echo  '<a href="delete.php?id='.$student['id'].'" class="btn btn-danger btn-sm m-1" >Delete </a>';
    echo "</tr>";

  }
?>

  </tbody>
</table>
 </div>

 <footer class="d-flex justify-content-center py-5 bg-dark text-white">
  <p>&copy; 2025. Designed with &hearts; by suman khatri</p>
</footer>

    <script src="https://cdn.jsdelivr.net/npm/bootstrap@5.3.3/dist/js/bootstrap.bundle.min.js" integrity="sha384-YvpcrYf0tY3lHB60NNkmXc5s9fDVZLESaAA55NDzOxhy9GkcIdslK1eN7N6jIeHz" crossorigin="anonymous"></script>
  </body>
</html>

Explanation of app.php

  1. Database Connection

    • Includes config.php to establish a connection to the MySQL database.

  2. Fetching Data

    • Executes SELECT * FROM student to retrieve all student records.

    • Uses mysqli_fetch_all($result, MYSQLI_ASSOC) to store the result in an associative array.

  3. Displaying Data in a Table

    • Uses Bootstrap to create a responsive table.

    • Iterates through student records using foreach and displays them in table rows.

  4. Action Buttons

    • Each student row includes:

      • View (read.php?id=) – Displays student details.

      • Edit (update.php?id=) – Allows updating student information.

      • Delete (delete.php?id=) – Removes the student from the database.

  5. Footer

    • Adds a footer with a copyright notice.

  6. Bootstrap Integration

    • Uses Bootstrap for styling and responsiveness.

Conclusion

With this PHP CRUD application, you’ve learned how to:

  • Connect to a database.

  • Perform basic CRUD operations.

  • Use a structured PHP file system.

Feel free to modify and enhance this application for your specific needs!

PHPsimplecrudBootstrap 5BootstrapMySQLSQL