In this tutorial we will build simple CRUD application using Php and MySql. CRUD stands for Create, Read, Update and Delete. Create is for inserting the data using an INSERT statement. Read is for reading the data using a SELECT statement. Update is for updating the data using an UPDATE statement. And Delete is for deleting the data using a DELETE statement. On this tutorial we will create a simple php application to perform all these operation on a MySql database table at one place.

Well let’s start by creating the database and table, which we will use in our example :

Creating the Database and Table

Run the below query to create the database named crud.

CREATE DATABASE crud;

Now, execute the following command to create the table named person inside your crud database.

CREATE TABLE IF NOT EXISTS `person` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `name` varchar(100) NOT NULL,
  `email` varchar(100) NOT NULL,
  `mobile` varchar(100) NOT NULL,
  `address` varchar(100) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Creating the dbconnect file

After creating the table, we need to connect to the MySql server. We will write a PHP script to connect to the server. For that, let’s create a file named dbconnect.php and write the following code

<?php
$host = 'localhost';
$user = 'root';
$password = '';
$db = 'crud';
$con = mysqli_connect( $host , $user, $password ) or die ( 'Could not connect to server' . mysqli_error ($con) );
mysqli_select_db( $con , $db ) or die ( 'Could not connect to database' . mysqli_error ($con) );
?>

Don’t forget to replace the credentials according to your MySql setting. Otherwise, this code will not work.

Creating the Landing Page

At first, let’s create a landing page for our application. This page will contain the table showing the records from the database. There is also a action button for each record displayed on the table. These button are used to read, edit and delete data from database.

We will also place one button at the top of the table. This button is used for creating the new records on database. Create a file named index.php and put the following code on it.

<!DOCTYPE html>
<html lang="en">
<head>
    <title>Crud Demo</title>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.0/css/bootstrap.min.css">
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
    <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.0/js/bootstrap.min.js"></script>
</head>
<body>

<div class="container">
    <div class="row">
        <h3>PHP CRUD Example</h3>
    </div>

    <div class="row">
        <p>
            <a href="create.php" class="btn btn-success">Create</a>
        </p>
        <table class="table table-bordered table-striped">
            <thead>
            <tr>
                <td>Name</td>
                <td>Email</td>
                <td>Mobile</td>
                <td>Address</td>
                <td>Action</td>
            </tr>
            </thead>
            <tbody>
            <?php
            include 'dbconnect.php';

            $query = mysqli_query($con, 'SELECT * FROM person ORDER BY id DESC');
            while ($row = mysqli_fetch_assoc($query)){
                echo '<tr>';
                    echo '<td>'.$row['name'].'</td>';
                    echo '<td>'.$row['email'].'</td>';
                    echo '<td>'.$row['mobile'].'</td>';
                    echo '<td>'.$row['address'].'</td>';
                    echo '<td><a href="read.php?id='.$row['id'].'" class="btn btn-default">Read</a>
                              <a href="update.php?id='.$row['id'].'" class="btn btn-success">Update</a>
                              <a href="delete.php?id='.$row['id'].'" class="btn btn-danger">Delete</a></td>';
                echo '</tr>';
            }

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

</body>
</html>

Now your table will look like this

Crud Table

Creating the create page

In this section we will be building the create functionality for our CRUD application.

Let’s create a file named create.php and put the following code inside it. It will generate a simple form that can can be used to insert data on person table.

<?php

//include connection file
include 'dbconnect.php';

// Define variables and initializes with empty values
$nameError = $emailError = $mobileError = $addressError = null;
$name = $email = $mobile = $address = null;

