December 14, 2023 5:02 am

Mysql Stored Procedure in PHP

Are you making stored procedures if not please have a look at this. Stored procedures can help to improve web applications and reduce database requests traffic. This post explains you how to make procedures and decrease database requests.

Database

users table contains username and name.

CREATE TABLE users ( id INT PRIMARY KEY AUTO_INCREMENT,
userID VARCHAR(50) UNIQUE, name VARCHAR(50), );

Result.php (Direct database server access)

Calling SQL statements directly. Here displaying the users content with PHP.

<?php
include('DataBase.php'); // Check code below of the post.
$sql=mysql_query("SELECT userID,name FROM users");
while($row=mysql_fetch_array($sql))
{
echo $row['user'].'--'.$row['name'].'</br>';
}
?>

I had implemented Stored Procedure at huzoorbux.com

How to Create Stored Procedure
You can create stored procedures that run on your database server. Stored Procedure name userIDName(). Just like SQL statements.

DELIMITER // 
CREATE PROCEDURE userIDName() 
SELECT userID,name FROM users;

How to Call Stored Procedure

Results.php (With stored procedures)

<?php
include("DataBase.php");
$sql=mysql_query("CALL users()");
while($row=mysql_fetch_array($sql))
{
echo $row['user'].'--'.$row['name'].'';
}
>

Stored Procedure Input

Simple Way
insert procedure IN – Input , name and datatype.

DELIMITER // 
CREATE PROCEDURE insert(IN userID VARCHAR(50),IN name VARCHAR(50))

INSERT INTO users(userID,name) VALUES (userID,name);

 Nice Way

I suggest you to create stored procedures with following statements.

DELIMITER // 
CREATE PROCEDURE insert(IN userID INT,IN name VARCHAR(40))

BEGIN

SET @userID=userID; 
SET @name=name;

PREPARE STMT FROM 
"INSERT INTO users(userID,name) VALUES (?,?)";

EXECUTE STMT USING @userID,@name; 

END

 Insert.php

Here inserting values into users table with calling insert() procedure.

<?php
include("DataBase.php");
$userID=121;
$name='Huzoor Bux';
$sql=mysql_query($connect,"CALL insert('$username','$name')");
?>

 DataBase.php Database configuration code.

<?php
$mysql_hostname = "localhost";
$mysql_user = "username";
$mysql_password = "password";
$mysql_database = "database";
$bd = mysql_connect($mysql_hostname, $mysql_user, $mysql_password) 
or die("Opps some thing went wrong");
mysql_select_db($mysql_database, $bd) or die("Opps some thing went wrong");
?>

 

Author Huzoor Bux

I am Huzoor Bux from Karachi (Pakistan). I have been working as a PHP Developer from last 5+ years, and its my passion to learn new things and implement them as a practice. Basically I am a PHP developer but now days exploring more in HTML5, CSS and jQuery libraries.


Tutorial Categories:

13 responses to “Mysql Stored Procedure in PHP”

  1. SkyM says:

    can’t call multiple stored procedure.

  2. SkyM says:

    ex
    <?php
    include('DataBase.php'); // Check code below of the post.
    $

    sql=mysql_query("SELECT userID,name FROM users");
    while($row=mysql_fetch_array($sql))
    {
    echo $row['user'].'–'.$row['name'].'’;
    }

    $sql=mysql_query(“SELECT userID,name FROM users2″);
    while($row=mysql_fetch_array($sql))
    {
    echo $row[‘user’].’–‘.$row[‘name’].”;
    }
    ?>

  3. moses says:

    how can i write a unit test case for this

  4. nonu says:

    don’t copy to 9lessons.info

  5. mohicanin says:

    you made procedure userIDName() and then you call it using mysql_query(“CALL users()”)….
    srsly is it to hard to make such simple tutorial without such ridiculous mistakes?

  6. julie says:

    how about delete and update by ID?

  7. adar says:

    userid or username, wkwkwk

  8. Jyo says:

    wht’s this??

  9. Rasmi Ranjan Swain says:

    Can you show the example with mysqli instead of mysql

  10. Deep says:

    CREATE PROCEDURE insert(IN userID INT,IN name VARCHAR(40))
    in this line can you please let me know , what is meaning of ‘ IN ‘

  11. nani says:

    hii a this a simple code

    but when i run it it is giving as Resource id#3

Leave a Reply

Your email address will not be published. Required fields are marked *