PHP Kishan: How to create Stored Procedures for Insert,Update,Delete,Login in PHP?

Tuesday, 18 August 2015

How to create Stored Procedures for Insert,Update,Delete,Login in PHP?

<?php
STEP - 1
#### Create Insert Data Store Procedure In PHP ##### First Create Table
# Table structure for table `users`
CREATE TABLE IF NOT EXISTS `users` (
`id` int(11) NOT NULL AUTO_INCREMENT,
`name` varchar(50) DEFAULT NULL,
`username` varchar(50) DEFAULT NULL,
PRIMARY KEY (`id`),
UNIQUE KEY `username` (`name`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;
# Dumping data for table `users`
INSERT INTO `users` (`id`, `name`, `username`) VALUES
(1, 'Karan', 'Raj'),
(2, 'Ram', 'Kishan'),
(3, 'Abc', 'Pqr');
#### Insert Record Procedure ####

STEP - 2
# This Run in PHPMyAdmin -> [database name] -> SQL
DELIMITER $$
DROP PROCEDURE IF EXISTS InsertUser $$
CREATE PROCEDURE InsertUser(IN p_id INT(11),IN p_name VARCHAR(50),IN p_username VARCHAR(50))
BEGIN
    INSERT
INTO
   users(id,name,username)
VALUES
   (p_id,p_name,p_username);
END $$
DELIMITER ;

STEP - 3
$host="localhost";
$username="root";
$password="";
$dbname="test";

$con = new PDO("mysql:host=$host;dbname=$dbname",$username,$password) or die(mysql_error());

if($_SERVER["REQUEST_METHOD"] == "POST"){
if($_POST['insert_data'] == "Submit") {
    try {
      $conn = new PDO("mysql:host=localhost;dbname=test",'root','');

      // execute the stored procedure
      $id='';
      $name=ucfirst($_POST['name']);
      $username=ucfirst($_POST['username']);

      $sql = "CALL InsertUser('','$name','$username')";
      $q = $conn->query($sql);
      echo "insert Suucess...!";
    } catch (PDOException $pe) {
      die("Error occurred:" . $pe->getMessage());
    }
}
}
?>
<html>
<title>How to create Stored Procedures for Insert,Update,Delete,Login in PHP?</title>
<div align="center">
<form action="<?php basename($_SERVER['PHP_SELF']); ?>" method="POST">
<table>
    <tr><td>Name : </td><td><input type="text" name="name" required="required" /></td></tr>
    <tr><td>Username : </td><td><input type="text" name="username" required="required"/></td></tr>
    <tr><td colspan="2"><input type="submit" name="insert_data" value="Submit"/></td></tr>
</table>
</form>
</div>
</html>