PHP CRUD Operation using Stored Procedure
In previous tutorial, we learned about stored procedure in PHP. In this tutorial, we will learn CRURD operation using Stored Procedure.
File Structure for CRUD Operation
dbconfig.php- used for database connection
tblusers.sql– Contain the structure of the database table
insert.php- used for add a record in the database
index.php– Used for read the record from database .
update.php– Used for updating a record.
Step 1– Create a database
Open browser type http://localhost/phpmyadmin, create a database named ‘spcruddb’ . After creating database run the SQL script or import the SQL file.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 |
-- Table structure for table `tblusers` -- CREATE TABLE IF NOT EXISTS `tblusers` ( `id` int(11) NOT NULL, `FirstName` varchar(150) NOT NULL, `LastName` varchar(150) NOT NULL, `EmailId` varchar(120) NOT NULL, `ContactNumber` char(11) NOT NULL, `Address` varchar(255) NOT NULL, `PostingDate` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP ) ENGINE=InnoDB DEFAULT CHARSET=latin1; -- Indexes for table `tblusers` -- ALTER TABLE `tblusers` ADD PRIMARY KEY (`id`); -- AUTO_INCREMENT for dumped tables -- AUTO_INCREMENT for table `tblusers` -- ALTER TABLE `tblusers` MODIFY `id` int(11) NOT NULL AUTO_INCREMENT; |
Step 2– Create a database connection file (dbconfig.php)
create a dbconfig.php file and place the code given below :
|
1 2 3 4 5 6 7 8 9 10 11 12 |
<?php define('DB_SERVER','localhost'); define('DB_USER','root'); define('DB_PASS' ,''); define('DB_NAME', 'spcruddb'); $con = mysqli_connect(DB_SERVER,DB_USER,DB_PASS,DB_NAME); // Check connection if (mysqli_connect_errno()) { echo "Failed to connect to MySQL: " . mysqli_connect_error(); } ?> |
Step 3 : Insert a record in database
Step3.1 Create an insert.php file for insert record in the database. This page includes an HTML form with an input field where we can fill the data.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 |
<!DOCTYPE html> <html lang="en"> <head> <meta charset="utf-8"> <title>PHP CURD Operation using Stored Procedure </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>Insert Record | PHP CRUD Operations using Stored Procedure</h3> <hr /> </div> </div> <form name="insertrecord" 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> |
Step 3.2: Create a stored procedure for data insertion “sp_insert”
|
1 2 3 4 5 6 7 8 9 10 11 12 |
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 tblusers(FirstName,LastName,EmailId,ContactNumber,Address) value(fname,lname,emailid,cntnumber,address); END$$ DELIMITER ; |
Step 3.3: PHP Code for Data Insertion
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 |
<?php // include database connection file require_once'dbconfig.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>"; } } ?> |
Here is the full code that we have written for data insertion (insert.php)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 |
<?php // include database connection file require_once'dbconfig.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>PHP CURD Operation using Stored Procedure </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>Insert Record | PHP CRUD Operations using Stored Procedure</h3> <hr /> </div> </div> <form name="insertrecord" 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> |
Step 4 : Read record from the database
Create a stored procedure (sp_read) for read data
|
1 2 3 |
BEGIN select * from tblusers; END |
Now, create a index.php file for read all records from database.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 |
<?php // include database connection file require_once'dbconfig.php'; ?> <!DOCTYPE html> <html lang="en"> <head> <meta charset="utf-8"> <title>PHP CRUD Operations using Stored Procedure </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 type="text/css"> </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>PHP CRUD Operations using Stored Procedure</h3> <hr /> <a href="insert.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> |
Step 5 : Update record in the database
Step 5.1 : Create a stored procedure (sp_sp_readarow) for read data of a particular row
|
1 2 3 |
BEGIN select * from tblusers where id=rid; END |
Step 5.2 : Get data in HTML From
create update.php file. For updating a record we have to get the row id of that record and store in $id. We access the $_GET[‘id’] variable to do it.
Code for gets a record based on the given id. Through this way, we can get data autofill-data in HTML Form.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 |
<?php // include database connection file require_once'dbconfig.php'; ?> <!DOCTYPE html> <html lang="en"> <head> <meta charset="utf-8"> <title>PHP CURD Operation using Stored Procedure </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($con, "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 |
Step 5.3 : Create a stored procedure (sp_update) for for update a record
|
1 2 3 |
BEGIN update tblusers set FirstName=fname,LastName=lname,EmailId=emailid,ContactNumber=cntnumber,Address=address where id=rid; END |
Step 5.4 : Code for update the record
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 |
<?php // include database connection file require_once'dbconfig.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>"; } ?> |
Here is the full code that we have written for data updation (update.php)
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 |
<?php // include database connection file require_once'dbconfig.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>PHP CURD Operation using Stored Procedure </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($con, "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 |
Step 6 : Delete a record from the database
Step 6.1: create a stored procedure(sp_delete) for delete a particular record
|
1 2 3 |
BEGIN delete from tblusers where id=rid; END |
Step 6.1: PHP Code for data deletion
Place this code in the index.php file.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 |
<?php // include database connection file require_once'dbconfig.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>"; } ?> |
View Demo———————————————————
Source Code (PHP CRUD Operation using Stored Procedure)
Size: 6.47 KB
Version: V 1.0
