In our previous blog, we created a simple CRUD application. To introduce some more features, we have created this new application. We have added CSS and JavaScript for better user interface. But the main code base is almost same.

As in before, we will follow following steps to complete our application:

  1. Create Database and Table in MySQL
  2. Retrieve Record
  3. Create Record
  4. Update Record
  5. Delete Record

Create Database and Table

CREATE DATABASE address_book_db;
USE address_book_db;
CREATE TABLE address_table (
    id int NOT NULL PRIMARY KEY AUTO_INCREMENT,
    name varchar(100) NOT NULL,
    address varchar(255),
    phone VARCHAR(50),
    email VARCHAR(50)
);
INSERT INTO address_table (name, address, phone, email) 
    VALUES ("Jack Smith", "123 ABC ST. New York", "123-456-789", "jack123@gmail.com");

Retrieve Record

<!DOCTYPE html>
<html lang="en">
<head>
  <link rel="stylesheet" type="text/css" href="styles.css">
  <script>
    // Get confirmation before deleting
    function confirm_delete(){
      if(confirm("Are you sure you want to delete?") === true){
        return true;
      } else {
        return false;
      }
    }
</script>
  <title>Home</title>
</head>
<body>
   
<?php
// Include the config file
include('config.php');
echo "<h1>CRUD application with PHP and MySQL</h1>";
// Add a link for new record addition
echo "<div align=center><a href=create.php><button type=button>Add A New Address</button></a></div>";
// Select fields
$sql = "SELECT * FROM address_table";
// Execute query
$result = mysqli_query($conn, $sql);
// Show Fields
if (mysqli_num_rows($result) > 0) {
?>
<!-- Create a HTML table to format the output -->
  <table cellspacing="0" cellpadding="1" border="1">
    <tr>
        <th>ID</th>
        <th>Name</th>
        <th>Address</th>
        <th>Phone</th>
        <th>EMail</th>
        <th>Actions</th>
      </tr>
<?php
    // output data of each row
  while($row = mysqli_fetch_assoc($result)) {
    echo "<tr>";
        echo "<td>" . $row["id"] . "</td>";
        echo "<td>" . $row["name"] . "</td>";
        echo "<td>" . $row["address"] . "</td>";
        echo "<td>" . $row["phone"] . "</td>";
        echo "<td>" . $row["email"] . "</td>";
        // Links to update and delete file by passing table "id" fieled
        echo "<td>";
        echo "<a href=update.php?id=" . $row["id"] . "><button type=button>Update</button></a>&nbsp;"; 
        echo "<a href=delete.php?id=" . $row["id"] . "><button type=button Onclick='return confirm_delete();'>Delete</button></a>";
        echo "</td>";
    echo "</tr>";
  }
  echo "</table>";
} else {
  echo "<h3>No record found</h3>";
}
// Close mysql connection
mysqli_close($conn);
?>
</body>
</html>

Create Record

<html lang="en">
<head>
    <link rel="stylesheet" href="styles.css">
    <title>Create Record</title>
</head>
<body>
    <h1>CRUD application with PHP and MySQL</h1>
    <!-- HTML form for user input. Form submits to itself to execute PHP code bellow -->
    <form action="<?php ($_SERVER["PHP_SELF"]); ?>" method="post">
        <label for="form_name">Name</label>
        <input type="text" id="form_name" name="form_name">
        <label for="form_address">Address</label>
        <textarea id="form_address" name="form_address"></textarea>
        <label for="form_phone">Phone</label>
        <!-- Input type tel for phone number with formating (3 numbers - 3 numbers - 4 numbers) -->
        <input type="tel" id="form_phone" name="form_phone" pattern="[0-9]{3}-[0-9]{3}-[0-9]{4}">
        <small>Format: 123-456-7890</small><br>
        <!-- Input type email for validation -->
        <label for="form_email">Email</label>
        <input type="email" id="form_email" name="form_email">
        <input type="submit">
    </form>