//Processing form data when form is submitted
if($_SERVER["REQUEST_METHOD"] == "POST"){

    //validate name
    if(empty($_POST["name"])){
        $nameError = "Please enter a name.";
    }else{
        $name = trim($_POST["name"]);
    }

    //validate email
    if(empty($_POST["email"])){
        $emailError = "Please enter a email.";
    }else{
        $email = trim($_POST["email"]);
    }

    //validate mobile
    if(empty($_POST["mobile"])){
        $mobileError = "Please enter a mobile.";
    }else{
        $mobile = trim($_POST["mobile"]);
    }

    //validate address
    if(empty($_POST["address"])){
        $addressError = "Please enter a address";
    }else{
        $address = trim($_POST["address"]);
    }

    //checking input errors before inserting in database
    if( empty($nameError) &amp;&amp; empty($emailError) &amp;&amp; empty($mobileError) &amp;&amp; empty($addressError) ){
        //Prepare an insert statement
        $sql = "INSERT INTO person (name, email, mobile, address) VALUES ('".$name."', '".$email."', '".$mobile."', '".$address."')";
        $query = mysqli_query($con, $sql);

        if($query){
            header("Location: index.php");
        }

    }

}

?>

<!DOCTYPE html>
<html lang="en">
<head>
    <title>Crud Demo</title>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.0/css/bootstrap.min.css">
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
    <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.0/js/bootstrap.min.js"></script>
</head>
<body>
    <div class="container">
        <div class="row">
            <h3>Add new Person</h3>
        </div>

        <form action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]); ?>" method="POST" class="form-horizontal">
            <div class="form-group <?php echo !empty($nameError)? 'error': ''; ?>">
                <label class="control-label col-sm-2" for="name">Name</label>
                <div class="col-sm-10">
                    <input name="name" class="form-control" type="text" placeholder="Name" value="<?php echo !empty($name) ? $name : ''; ?>">
                    <?php
                    if(!empty($nameError)){
                        ?>
                        <span class="help-inline"><?php echo $nameError; ?></span>
                        <?php
                    }
                    ?>
                </div>
            </div>

            <div class="form-group <?php echo !empty($emailError)? 'error': ''; ?>">
                <label class="control-label col-sm-2" for="name">Email</label>
                <div class="col-sm-10">
                    <input name="email" class="form-control" type="email" placeholder="Email" value="<?php echo !empty($email) ? $email : ''; ?>">
                    <?php
                    if(!empty($emailError)){
                        ?>
                        <span class="help-inline"><?php echo $emailError; ?></span>
                        <?php
                    }
                    ?>
                </div>
            </div>
            <div class="form-group <?php echo !empty($mobileError)? 'error': ''; ?>">
                <label class="control-label col-sm-2" for="mobile">Moble</label>
                <div class="col-sm-10">
                    <input name="mobile" class="form-control" type="text" placeholder="Mobile" value="<?php echo !empty($mobile) ? $mobile : ''; ?>">
                    <?php
                    if(!empty($mobileError)){
                        ?>
                        <span class="help-inline"><?php echo $mobileError; ?></span>
                        <?php
                    }
                    ?>
                </div>
            </div>
            <div class="form-group <?php echo !empty($addressError)? 'error': ''; ?>">
                <label class="control-label col-sm-2" for="address">Address</label>
                <div class="col-sm-10">
                    <input name="address" class="form-control" type="text" placeholder="Address" value="<?php echo !empty($address) ? $address : ''; ?>">
                    <?php
                    if(!empty($addressError)){
                        ?>
                        <span class="help-inline"><?php echo $addressError; ?></span>
                        <?php
                    }
                    ?>
                </div>
            </div>
            <div class="form-group">
                <div class="col-sm-offset-2 col-sm-10">
                    <button type="submit" class="btn btn-success">Create</button>
                    <a class="btn" class="btn btn-info" href="index.php">Back</a>
                </div>
            </div>

        </form>
    </div>
</body>
</html>

Creating the Read Page

Here we will build the read functionality for our CRUD application.

Let’s create a file named read.php and add the following code on it. It will simply fetch the records from the person table based on the id.

<?php

require 'dbconnect.php';

$id = null;

if(!empty($_GET['id'])){
    $id = $_REQUEST['id'];
}

