PHP CRUD operation with MySQL Example

June 3, 2018 XpertPhp Comments 1 comment

PHP CRUD means Create, edit, update and delete(PHP CRUD operation with MySQL). PHP is a server-side programing language, generally, it is stored data in the MySQL database.

Whenever you start PHP crud that time will have compulsorily required database connectivity using PHP.

We will go through the steps of a creating PHP CRUD operation post. We want to demonstrate how to connect PHP with MySQL, We hope you can learn something new from this tutorial. in our post through how to connect PHP to MySQL, create a new record, fetch a record, update record and delete the record.

1. Create Database in table

CREATE TABLE IF NOT EXISTS `register` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(64) NOT NULL,
  `last_name` varchar(64) NOT NULL,
  `address` text NOT NULL,
  `email` varchar(64) NOT NULL,
  `mobile` varchar(12) NOT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=15 ;

CREATE TABLE IF NOT EXISTS `register` (

`id` int(11) NOT NULL AUTO_INCREMENT,

`first_name` varchar(64) NOT NULL,

`last_name` varchar(64) NOT NULL,

`address` text NOT NULL,

`email` varchar(64) NOT NULL,

`mobile` varchar(12) NOT NULL,

PRIMARY KEY (`id`)

) ENGINE=InnoDB DEFAULT CHARSET=latin1 AUTO_INCREMENT=15 ;

2. Connect to Database

Create a new connect.php file. The code below is to provide how to connect with the database.
The following code is used to connect MySQL from PHP. It requires hostname, database username, database password, and database name.

<?php

 $hostname="localhost";
 $username="root";
 $password="test";
 $database="register";

 $conn = mysqli_connect($hostname,$username,$password,$database); 
 
?>

<?php

$hostname="localhost";

$username="root";

$password="test";

$database="register";

$conn = mysqli_connect($hostname,$username,$password,$database);

3. Creating New Record in MySQL Database

Create a PHP file add.php in this file in add the below code.
The code below in the INSERT query through create a new record to the database table. This HTML form contains input fields to enter user data to be inserted into the table.

<?php
if(isset($_POST['btnadd']))
{
    $first_name  = $_POST['txtFname'];
    $last_name   = $_POST['txtLname'];
    $address     = $_POST['txtAddress'];
    $email       = $_POST['txtEmail'];
    $mobile      = $_POST['txtMobile'];

    include('connection.php');
    $insert="insert into register (first_name,last_name,address,email,mobile) values('$first_name','$last_name','$address','$email','$mobile')";
    mysqli_query($conn,$insert);
    header('location:index.php');
}
?>
<form method="post" name="frmAdd">
        <table align="center">
            <tr>
                <td colspan="2" align="center">Add Record</td>
            </tr>

            <tr>
                <td>First Name</td>
                <td><input type="text" name="txtFname"> </td>
            </tr>
            <tr>
                <td>Last Name</td>
                <td><input type="text" name="txtLname"> </td>
            </tr>
            <tr>
                <td>Address</td>
                <td><textarea name="txtAddress" rows="4" cols="16"></textarea> </td>
            </tr>
            <tr>
                <td>Email</td>
                <td><input type="text" name="txtEmail"> </td>
            </tr>
            <tr>
                <td>Mobile</td>
                <td><input type="text" name="txtMobile"> </td>
            </tr>
            <tr>
                <td colspan="2" align="center"><input type="submit" value="Add" name="btnadd"> </td>
            </tr>
        </table>
</form>

<?php

if(isset($_POST['btnadd']))

{

$first_name = $_POST['txtFname'];

$last_name = $_POST['txtLname'];

$address = $_POST['txtAddress'];

$email = $_POST['txtEmail'];

$mobile = $_POST['txtMobile'];

include('connection.php');

$insert="insert into register (first_name,last_name,address,email,mobile) values('$first_name','$last_name','$address','$email','$mobile')";

mysqli_query($conn,$insert);

header('location:index.php');

}

<tr>

<td colspan="2" align="center">Add Record</td>

</tr>

<tr>

<td>First Name</td>

</tr>

<tr>

</tr>

<tr>

<td>Address</td>

</tr>

<tr>

<td>Email</td>

</tr>

<tr>

<td>Mobile</td>

</tr>

<tr>

</tr>

</table>

</form>

4. PHP MySQL Read

Create a new PHP file index.php in this file in add the below code.
The following code shows how to fetch all the records from the database in the index file.

