Stored Procedure Tutorial in PHP:
Are you writing Stored Procedures if not please take a look at this post. Stored procedures can help to improve web application performance and reduce database access traffic. In this post I want to explain how to create and call the stored procedures from database server.
Database:
users table contains username and name.
(
id INT PRIMARY KEY AUTO_INCREMENT,
username VARCHAR(50) UNIQUE,
name VARCHAR(50),
);
Results.php (Direct database server access)
Calling SQL statements directly. Here displaying the users content with PHP.
include(‘db.php‘); // Check code below of the post.
$sql=mysql_query(“SELECT user,name FROM users”);
while($row=mysql_fetch_array($sql))
{
echo $row[‘user’].’–‘.$row[‘name’].’</br>‘;
}
?>
How to Create Stored Procedure
You can create stored procedures that run on your database server. Stored Procedure name users(). Just like SQL statements.
CREATE PROCEDURE users()
SELECT username,name FROM users;
How to Call Stored Procedure
Results.php (With stored procedures)
Notice that here mysqli(MySQL Improved Extension)
include(“newdb.php”);
$sql=mysqli_query($connect,”CALL users()“);
while($row=mysqli_fetch_array(sql))
{
echo $row[‘user’].’–‘.$row[‘name’].”;
}
Stored Procedure Input OR Insert
Normal Way
insert procedure IN – Input , name and datatype.
CREATE PROCEDURE insert(IN username VARCHAR(50),IN name VARCHAR(50))
INSERT INTO users(username,name) VALUES (username,name);
Better Way
I recommend you to create stored procedures following statements.
CREATE PROCEDURE insert(IN username VARCHAR(50),IN name VARCHAR(50))
BEGIN
SET @username=username;
SET @name=name;
PREPARE STMT FROM
“INSERT INTO users(username,name) VALUES (?,?)”;
EXECUTE STMT USING @username,@name;
END
insert.php
Here inserting values into users table with calling insert() procedure.
include(“newdb.php”);
$username=’Venugopal24′;
$name=’venugopal’;
$sql=mysqli_query($connect,
“CALL insert(‘$username’,’$name’)“);
?>