if(null == $id){
    header("Location: index.php");
}else{
    $sql = "SELECT * FROM person WHERE id=".$id;
    $query = mysqli_query($con, $sql);
    $row = mysqli_fetch_assoc($query);
}
?>

<!DOCTYPE html>
<html lang="en">
<head>
    <title>Crud Demo</title>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.0/css/bootstrap.min.css">
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
    <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.0/js/bootstrap.min.js"></script>
</head>
<style>
    #data {
        border: 1px solid;
        padding: 10px;
        box-shadow: 5px 10px #888888;
        width: 500px;
    }
</style>
<body>
<div class="container" style="margin-top:20px;">
    <h3>Read Data</h3>
    <a href="index.php" class="btn btn-info pull-right">Back</a>
    <div id="data">
        <p>Name : <strong><?php echo $row['name']; ?></strong></p>
        <p>Email : <strong><?php echo $row['email']; ?></strong></p>
        <p>Mobile : <strong><?php echo $row['mobile']; ?></strong></p>
        <p>Address : <strong><?php echo $row['address']; ?></strong></p>
    </div>
</div>
</body>
</html>

Creating the Update Page

Now it’s time to build the Update functionality for our application.

Let’s create a file named update.php and place the following code on it. It will update the existing the record in the person table based on the id field of table.

<?php

//include connection file
include 'dbconnect.php';

$id = null;

if(!empty($_GET['id'])){
    $id = $_REQUEST['id'];
}

if ( null==$id ) {
    header("Location: index.php");
}

$data = mysqli_query($con, 'SELECT * FROM person WHERE id='.$id);
$row = mysqli_fetch_assoc($data);

// Define variables and initializes with empty values
$nameError = $emailError = $mobileError = $addressError = null;
$name = $row['name'];
$email = $row['email'];
$mobile = $row['mobile'];
$address = $row['address'];


//Processing form data when form is submitted
if($_SERVER["REQUEST_METHOD"] == "POST"){

    //validate name
    if(empty($_POST["name"])){
        $nameError = "Please enter a name.";
    }else{
        $name = trim($_POST["name"]);
    }

    //validate email
    if(empty($_POST["email"])){
        $emailError = "Please enter a email.";
    }else{
        $email = trim($_POST["email"]);
    }

    //validate mobile
    if(empty($_POST["mobile"])){
        $mobileError = "Please enter a mobile.";
    }else{
        $mobile = trim($_POST["mobile"]);
    }

    //validate address
    if(empty($_POST["address"])){
        $addressError = "Please enter a address";
    }else{
        $address = trim($_POST["address"]);
    }

    //checking input errors before inserting in database
    if( empty($nameError) &amp;&amp; empty($emailError) &amp;&amp; empty($mobileError) &amp;&amp; empty($addressError) ){
        //Prepare an insert statement
        $sql = "UPDATE person set name='".$name."', email='".$email."', mobile='".$mobile."', address='".$address."' WHERE id='".$id."' ";
        $query = mysqli_query($con, $sql);

        if($query){
            header("Location: index.php");
        }

    }

}

?>

<!DOCTYPE html>
<html lang="en">
<head>
    <title>Crud Demo</title>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.0/css/bootstrap.min.css">
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
    <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.0/js/bootstrap.min.js"></script>