<table border="1" align="center">
    <tr>
        <td colspan="7" align="right"><a href="add.php">Add</a></td>
    </tr>
    <tr>
        <td>First Name</td>
        <td>Last Name</td>
        <td>Address</td>
        <td>Email</td>
        <td>Mobile</td>
        <td>Action</td>
    </tr>
    <?php
    include('connection.php');

    $slt="select * from register";
    $rec=mysqli_query($conn,$slt);
    while($row=mysqli_fetch_array($rec))
    {
        ?>
        <tr>
            <td><?php echo $row['first_name']; ?></td>
            <td><?php echo $row['last_name']; ?></td>
            <td><?php echo $row['address']; ?></td>
            <td><?php echo $row['email']; ?></td>
            <td><?php echo $row['mobile']; ?></td>
            <td><a href="update.php?edit_id=<?php echo $row['id'];?>">Edit</a>&amp;nbsp;&amp;nbsp;<a href="delete.php?delete_id=<?php echo $row['id'];?>">Delete</a> </td>
        </tr>
        <?php
    }
    ?>
   
</table>

<tr>

</tr>

<tr>

<td>First Name</td>

<td>Address</td>

<td>Email</td>

<td>Mobile</td>

<td>Action</td>

</tr>

<?php

include('connection.php');

$slt="select * from register";

$rec=mysqli_query($conn,$slt);

while($row=mysqli_fetch_array($rec))

{

<tr>

<td><a href="update.php?edit_id=<?php echo $row['id'];?>">Edit</a>&nbsp;&nbsp;<a href="delete.php?delete_id=<?php echo $row['id'];?>">Delete</a> </td>

</tr>

<?php

}

</table>

5. Php MySQL Update Record

Create a new update.php file in this file to add the below code.
First, we will fetch a particular data record in the update form. On submitting edited user information we form an update query to edit the record with the reference of its edit_id.

<?php
$edit_id = $_REQUEST['edit_id'];
include('connection.php');
$slt="select * from register where id=$edit_id";
$rec=mysqli_query($conn,$slt);
$row=mysqli_fetch_array($rec);
?>
<?php
if(isset($_POST['btnUpdate']))
{
    $first_name  = $_POST['txtFname'];
    $last_name   = $_POST['txtLname'];
    $address     = $_POST['txtAddress'];
    $email       = $_POST['txtEmail'];
    $mobile      = $_POST['txtMobile'];

    include('connection.php');
    $update="update register set first_name='$first_name',last_name='$last_name',address='$address',email='$email',mobile='$mobile' where id='".$_REQUEST['edit_id']."'";
    mysqli_query($conn,$update);
    header('location:index.php');
}
?>

<form method="post" name="frmUpdate">
    <table align="center">
        <tr>
            <td colspan="2" align="center">Update Record</td>
        </tr>

        <tr>
            <td>First Name</td>
            <td><input type="text" name="txtFname" value="<?php echo $row['first_name'];?>"> </td>
        </tr>
        <tr>
            <td>Last Name</td>
            <td><input type="text" name="txtLname" value="<?php echo $row['last_name'];?>"> </td>
        </tr>
        <tr>
            <td>Address</td>
            <td><textarea name="txtAddress" rows="4" cols="16"><?php echo $row['address'];?></textarea> </td>
        </tr>
        <tr>
            <td>Email</td>
            <td><input type="text" name="txtEmail" value="<?php echo $row['email'];?>"> </td>
        </tr>
        <tr>
            <td>Mobile</td>
            <td><input type="text" name="txtMobile" value="<?php echo $row['mobile'];?>"> </td>
        </tr>
        <tr>
            <td colspan="2" align="center"><input type="submit" value="Update" name="btnUpdate"> </td>
        </tr>
    </table>
</form>

<?php

$edit_id = $_REQUEST['edit_id'];

include('connection.php');

$slt="select * from register where id=$edit_id";

$rec=mysqli_query($conn,$slt);

$row=mysqli_fetch_array($rec);

<?php

if(isset($_POST['btnUpdate']))

{

$first_name = $_POST['txtFname'];

$last_name = $_POST['txtLname'];

$address = $_POST['txtAddress'];

$email = $_POST['txtEmail'];

$mobile = $_POST['txtMobile'];

include('connection.php');

$update="update register set first_name='$first_name',last_name='$last_name',address='$address',email='$email',mobile='$mobile' where id='".$_REQUEST['edit_id']."'";

mysqli_query($conn,$update);

header('location:index.php');

}

<tr>

<td colspan="2" align="center">Update Record</td>

</tr>

<tr>

<td>First Name</td>

</tr>

<tr>

</tr>

<tr>

<td>Address</td>

</tr>

<tr>

<td>Email</td>

</tr>

<tr>

<td>Mobile</td>

</tr>

<tr>

</tr>

</table>

</form>

6. Delete Record from MySQL Database

Create a new delete.php file in this file to add the below code.
The below code is used to delete a particular record from the database.

<?php
include('connection.php');

$delete="delete from register where id='".$_REQUEST['delete_id']."'";
mysqli_query($conn,$delete);
header("location:index.php");
?>

<?php

include('connection.php');

$delete="delete from register where id='".$_REQUEST['delete_id']."'";

mysqli_query($conn,$delete);

header("location:index.php");

We think would you like this article, so you can click on “Show Demo” button and you can see this demo article.

Show Demo

Please follow and like us: