Most Popular Tutorials
Most Popular Tutorials :-

Simply Easy Learning at Your Fingertips. Click Tutorials Menu to view More Tutorial List





How to create CRUD using procedure in php with mysql?

Create Database Table:-

Table Name : users

CREATE TABLE `users` (
`id` int(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
`first_Name` varchar(200) DEFAULT NULL,
`last_Name` varchar(200) DEFAULT NULL,
`mobile_Number` bigint(10) DEFAULT NULL,
`email` varchar(200) DEFAULT NULL,
`address` mediumtext DEFAULT NULL,
`created_at` timestamp NOT NULL DEFAULT current_timestamp() ON UPDATE current_timestamp()
) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4;

Create Dbconnection

File Name : dbconnection.php

<?php
$host = "localhost";
$username ="ittutorial";
$password = "sana_mahtab";
$db_name = "itechxpert";

$conn = mysqli_connect($host,$username,$password)or die(mysqli_error());
mysqli_select_db($conn,$db_name)or die(mysqli_error($conn));
// $conn = mysqli_connect("localhost", "root", "", "itechxpert");

// $conn = mysqli_connect($host,$username,$password,$db_name)or die(mysqli_error());
// Check connection
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}

#################### OR ##############################


define ( 'DB_HOST', 'localhost' );
define ( 'DB_USER', 'gmax' );
define ( 'DB_PASSWORD', 'gmaxpass' );
define ( 'DB_NAME', 'gmaxdb' );

$conn = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD) or die('Could not connect to database server.');
mysqli_select_db($conn,DB_NAME) or die('Could not select database.');
// // $conn = mysqli_connect("localhost", "root", "", "itechxpert");

// $conn = mysqli_connect(DB_HOST, DB_USER, DB_PASSWORD,DB_NAME) or die('Could not connect to database server.');
// Check connection
if (mysqli_connect_errno())
{
echo "Failed to connect to MySQL: " . mysqli_connect_error();
}
?>

Insert record

Create an insert-data.php file for insert record in the database.

File Name : insert-data.php

<?php
// include database connection file
require_once 'dbconnection.php';
if(isset($_POST['insert']))
{
// Posted Values
$fname=$_POST['firstname'];
$lname=$_POST['lastname'];
$emailid=$_POST['emailid'];
$contactno=$_POST['contactno'];
$address=$_POST['address'];
// Call the store procedure for insertion
$sql=mysqli_query($con,"call sp_insert('$fname','$lname','$emailid','$contactno','$address')");
if($sql)
{
// Message for successfull insertion
echo "<script>alert('Record inserted successfully');</script>";
echo "<script>window.location.href='index.php'</script>";
}
else
{
// Message for unsuccessfull insertion
echo "<script>alert('Something went wrong. Please try again');</script>";
echo "<script>window.location.href='index.php'</script>";
}
}
?>

<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="utf-8">
<title>CURD Operation using Stored Procedure in php with mysql </title>
<meta name="viewport" content="width=device-width, initial-scale=1">
<link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.0/css/bootstrap.min.css" rel="stylesheet">
<script src="https://code.jquery.com/jquery-1.11.1.min.js"></script>
<script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.0/js/bootstrap.min.js"></script>
</head>
<body>
<div class="container">
<div class="row">
<div class="col-md-12">
<h3>CURD Operation using Stored Procedure in php with mysql</h3>
<hr />
</div>
</div>

<form name="frm" method="post">
<div class="row">
<div class="col-md-4"><b>First Name</b>
<input type="text" name="firstname" class="form-control" required>
</div>
<div class="col-md-4"><b>Last Name</b>
<input type="text" name="lastname" class="form-control" required>
</div>
</div>

<div class="row">
<div class="col-md-4"><b>Email id</b>
<input type="email" name="emailid" class="form-control" required>
</div>
<div class="col-md-4"><b>Contactno</b>
<input type="text" name="contactno" class="form-control" maxlength="10" required>
</div>
</div>

<div class="row">
<div class="col-md-8"><b>Address</b>
<textarea class="form-control" name="address" required></textarea>
</div>
</div>

<div class="row" style="margin-top:1%">
<div class="col-md-8">
<input type="submit" name="insert" value="Submit">
</div>
</div>
</form>

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

create procedure sp_insert

DELIMITER $$
CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_insert`(
fname varchar(120),
lname varchar(120),
emailid varchar(150),
cntnumber bigint(12),
address varchar(255)
)
BEGIN
insert into users(first_name,last_name,email,mobile_number,address) value(fname,lname,emailid,cntnumber,address);
END$$
DELIMITER ;



#################### OR ##########################



open phpmyadmin

  • select database
  • click on the routines button from menus
  • click add routine
  • type routine name :- sp_insert
  • type :- procedure
  • add parameters
  • add defenitions :
    BEGIN
    insert into users(first_name,last_name,email,mobile_number,address) value(fname,lname,emailid,cntnumber,address);
    END
  • Definer :- root`@`localhost
  • Security type :- DEFINER
  • Click :- Go

    • query creaed: - CREATE PROCEDURE `sp_insert`(IN `fname` VARCHAR(64), IN `lname` VARCHAR(64), IN `emailid` VARCHAR(64), IN `cntnumber` INT(12), IN `address` VARCHAR(255)) NOT DETERMINISTIC NO SQL SQL SECURITY DEFINER BEGIN insert into tblusers(FirstName,LastName,EmailId,ContactNumber,Address) value(fname,lname,emailid,cntnumber,address); END

    DELIMITER $$
    CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_insert`(IN `fname` VARCHAR(64), IN `lname` VARCHAR(64), IN `emailid` VARCHAR(64), IN `cntnumber` INT(12), IN `address` VARCHAR(255))
    NO SQL
    BEGIN
    insert into users(first_name,last_name,email,mobile_number,address) value(fname,lname,emailid,cntnumber,address);
    END$$
    DELIMITER ;

    How to show procedure in phpmyadmin

    File Name :

  • select database
  • click Routines button from menus
  • show procedure list
  • EDIT / EXECUTE / EXPORT
  • click on EXPORT
  • then Show stored procedure query


    • DELIMITER $$
    CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_insert`(IN `fname` VARCHAR(64), IN `lname` VARCHAR(64), IN `emailid` VARCHAR(64), IN `cntnumber` INT(12), IN `address` VARCHAR(255))
    NO SQL
    BEGIN
    insert into users(FirstName,LastName,EmailId,ContactNumber,Address) value(fname,lname,emailid,cntnumber,address);
    END$$
    DELIMITER ;

    Read record from the database

    Create a stored procedure (sp_read) for read data

    File Name :

    BEGIN
    select * from users;
    END



    #################### OR #################



    DELIMITER $$
    CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_read`()
    NO SQL
    BEGIN
    select * from users;
    END$$
    DELIMITER ;

    Index page

    File Name : index.php

    <?php
    // include database connection file
    require_once'dbconnection.php';
    ?>
    <!DOCTYPE html>
    <html lang="en">
    <head>
    <meta charset="utf-8">
    <title>CRUD Operations using Stored Procedure in php using mysql </title>
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.0/css/bootstrap.min.css" rel="stylesheet">
    <style >

    </style>
    <script src="https://code.jquery.com/jquery-1.11.1.min.js"></script>
    <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.0/js/bootstrap.min.js"></script>
    <script src="https://ajax.googleapis.com/ajax/libs/jquery/1.9.1/jquery.min.js"></script>
    <script src="https://getbootstrap.com/dist/js/bootstrap.min.js"></script>
    </head>
    <body>
    <div class="container">
    <div class="row">
    <div class="col-md-12">
    <h3>CRUD Operations using Stored Procedure in php using mysql </h3> <hr />
    <a href="insert-data.php"><button class="btn btn-primary"> Insert Record</button></a>
    <div class="table-responsive">
    <table id="mytable" class="table table-bordred table-striped">
    <thead>
    <th>#</th>
    <th>First Name</th>
    <th>Last Name</th>
    <th>Email</th>
    <th>Contact</th>
    <th>Address</th>
    <th>Posting Date</th>
    <th>Edit</th>
    <th>Delete</th>
    </thead>
    <tbody>

    <?php
    $sql =mysqli_query($con, "call sp_read()");
    $cnt=1;
    $row=mysqli_num_rows($sql);
    if($row>0){
    while ($result=mysqli_fetch_array($sql)) {
    ?>
    <tr>
    <td><?php echo htmlentities($cnt);?></td>
    <td><?php echo htmlentities($result['FirstName']);?></td>
    <td><?php echo htmlentities($result['LastName']);?></td>
    <td><?php echo htmlentities($result['EmailId']);?></td>
    <td><?php echo htmlentities($result['ContactNumber']);?></td>
    <td><?php echo htmlentities($result['Address']);?></td>
    <td><?php echo htmlentities($result['PostingDate']);?></td>

    <td><a href="update.php?id=<?php echo htmlentities($result['id']);?>"><button class="btn btn-primary btn-xs"><span class="glyphicon glyphicon-pencil"></span></button></a></td>

    <td><a href="index.php?del=<?php echo htmlentities($result['id']);?>"><button class="btn btn-danger btn-xs" onClick="return confirm('Do you really want to delete');"><span class="glyphicon glyphicon-trash"></span></button></a></td>
    </tr>

    <?php
    // for serial number increment
    $cnt++;
    } } else { ?>
    <tr>
    <td colspan="9" style="color:red; font-weight:bold;text-align:center;"> No Record found</td>
    </tr>
    <?php } ?>
    </tbody>
    </table>
    </div>
    </div>
    </div>
    </div>
    </body>
    </html>

    Update record in the database using stored procedure

    Create a procedure sp_readarow for read data of a particular row from table.

    File Name :

    DELIMITER $$
    CREATE DEFINER=`root`@`localhost` PROCEDURE `sp_readarow`()
    NO SQL
    BEGIN
    select * from users where id=rid;
    END$$
    DELIMITER ;

    Update Record using stored procedure

    File Name : update.php

    <?php
    // include database connection file
    require_once'dbconnection.php';
    ?>
    <!DOCTYPE html>
    <html lang="en">
    <head>
    <meta charset="utf-8">
    <title>CURD Operation using Stored Procedure in php using mysql</title>
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.0/css/bootstrap.min.css" rel="stylesheet">
    <script src="https://code.jquery.com/jquery-1.11.1.min.js"></script>
    <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.0/js/bootstrap.min.js"></script>
    </head>
    <body>

    <div class="container">

    <div class="row">
    <div class="col-md-12">
    <h3>Update Record | PHP CRUD Operations using Stored Procedure</h3>
    <hr />
    </div>
    </div>

    <?php
    // Get the userid
    $userid=intval($_GET['id']);
    $sql =mysqli_query($conn, "call sp_readarow('$userid')");
    while ($result=mysqli_fetch_array($sql)) {
    ?>
    <form name="insertrecord" method="post">
    <div class="row">
    <div class="col-md-4"><b>First Name</b>
    <input type="text" name="firstname" value="<?php echo htmlentities($result['FirstName']);?>" class="form-control" required>
    </div>
    <div class="col-md-4"><b>Last Name</b>
    <input type="text" name="lastname" value="<?php echo htmlentities($result['LastName']);?>" class="form-control" required>
    </div>
    </div>

    <div class="row">
    <div class="col-md-4"><b>Email id</b>
    <input type="email" name="emailid" value="<?php echo htmlentities($result['EmailId']);?>" class="form-control" required>
    </div>
    <div class="col-md-4"><b>Contactno</b>
    <input type="text" name="contactno" value="<?php echo htmlentities($result['ContactNumber']);?>" class="form-control" maxlength="10" required>
    </div>
    </div>


    <div class="row">
    <div class="col-md-8"><b>Address</b>
    <textarea class="form-control" name="address" required><?php echo htmlentities($result['Address']);?></textarea>
    </div>
    </div>
    <?php } ?>

    <div class="row" style="margin-top:1%">
    <div class="col-md-8">
    <input type="submit" name="update" value="Update">
    </div>
    </div>
    </form>
    </div>
    </div>
    </body>
    </html>

    Create a procedure sp_update for for update a record

    File Name :

    BEGIN
    update users set first_name=fname,last_name=lname,email=emailid,mobile_number=cntnumber,address=address where id=rid;
    END

    Update record

    File Name :

    <?php

    require_once'dbconnection.php';
    if(isset($_POST['update']))
    {
    // Get the row id
    $rid=intval($_GET['id']);
    // Posted Values
    $fname=$_POST['firstname'];
    $lname=$_POST['lastname'];
    $emailid=$_POST['emailid'];
    $contactno=$_POST['contactno'];
    $address=$_POST['address'];
    // Store Procedure for Updation
    $sql=mysqli_query($conn,"call sp_update('$fname','$lname','$emailid','$contactno','$address','$rid')");
    // Mesage after updation
    echo "<script>alert('Record Updated successfully');</script>";
    // Code for redirection
    echo "<script>window.location.href='index.php'</script>";
    }
    ?>

    Full code of Update record

    File Name : update.php

    <?php
    // include database connection file
    require_once'dbconnection.php';
    if(isset($_POST['update']))
    {
    // Get the row id
    $rid=intval($_GET['id']);
    // Posted Values
    $fname=$_POST['firstname'];
    $lname=$_POST['lastname'];
    $emailid=$_POST['emailid'];
    $contactno=$_POST['contactno'];
    $address=$_POST['address'];

    // Store Procedure for Updation
    $sql=mysqli_query($con,"call sp_update('$fname','$lname','$emailid','$contactno','$address','$rid')");
    // Mesage after updation
    echo "<script>alert('Record Updated successfully');</script>";
    // Code for redirection
    echo "<script>window.location.href='index.php'</script>";
    }
    ?>
    <!DOCTYPE html>
    <html lang="en">
    <head>
    <meta charset="utf-8">
    <title>CURD Operation using Stored Procedure in php using mysql </title>
    <meta name="viewport" content="width=device-width, initial-scale=1">
    <link href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.0/css/bootstrap.min.css" rel="stylesheet">
    <script src="https://code.jquery.com/jquery-1.11.1.min.js"></script>
    <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.0/js/bootstrap.min.js"></script>
    </head>
    <body>

    <div class="container">

    <div class="row">
    <div class="col-md-12">
    <h3>CURD Operation using Stored Procedure in php using mysql </h3>
    <hr />
    </div>
    </div>

    <?php
    // Get the userid
    $userid=intval($_GET['id']);
    $sql =mysqli_query($conn, "call sp_readarow('$userid')");
    while ($result=mysqli_fetch_array($sql)) {
    ?>
    <form name="insertrecord" method="post">
    <div class="row">
    <div class="col-md-4"><b>First Name</b>
    <input type="text" name="firstname" value="<?php echo htmlentities($result['FirstName']);?>" class="form-control" required>
    </div>
    <div class="col-md-4"><b>Last Name</b>
    <input type="text" name="lastname" value="<?php echo htmlentities($result['LastName']);?>" class="form-control" required>
    </div>
    </div>

    <div class="row">
    <div class="col-md-4"><b>Email id</b>
    <input type="email" name="emailid" value="<?php echo htmlentities($result['EmailId']);?>" class="form-control" required>
    </div>
    <div class="col-md-4"><b>Contactno</b>
    <input type="text" name="contactno" value="<?php echo htmlentities($result['ContactNumber']);?>" class="form-control" maxlength="10" required>
    </div>
    </div>



    <div class="row">
    <div class="col-md-8"><b>Address</b>
    <textarea class="form-control" name="address" required><?php echo htmlentities($result['Address']);?></textarea>
    </div>
    </div>
    <?php } ?>

    <div class="row" style="margin-top:1%">
    <div class="col-md-8">
    <input type="submit" name="update" value="Update">
    </div>
    </div>
    </form>
    </div>
    </div>

    </body>
    </htm

    Delete a record from the database

    create a stored procedure sp_delete for delete a particular record

    File Name : update.php

    BEGIN
    delete from users where id=rid;
    END

    Delete Record

    File Name : index.php

    <?php
    // include database connection file
    require_once'dbconnection.php';
    // Code for record deletion
    if(isset($_REQUEST['del']))
    {
    //Get row id
    $rid=intval($_GET['del']);
    //Qyery for deletion
    $sql =mysqli_query($con,"call sp_delete('$rid')");

    echo "<script>alert('Record deleted');</script>";
    // Code for redirection
    echo "<script>window.location.href='index.php'</script>";
    }
    ?>

    File Name :


    Example :- IN parameters

    DELIMITER //

    CREATE PROCEDURE student_search (IN first_name VARCHAR(64))
    BEGIN
    SELECT * FROM itechxpert where name=first_name;
    END //

    DELIMITER ;

    Example :- OUT parameters

    File Name :

    DELIMITER //

    CREATE PROCEDURE student_count (
    IN first_name VARCHAR(64),
    OUT total INT
    )

    BEGIN
    SELECT COUNT(id) INTO total
    FROM itechxpert WHERE name = first_name ;
    END//

    DELIMITER ;

    File Name :

    CALL student_count('Mahi',@total);
    SELECT @total;





    Previous Next


    Trending Tutorials


    PHP

    PHP

    100+ Tutorials
    oops

    PHP OOPs

    80+ Tutorial
    Codeigniter

    Codeigniter

    100+ Tutorial
    Laravel

    Laravel

    100+ Tutorials
    Node.js

    NODE.JS

    100+ Tutorials
    Javascript

    Javascript

    100+ Tutorials


    Review & Rating

    0.0 / 5

    0 Review

    5
    (0)

    4
    (0)

    3
    (0)

    2
    (0)

    1
    (0)

    Write Review Here


    Most Popular Tutorials

     Important Link