</head>
<body>
<div class="container">
    <div class="row">
        <h3>Add new Person</h3>
    </div>

    <form action="update.php?id=<?php echo $id;?>" method="POST" class="form-horizontal">
        <div class="form-group <?php echo !empty($nameError)? 'error': ''; ?>">
            <label class="control-label col-sm-2" for="name">Name</label>
            <div class="col-sm-10">
                <input name="name" class="form-control" type="text" placeholder="Name" value="<?php echo !empty($name) ? $name : ''; ?>">
                <?php
                if(!empty($nameError)){
                    ?>
                    <span class="help-inline"><?php echo $nameError; ?></span>
                    <?php
                }
                ?>
            </div>
        </div>

        <div class="form-group <?php echo !empty($emailError)? 'error': ''; ?>">
            <label class="control-label col-sm-2" for="name">Email</label>
            <div class="col-sm-10">
                <input name="email" class="form-control" type="email" placeholder="Email" value="<?php echo !empty($email) ? $email : ''; ?>">
                <?php
                if(!empty($emailError)){
                    ?>
                    <span class="help-inline"><?php echo $emailError; ?></span>
                    <?php
                }
                ?>
            </div>
        </div>
        <div class="form-group <?php echo !empty($mobileError)? 'error': ''; ?>">
            <label class="control-label col-sm-2" for="mobile">Moble</label>
            <div class="col-sm-10">
                <input name="mobile" class="form-control" type="text" placeholder="Mobile" value="<?php echo !empty($mobile) ? $mobile : ''; ?>">
                <?php
                if(!empty($mobileError)){
                    ?>
                    <span class="help-inline"><?php echo $mobileError; ?></span>
                    <?php
                }
                ?>
            </div>
        </div>
        <div class="form-group <?php echo !empty($addressError)? 'error': ''; ?>">
            <label class="control-label col-sm-2" for="address">Address</label>
            <div class="col-sm-10">
                <input name="address" class="form-control" type="text" placeholder="Address" value="<?php echo !empty($address) ? $address : ''; ?>">
                <?php
                if(!empty($addressError)){
                    ?>
                    <span class="help-inline"><?php echo $addressError; ?></span>
                    <?php
                }
                ?>
            </div>
        </div>
        <div class="form-group">
            <div class="col-sm-offset-2 col-sm-10">
                <button type="submit" class="btn btn-success">Update</button>
                <a class="btn" class="btn btn-info" href="index.php">Back</a>
            </div>
        </div>

    </form>
</div>
</body>
</html>

Creating the Delete Page

Similarly, let’s create a delete functionality for our CRUD application.

Let’s create a file named delete.php and add the following code on it. It will ask the confirmation whether you want to delete the record or not. If you click on yes it will delete the record form the database base on id field. Otherwise it will terminate the deletion of the data from the database. And return to the landing page.

<?php

//include connection file
include 'dbconnect.php';

$id = null;

if(!empty($_GET['id'])){
    $id = $_REQUEST['id'];
}

if ( null==$id ) {
    header("Location: index.php");
}

//Processing form data when form is submitted
if($_SERVER["REQUEST_METHOD"] == "POST"){

    $id = $_POST['id'];

    $sql = "DELETE FROM person WHERE id = $id";
    $query = mysqli_query($con, $sql);

    if($query){
        header("Location: index.php");
    }
}

?>
<!DOCTYPE html>
<html lang="en">
<head>
    <title>Crud Demo</title>
    <meta charset="utf-8">
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.0/css/bootstrap.min.css">
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
    <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.4.0/js/bootstrap.min.js"></script>
</head>
<body>
<div class="container">

        <div class="row">
            <h3>Delete a Person</h3>
        </div>

        <form class="form-horizontal" action="<?php echo htmlspecialchars($_SERVER["PHP_SELF"]); ?>" method="post">
            <input type="hidden" name="id" value="<?php echo $id;?>"/>
            <p class="alert alert-danger">Are you sure you want to delete this?</p>
            <div class="form-actions">
                <button type="submit" class="btn btn-danger">Yes</button>
                <a class="btn btn-default" href="index.php">No</a>
            </div>
        </form>
</div>
</body>
</html>

Conclusion

In this lesson, we build a CRUD application using PHP and MySql. This is a simple example. You can add more complex logic, design and validations as per your requirements. If you wish to add to the discussion or would like to ask a question, then leave a comment below.

Download Code from below

Comments

  1. Hello, you used to write great, but the last several posts have been kinda boring… I miss your great writings. Past several posts are just a little bit out of track! come on!

Leave a Reply

Your email address will not be published. Required fields are marked *