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.
CREATE TABLE users
(
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.
<?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.
DELIMITER //
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)
<?php
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.
DELIMITER //
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.
DELIMITER //
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;
ENDinsert.php
Here inserting values into users table with calling insert() procedure.
<?php
include(“newdb.php”);
$username=’Venugopal24′;
$name=’venugopal’;
$sql=mysqli_query($connect,
“CALL insert(‘$username’,’$name’)“);
?>
