Stored Procedure SQL Crud oparetion in PHP (WORKBENCH)

 

Stored Procedure Create: 
Workbench Code:

CREATE DEFINER=`root`@`localhost` PROCEDURE `crud_oparation`(`ids` INT(11),`username` VARCHAR(100),`address` VARCHAR(100),`phone` VARCHAR(100),`StatementType` VARCHAR(20))

BEGIN

IF StatementType = 'insert' THEN

    BEGIN

INSERT INTO store_proce_demo

(id,

                        username,

                        address,

                        phone) 

VALUES 

(ids,

                        username,

                        address,

                        phone);

END;

    end if;

IF StatementType = 'select' THEN

BEGIN

SELECT * FROM store_proce_demo;

END;

END IF;

        IF StatementType = 'update' THEN

BEGIN 

UPDATE store_proce_demo SET 

username = username

WHERE id = ids;

END;

END IF;

        IF StatementType = 'delete' THEN

BEGIN 

DELETE FROM store_proce_demo 

                WHERE id = ids;

END;

END IF;

END


PHP CODE: 


<?php 

connection code:

$conn = mysqli_connect('localhost','root','','workbench_demo');

insert code:

if (isset($_REQUEST['submit_data'])) {

    $username = $_REQUEST['username'];

    $address = $_REQUEST['address'];

    $phone = $_REQUEST['phone'];

    

    $query = mysqli_query($conn, "CALL crud_oparation('','$username','$address','$phone','insert')");

        if ($query) {

            echo "inserted";

        }else{

            echo "not insert";

        }

}


update code:

if (isset($_REQUEST['update_data'])) {

    $hidden = $_REQUEST['id'];

    $username = $_REQUEST['username'];

    $address = $_REQUEST['address'];

    $phone = $_REQUEST['phone'];

    $update_query = mysqli_query($conn, "CALL crud_oparation('$hidden','$username','$address','$phone','update')");


        if ($update_query) {

            echo "update";

        }else{

            echo "not update";

        }

}

?>



<!DOCTYPE html>

<html lang="en">

<head>

    <meta charset="UTF-8">

    <meta http-equiv="X-UA-Compatible" content="IE=edge">

    <meta name="viewport" content="width=device-width, initial-scale=1.0">

    <title>Document</title>

</head>

<body>

    <h1>Stored procedure Crud Oparetion</h1>

    <form method="POST">

        <input type="hidden" name="id" id="id" placeholder="Enter UserName"><br><br>

        <input type="text" name="username" id="username" placeholder="Enter UserName"><br><br>

        <input type="text" name="address" id="address" placeholder="Enter Address"><br><br>

        <input type="text" name="phone" id="phone" placeholder="Enter Phone"><br><br>

        <input type="submit" name="submit_data" value="Submit">

        <input type="submit" name="update_data" value="Update">

    </form><br><br>


    <table border="1" cellspacing="0">

        <tr>

            <th>Sl No</th>

            <th>Name</th>

            <th>Address</th>

            <th>Phone</th>

            <th>Action</th>

        </tr>

        <?php 

        

        $query = mysqli_query($conn, "CALL crud_oparation('','username','address','phone','select')");

        $count = 1;

        while ($row = mysqli_fetch_array($query)) {

            $count++;

        ?>

        <tr>

            <td><?php echo $count?></td>

            <td><?php echo $row['username']?></td>

            <td><?php echo $row['address']?></td>

            <td><?php echo $row['phone']?></td>

            <td>

                

                <input type="submit" onclick="edit_data('<?php echo $row['id']?>','<?php echo $row['username']?>','<?php echo $row['address']?>','<?php echo $row['phone']?>')" value="view">

                <a href="delete.php?delete_data=<?php echo $row['id']?>"><button>DELETE</button></a>

                

            </td>

        </tr>

        <?php } ?>

    </table>

    <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.6.0/jquery.min.js"></script>

    <script>

View in form field jQuery:

        function edit_data(id,username,address,phone){

            $('#id').val(id);

            $('#username').val(username);

            $('#address').val(address);

            $('#phone').val(phone);

        }

    </script>

</body>

</html>


Comments

Post a Comment

Popular posts from this blog

C.R.U.D Oparetion in (PHP Code)

C.R.U.D Oparetion   index.php <?php include "conn.php"; if (isset($_POST["usersubmit"]))  {     $vrname = $_POST["username"];     $vremployee = $_POST["useremployee"];     $vrsalary = $_POST["usersalary"];     $vrgender = $_POST["gender"];     //testing sucessfully complete     // echo $vrname;     // echo $vremployee;     // echo $vrsalary;     // echo $vrgender;     // exit();     $inserting = "INSERT INTO dbemployeelist(dbname, dbemployee, dbsalary, dbgender) VALUES('$vrname', '$vremployee', '$vrsalary', '$vrgender')";     $connecting = mysqli_query($conn,$inserting); } ?> <!DOCTYPE html> <html lang="en"> <head>     <title>Document</title>     <style>         form{             text-align:center;             padding:2...
Read more

Search Dropdown - jQuery

Image
<select name="vehicle_no" id="vehicle_no" class="form-control select" >   <option value="" >Select Vehicle</option>      <?php //echo $vchVehicleNumber;exit;?>           @foreach($vehiclesData as $data)              <option {{(isset($vchVehicleNumber) && $vchVehicleNumber == $data['vehicleno'])?'selected':''}} value="{{ $data['vehicleno'] }}" >{{ $data['vehicleno'] }}</option>              @endforeach </select> // jQuery:- you have to add CDN of select2 jquery $("#vehicle_no").select2({   minimumResultsForSearch: Infinity }); $('#vehicle_no').select2({    minimumInputLength: 0  });
Read more

Ajax Crud Oparetion (Only Insert Code "create") | PHP | Jquery | Ajax | CODE

Image
 index.php(Frontend & ajax) <!DOCTYPE html> <html lang="en"> <head>     <meta charset="UTF-8">     <meta http-equiv="X-UA-Compatible" content="IE=edge">     <meta name="viewport" content="width=device-width, initial-scale=1.0">     <title>Document</title> </head> <body>     <form id="aj">     Name :<input type="text" name="u_name">     Mobile :<input type="text" name="u_mob">     <button type="button" onclick="user_submit();">SEND</button >     <script src="https://ajax.googleapis.com/ajax/libs/jquery/3.5.1/jquery.min.js"></script>     <script>         function user_submit()          {             var send = new FormData(aj);             // console.log(send);   ...
Read more