473,320 Members | 1,841 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

How to use MySQL procedure in PHP?

20
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........
Apr 16 '10 #1
9 3867
chathura86
227 100+
you have to create a procedure in your mysql database

http://dev.mysql.com/tech-resources/...toredproc.html

you can use mysql_query() to call the procedure

Regards
Apr 16 '10 #2
iskhan
20
@chathura86
How to use in PHP?
Give any practical example.
Apr 16 '10 #3
Dormilich
8,658 Expert Mod 8TB
@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)
Apr 16 '10 #4
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.
Apr 16 '10 #5
iskhan
20
@Atli
Your mentioned link have following types of example:
Expand|Select|Wrap|Line Numbers
  1. CREATE PROCEDURE dorepeat(p1 INT)
  2. BEGIN
  3.   SET @x = 0;
  4.   REPEAT SET @x = @x + 1; UNTIL @x > p1 END REPEAT;
  5. END
or
Expand|Select|Wrap|Line Numbers
  1. CREATE FUNCTION hello (s CHAR(20))
  2. RETURNS CHAR(50) DETERMINISTIC
  3. RETURN CONCAT('Hello, ',s,'!');
or
Expand|Select|Wrap|Line Numbers
  1. CREATE FUNCTION f1(i INT)
  2. RETURNS INT
  3. DETERMINISTIC
  4. READS SQL DATA
  5. BEGIN
  6.   RETURN i;
  7. END;
or
Expand|Select|Wrap|Line Numbers
  1. CREATE FUNCTION f2()
  2. RETURNS CHAR(36) CHARACTER SET utf8
  3. BEGIN
  4.   RETURN UUID();
  5. 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.
Apr 17 '10 #6
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:
Expand|Select|Wrap|Line Numbers
  1. BEGIN 
  2.     CREATE ...;
  3.     INSERT ...;
  4.     UPDATE ...;
  5.     SELECT ...;
  6.     DELETE ...;
  7. END
And once you have created the procedure, you call it by issuing a CALL command.
Expand|Select|Wrap|Line Numbers
  1. 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.
Apr 17 '10 #7
iskhan
20
@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?
Apr 17 '10 #8
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.
Apr 17 '10 #9
iskhan
20
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.
Expand|Select|Wrap|Line Numbers
  1. CREATE DATABASE `test`;
Now create the table users:
Expand|Select|Wrap|Line Numbers
  1. DROP TABLE IF EXISTS `test`.`users`;
  2. CREATE TABLE  `test`.`users` (
  3. `users_id` INT(10) UNSIGNED NOT NULL AUTO_INCREMENT,
  4. `first_name` VARCHAR(100) NOT NULL,
  5. `last_name` VARCHAR(100) NOT NULL,
  6. PRIMARY KEY  (`users_id`)
  7. ) 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:
Expand|Select|Wrap|Line Numbers
  1. INSERT INTO `test`.`users` VALUES (NULL, ‘Joey’, ‘Rivera’), (NULL, ‘John’, ‘Doe’);
Next create the first stored procedure get_user:
Expand|Select|Wrap|Line Numbers
  1. DELIMITER $$
  2. DROP PROCEDURE IF EXISTS `test`.`get_user`$$
  3. CREATE PROCEDURE  `test`.`get_user`
  4. (
  5. IN userId INT,
  6. OUT firstName VARCHAR(100),
  7. OUT lastName VARCHAR(100)
  8. )
  9. BEGIN
  10. SELECT first_name, last_name
  11. INTO firstName, lastName
  12. FROM users
  13. WHERE users_id = userId;
  14. END $$
  15. DELIMITER ;
Finally create the second and last stored procedure get_users:
Expand|Select|Wrap|Line Numbers
  1. DELIMITER $$
  2. DROP PROCEDURE IF EXISTS `test`.`get_users`$$
  3. CREATE PROCEDURE  `test`.`get_users`()
  4. BEGIN
  5. SELECT *
  6. FROM users;
  7. END $$
  8. 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:
Expand|Select|Wrap|Line Numbers
  1. <?php
  2. // MYSQL
  3. $mysql = mysql_connect(‘localhost’, ‘example’, ‘example’);
  4. mysql_select_db(‘test’, $mysql);
  5.  
  6. print ‘<h3>MYSQL: simple select</h3>’;
  7. $rs = mysql_query( ‘SELECT * FROM users;’ );
  8. while($row = mysql_fetch_assoc($rs))
  9. {
  10. debug($row);
  11. }
  12.  
  13. print ‘<h3>MYSQL: calling sp with out variables</h3>’;
  14. $rs = mysql_query( ‘CALL get_user(1, @first, @last)’ );
  15. $rs = mysql_query( ‘SELECT @first, @last’ );
  16. while($row = mysql_fetch_assoc($rs))
  17. {
  18. debug($row);
  19. }
  20.  
  21. print ‘<h3>MYSQL: calling sp returning a recordset – doesn\’t work</h3>’;
  22. $rs = mysql_query( ‘CALL get_users()’ );
  23. while($row = mysql_fetch_assoc($rs))
  24. {
  25. debug($row);
  26. }
  27.  
  28. // MYSQLI
  29. $mysqli = new mysqli(‘localhost’, ‘example’, ‘example’, ‘test’);
  30.  
  31. print ‘<h3>MYSQLI: simple select</h3>’;
  32. $rs = $mysqli->query( ‘SELECT * FROM users;’ );
  33. while($row = $rs->fetch_object())
  34. {
  35. debug($row);
  36. }
  37.  
  38. print ‘<h3>MYSQLI: calling sp with out variables</h3>’;
  39. $rs = $mysqli->query( ‘CALL get_user(1, @first, @last)’ );
  40. $rs = $mysqli->query( ‘SELECT @first, @last’ );
  41. while($row = $rs->fetch_object())
  42. {
  43. debug($row);
  44. }
  45.  
  46. print ‘<h3>MYSQLI: calling sp returning a recordset</h3>’;
  47. $rs = $mysqli->query( ‘CALL get_users()’ );
  48. while($row = $rs->fetch_object())
  49. {
  50. debug($row);
  51. }
  52.  
  53. // PDO
  54. $pdo = new PDO(‘mysql:dbname=test;host=127.0.0.1′, ‘example’, ‘example’);
  55.  
  56. print ‘<h3>PDO: simple select</h3>’;
  57. foreach($pdo->query( ‘SELECT * FROM users;’ ) as $row)
  58. {
  59. debug($row);
  60. }
  61.  
  62. print ‘<h3>PDO: calling sp with out variables</h3>’;
  63. $pdo->query( ‘CALL get_user(1, @first, @last)’ );
  64. foreach($pdo->query( ‘SELECT @first, @last’ ) as $row)
  65. {
  66. debug($row);
  67. }
  68.  
  69. print ‘<h3>PDO: calling sp returning a recordset</h3>’;
  70. foreach($pdo->query( ‘CALL get_users()’ ) as $row)
  71. {
  72. debug($row);
  73. }
  74.  
  75. function debug($o)
  76. {
  77. print ‘<pre>’;
  78. print_r($o);
  79. print ‘</pre>’;
  80. }
  81. ?>

When you run this code you get the following results:
Expand|Select|Wrap|Line Numbers
  1. MYSQL: simple select
  2. Array
  3. (
  4.     [users_id] => 1
  5.     [first_name] => Joey
  6.     [last_name] => Rivera
  7. )
  8. Array
  9. (
  10.     [users_id] => 2
  11.     [first_name] => John
  12.     [last_name] => Doe
  13. )
  14.  
  15. MYSQL: calling sp with out variables
  16. Array
  17. (
  18.     [@first] => Joey
  19.     [@last] => Rivera
  20. )
  21.  
  22. MYSQL: calling sp returning a recordset – doesn‘t work
  23. Warning: mysql_fetch_assoc(): supplied argument is not a valid MySQL result resource in ***test.php on line 24
  24.  
  25. MYSQLI: simple select
  26. stdClass Object
  27. (
  28.     [users_id] => 1
  29.     [first_name] => Joey
  30.     [last_name] => Rivera
  31. )
  32. stdClass Object
  33. (
  34.     [users_id] => 2
  35.     [first_name] => John
  36.     [last_name] => Doe
  37. )
  38.  
  39. MYSQLI: calling sp with out variables
  40. stdClass Object
  41. (
  42.     [@first] => Joey
  43.     [@last] => Rivera
  44. )
  45.  
  46. MYSQLI: calling sp returning a recordset
  47. stdClass Object
  48. (
  49.     [users_id] => 1
  50.     [first_name] => Joey
  51.     [last_name] => Rivera
  52. )
  53. stdClass Object
  54. (
  55.     [users_id] => 2
  56.     [first_name] => John
  57.     [last_name] => Doe
  58. )
  59.  
  60. PDO: simple select
  61. Array
  62. (
  63.     [users_id] => 1
  64.     [0] => 1
  65.     [first_name] => Joey
  66.     [1] => Joey
  67.     [last_name] => Rivera
  68.     [2] => Rivera
  69. )
  70. Array
  71. (
  72.     [users_id] => 2
  73.     [0] => 2
  74.     [first_name] => John
  75.     [1] => John
  76.     [last_name] => Doe
  77.     [2] => Doe
  78. )
  79.  
  80. PDO: calling sp with out variables
  81. Array
  82. (
  83.     [@first] => Joey
  84.     [0] => Joey
  85.     [@last] => Rivera
  86.     [1] => Rivera
  87. )
  88.  
  89. PDO: calling sp returning a recordset
  90. Array
  91. (
  92.     [users_id] => 1
  93.     [0] => 1
  94.     [first_name] => Joey
  95.     [1] => Joey
  96.     [last_name] => Rivera
  97.     [2] => Rivera
  98. )
  99. Array
  100. (
  101.     [users_id] => 2
  102.     [0] => 2
  103.     [first_name] => John
  104.     [1] => John
  105.     [last_name] => Doe
  106.     [2] => Doe
  107. )
  108.  
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
Expand|Select|Wrap|Line Numbers
  1. $mysql = mysql_connect(‘localhost’, ‘example’, ‘example’);
to:
Expand|Select|Wrap|Line Numbers
  1. $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.
Apr 21 '10 #10

Sign in to post your reply or Sign up for a free account.

Similar topics

4
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...
7
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
0
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...
2
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...
1
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`...
1
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;...
7
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...
0
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...
1
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...
1
ssnaik84
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...
0
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...
0
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...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
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...
0
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...
0
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...
1
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)...
1
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....
0
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

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.