</body>
</html>
<?php  
    // Include the config file
    include('config.php');
    // If form submits and method is POST
    if($_SERVER["REQUEST_METHOD"] == "POST"){
        // Get form session variables
        $input_name = $_POST["form_name"];
        $input_address = $_POST["form_address"];
        $input_phone = $_POST["form_phone"];
        $input_email = $_POST["form_email"];
        // Insert the variables's data into table
        $sql = "INSERT INTO address_table (name, address, phone, email)
            VALUES ('$input_name', '$input_address', '$input_phone', '$input_email')";
        // Execute the query
        if(mysqli_query($conn, $sql)){
            // On success, redirect to landing page
            header("location: index.php");
            exit();
        } else{
            // If faild, throw an error
            echo "Something went wrong. Please try again later.";
        }
    }
// Close mysql connection
mysqli_close($conn);
?>

Update Record

<?php
// Include style sheet file
echo "<style>";
include('styles.css');
echo "</style>";
// Include the config file
include('config.php');
// Get record id from landing page link
$id=$_GET["id"];
// Select the field to be updated
$sql = "SELECT * FROM address_table where id=$id";
// execute the query
$result = mysqli_query($conn, $sql);
// Get the row
$row = mysqli_fetch_assoc($result);
?>
<!DOCTYPE html>
<html lang="en">
<head>
    <title>Update Record</title>
</head>
<body>
    <h1>CRUD application with PHP and MySQL</h1>
    <!-- HTML form with old data. Form submits to itself to execute PHP code bellow -->
    <form action="<?php ($_SERVER["PHP_SELF"]); ?>" method="post">
        <label for="form_name">Name</label>
        <input type="text" id="form_name" name="form_name" value="<?php echo $row["name"]; ?> ">
        <label for="form_address">Address</label>
        <textarea id="form_address" name="form_address"><?php echo $row["address"]; ?></textarea>
        <label for="form_phone">Phone</label>
        <!-- Input type tel for phone number with formating (3 numbers - 3 numbers - 4 numbers) -->
        <input type="tel" id="form_phone" name="form_phone" pattern="[0-9]{3}-[0-9]{3}-[0-9]{4}" value="<?php echo $row["phone"]; ?>">
        <small>Format: 123-456-7890</small><br>
        <!-- Input type email for validation -->
        <label for="form_email">Email</label>
        <input type="text" id="form_email" name="form_email" value="<?php echo $row["email"]; ?> ">
        <input type="submit">
    </form>
</body>
</html>
<?php  
    // If form submits and method is POST? Get form data into variables
    if($_SERVER["REQUEST_METHOD"] == "POST"){
        $input_name = $_POST["form_name"];
        $input_address = $_POST["form_address"];
        $input_phone = $_POST["form_phone"];
        $input_email = $_POST["form_email"];
        // Update record with new changed data
        $sql = "UPDATE address_table SET name='$input_name', address='$input_address', phone='$input_phone', email='$input_email' where id='$id'";
        // Execute the query
        if(mysqli_query($conn, $sql)){
            // If successfull, redirect to landing page
            header("location: index.php");
            exit();
        } else{
            // If faild, throw an error
            echo "Something went wrong. Please try again later.";
        }
    }
// Close mysql connection
mysqli_close($conn);
?>

Delete Record

<?php
// Include the config file
include('config.php');
// Get record id from landing page link
$id=$_GET["id"];
// Delete field query
$sql = "DELETE FROM address_table where id=$id";
if (mysqli_query($conn, $sql)) {
    header("location: index.php");
  } else {
    echo "Error deleting record: " . mysqli_error($conn);
  }
// Close mysql connection
mysqli_close($conn);
?>

That’s it. I hope that it is again helpful and you have enjoyed reading it. You can access our previous blog here for more details.

If you face any difficulty or have some suggestion for improvement, please use comment bellow.

You can find the complete source code of this tutorial at Github.