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.

1 2	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.

1 2 3 4 5 6 7 8

<?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.

1 2 3

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

How to Call Stored Procedure

Results.php (With stored procedures)

PHP

1 2 3 4 5 6 7 8

<?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.

1 2 3 4

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.

1 2 3 4 5 6 7 8 9 10 11 12 13 14

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.

1 2 3 4 5 6

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

 DataBase.php Database configuration code.

PHP

1 2 3 4 5 6 7 8 9

<?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"); ?>

 Feel free to ask your question :-)