The purpose of the question is some experience person told me that if you are using more then one queries continuously in php it is not a good practice use the procedure for defining all the queries and use it in your program.
Is it right? if yes
How to use a procedure in php?
e.g.
INSERT .........
SELECT........
//use the result............
DELETE........
UPDATE........
9 3867 @chathura86
How to use in PHP?
Give any practical example.
@iskhan
some experience person told me that if you are using more then one queries continuously in php it is not a good practice use the procedure for defining all the queries and use it in your program.
that doesn’t mean you should use an SQL Procedure. a simple Prepared Statement is more effective (and way more secure)
Atli 5,058
Expert 4TB @iskhan
The link he gave you included several "practical" examples.
A MySQL procedure is not created in PHP. You create it inside MySQL, and CALL it, like you would a function. From PHP's perspective, you are just executing a query.
I would advice you to use the Improved MySQL Extension, rather than the old MySQL extension, to avoid out-of-sync errors.
@Atli
Your mentioned link have following types of example: - CREATE PROCEDURE dorepeat(p1 INT)
-
BEGIN
-
SET @x = 0;
-
REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
-
END
or - CREATE FUNCTION hello (s CHAR(20))
-
RETURNS CHAR(50) DETERMINISTIC
-
RETURN CONCAT('Hello, ',s,'!');
or - CREATE FUNCTION f1(i INT)
-
RETURNS INT
-
DETERMINISTIC
-
READS SQL DATA
-
BEGIN
-
RETURN i;
-
END;
or - CREATE FUNCTION f2()
-
RETURNS CHAR(36) CHARACTER SET utf8
-
BEGIN
-
RETURN UUID();
-
END;
I can't learn any thing from this types of examples, how to define my SELECT, INSERT, UPDATE or DELETE queries in procedure and how to use it in php.
Atli 5,058
Expert 4TB
The first code snippet you posted would be pretty much exactly what you need, except that you would define your own queries instead of the SET and REPEAT clauses.
A procedure is created by issuing a CREATE PROCEDURE, followed by either a single statement, or a BEGIN ... END block with multiple statements.
That is, to issue multiple statements, the BEGIN ... END block would look like: - BEGIN
-
CREATE ...;
-
INSERT ...;
-
UPDATE ...;
-
SELECT ...;
-
DELETE ...;
-
END
And once you have created the procedure, you call it by issuing a CALL command. - CALL procedureName('param1', 'param2', 'etc...');
Keep in mind, though, because this issues multiple commands, the old MySQL functions in PHP can not deal with both of the sets returned. You need to mysqli extension for that, or you risk getting a out-of-sync error.
@Atli
OK. If my procedure have 5 select statements and every one returns different no. rows then how can I use them separately in my PHP?
Atli 5,058
Expert 4TB
You would have to use the mysqli functions to call the procedure, and then go through the result sets one at a time.
A CALL command executed via the mysqli::query method works pretty much the same as multiple SELECT queries executed via the mysqli::multi_query method. That is; it returns a group of result sets, rather than the one set you would expect from a normal mysql or mysqli query call.
If you look at the first example in the mysqli::multi_query method manual entry, it shows how you can iterate over all the results sets. The same code could be applied to a procedure call.
Here is the examples I need I found it and I share it for everyone. <Edit: The following was written by Joey Rivera at http://www.joeyrivera.com/ -- Atli >
Stored procedures using the following database extensions:
* MySQL - http://us.php.net/manual/en/book.mysql.php
* MySQLi - http://uk2.php.net/manual/en/class.mysqli.php
* PDO - http://us.php.net/manual/en/class.pdo.php
First we need to setup our enviroment which consists of a new database with one table and two stored procedures. In your db tool of choice create a new database named test. After you create the new database, make sure to add a user called example with password example to the database and give it read access.
Now create the table users: - DROP TABLE IF EXISTS `test`.`users`;
-
CREATE TABLE `test`.`users` (
-
`users_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
-
`first_name` VARCHAR(100) NOT NULL,
-
`last_name` VARCHAR(100) NOT NULL,
-
PRIMARY KEY (`users_id`)
-
) ENGINE=INNODB DEFAULT CHARSET=latin1;
Before we create the stored procedures, lets put some dummy data in the users table. To do that just run the following query: - INSERT INTO `test`.`users` VALUES (NULL, ‘Joey’, ‘Rivera’), (NULL, ‘John’, ‘Doe’);
Next create the first stored procedure get_user: - DELIMITER $$
-
DROP PROCEDURE IF EXISTS `test`.`get_user`$$
-
CREATE PROCEDURE `test`.`get_user`
-
(
-
IN userId INT,
-
OUT firstName VARCHAR(100),
-
OUT lastName VARCHAR(100)
-
)
-
BEGIN
-
SELECT first_name, last_name
-
INTO firstName, lastName
-
FROM users
-
WHERE users_id = userId;
-
END $$
-
DELIMITER ;
Finally create the second and last stored procedure get_users: - DELIMITER $$
-
DROP PROCEDURE IF EXISTS `test`.`get_users`$$
-
CREATE PROCEDURE `test`.`get_users`()
-
BEGIN
-
SELECT *
-
FROM users;
-
END $$
-
DELIMITER ;
If you understand the sql above, skip this section. The first script we ran to create a database is pretty self explanitory. The second script will delete the table users if it’s already in your database then it will recreate it. The table will consist of three fields: users_id, first_name, and last_name. The insert script will create two users: ‘Joey Rivera’ and ‘John Doe’.
If stored procedures are new to you, don’t worry. They aren’t that complicated once you start playing with them. When looking at the code for the first stored procedure, drop procedure works the same way as dropping a table. First you want to check if the stored procedure is there and deleted before you recreate it. Create does just that, create the stored procedure in the database. get_user has three parameters: userId, firstName, and lastName. IN means when this stored procedure is called, this variable should be passed with a value. OUT means after the stored procedure executes, it will set the OUT variables with a value that can then be retrieved. You can also have INOUT variables but we don’t need them for this example.
The blulk of the code for the stored procedure goes in the BEGIN to END block. get_user is selecting the first and last name fields from the table users where the user id is equal to the userId variable being passed in. The other thing happening here is the two OUT variables are getting the values retrieved from the select statement. Variable firstName is set to the field first_name and lastName is being set to last_name. That’s it for get_user. get_users doesn’t have any IN nor OUT variables. When that stored procedure is executed it will return a recordset instead of variables.
Now that we have our environment set, we are ready to start our tests. Depending on what you are trying to achieve, you may be using mysql, mysqli, or PDO. I’m going to run the same tests with all three to show you the difference as well as the limitation of mysql compared to mysqli and PDO. One of the tests I’ll be running doesn’t work with mysql while all the tests work with mysqli and PDO.
The three tests will be:
1. A simple select statement
2. Calling stored procedure passing IN variable and retrieve OUT variables – get_user
3. Calling stored procedure with no parameters and returns a recordset – get_users
Below is the code to run all three tests with each of the database extensions: - <?php
-
// MYSQL
-
$mysql = mysql_connect(‘localhost’, ‘example’, ‘example’);
-
mysql_select_db(‘test’, $mysql);
-
-
print ‘<h3>MYSQL: simple select</h3>’;
-
$rs = mysql_query( ‘SELECT * FROM users;’ );
-
while($row = mysql_fetch_assoc($rs))
-
{
-
debug($row);
-
}
-
-
print ‘<h3>MYSQL: calling sp with out variables</h3>’;
-
$rs = mysql_query( ‘CALL get_user(1, @first, @last)’ );
-
$rs = mysql_query( ‘SELECT @first, @last’ );
-
while($row = mysql_fetch_assoc($rs))
-
{
-
debug($row);
-
}
-
-
print ‘<h3>MYSQL: calling sp returning a recordset – doesn\’t work</h3>’;
-
$rs = mysql_query( ‘CALL get_users()’ );
-
while($row = mysql_fetch_assoc($rs))
-
{
-
debug($row);
-
}
-
-
// MYSQLI
-
$mysqli = new mysqli(‘localhost’, ‘example’, ‘example’, ‘test’);
-
-
print ‘<h3>MYSQLI: simple select</h3>’;
-
$rs = $mysqli->query( ‘SELECT * FROM users;’ );
-
while($row = $rs->fetch_object())
-
{
-
debug($row);
-
}
-
-
print ‘<h3>MYSQLI: calling sp with out variables</h3>’;
-
$rs = $mysqli->query( ‘CALL get_user(1, @first, @last)’ );
-
$rs = $mysqli->query( ‘SELECT @first, @last’ );
-
while($row = $rs->fetch_object())
-
{
-
debug($row);
-
}
-
-
print ‘<h3>MYSQLI: calling sp returning a recordset</h3>’;
-
$rs = $mysqli->query( ‘CALL get_users()’ );
-
while($row = $rs->fetch_object())
-
{
-
debug($row);
-
}
-
-
// PDO
-
$pdo = new PDO(‘mysql:dbname=test;host=127.0.0.1′, ‘example’, ‘example’);
-
-
print ‘<h3>PDO: simple select</h3>’;
-
foreach($pdo->query( ‘SELECT * FROM users;’ ) as $row)
-
{
-
debug($row);
-
}
-
-
print ‘<h3>PDO: calling sp with out variables</h3>’;
-
$pdo->query( ‘CALL get_user(1, @first, @last)’ );
-
foreach($pdo->query( ‘SELECT @first, @last’ ) as $row)
-
{
-
debug($row);
-
}
-
-
print ‘<h3>PDO: calling sp returning a recordset</h3>’;
-
foreach($pdo->query( ‘CALL get_users()’ ) as $row)
-
{
-
debug($row);
-
}
-
-
function debug($o)
-
{
-
print ‘<pre>’;
-
print_r($o);
-
print ‘</pre>’;
-
}
-
?>
When you run this code you get the following results:
As you can see from the results above, mysql could not get the recordset returned by the stored procedure while mysqli and PDO could. After some more research, some people mentioned (Bob’s World, php.net) that by adding ‘false,65536′ to the end of the mysql_connect line, mysql could then get recordsets from stored procedures. I tried this and in fact it does work. So by changing - $mysql = mysql_connect(‘localhost’, ‘example’, ‘example’);
to: - $mysql = mysql_connect(‘localhost’, ‘example’, ‘example’,false,65536);
all the different database extensions work on all tests. So in the end, it seems all of these can work with stored procedures just as well.
Sign in to post your reply or Sign up for a free account.
Similar topics
by: James E Koehler |
last post by:
I can't get the WHILE statement to work in MySQL.
The version of MySQL that I am using is:
Ver 12.16 Distrib 4.0.6-gamma, for Win95/Win98 (i32)
running on Windows MX.
Here is the relevant...
|
by: Michael C# |
last post by:
Is it possible to create Dynamic SQL queries in MySQL, like in SQL Server?
i.e., the EXECUTE command or sp_executesql stored procedure in SQL Server.
TIA
|
by: IamtheEvster |
last post by:
Hi All,
I am currently using PHP 5 and MySQL 5, both on Fedora Core 5. I am
unable to call a MySQL stored procedure that returns output parameters
using mysql, mysqli, or PDO. I'm having a...
|
by: bshumsky06 |
last post by:
Hi,
I am trying to create stored procedures in MySQL. Does anyone have a
suggestion on a good IDE to use. I have been working with the MySQL
Query Browser but it generates errors very often and...
|
by: objectmodelol |
last post by:
I just switched from MS SQL 2000/2005 to MySql.
What's wrong with this stored procedure:
DELIMITER $$
DROP PROCEDURE IF EXISTS `listing`.`SaveUser` $$
CREATE DEFINER=`root`@`localhost`...
|
by: Ted |
last post by:
In MS SQL I used the following to create a stored procedure.
USE AdventureWorks;
GO
IF OBJECT_ID ( 'HumanResources.usp_My_Search', 'P' ) IS NOT NULL
DROP PROCEDURE HumanResources.usp_My_Search;...
|
by: eholz1 |
last post by:
Hello PHP group,
Could someone help me out? I have PHP 5.2, Apache 2.0, and MySQL 5.0
running on Linux (Redhat Fedora Core 6).
All that works fine. I would like to be able to "call" a stored...
|
by: boyindie86 |
last post by:
Hi Forum,
I am having some really iritating problems with an simple input page which should pass parameters into a stored procedure from the text boxes first and last name.
I keep getting the...
|
by: preejith |
last post by:
Error Code : 1329, No data - zero rows fetched, selected, or processed. MYSQL
I am getting the following error while running a stored procedure in mysql5.0
Error Code : 1329
No data - zero rows...
|
by: ssnaik84 |
last post by:
Hi Guys,
Last year I got a chance to work with R&D team, which was working on DB scripts conversion..
Though there is migration tool available, it converts only tables and constraints..
Rest of...
|
by: DolphinDB |
last post by:
The formulas of 101 quantitative trading alphas used by WorldQuant were presented in the paper 101 Formulaic Alphas. However, some formulas are complex, leading to challenges in calculation.
Take...
|
by: DolphinDB |
last post by:
Tired of spending countless mintues downsampling your data? Look no further!
In this article, you’ll learn how to efficiently downsample 6.48 billion high-frequency records to 61 million...
|
by: ryjfgjl |
last post by:
ExcelToDatabase: batch import excel into database automatically...
|
by: isladogs |
last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM).
In this month's session, we are pleased to welcome back...
|
by: Vimpel783 |
last post by:
Hello!
Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
|
by: ArrayDB |
last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
|
by: PapaRatzi |
last post by:
Hello,
I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
|
by: Shællîpôpï 09 |
last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
|
by: af34tf |
last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
| |