By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
459,398 Members | 1,351 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 459,398 IT Pros & Developers. It's quick & easy.

How to use MySQL procedure in PHP?

P: 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
Share this Question
Share on Google+
9 Replies


chathura86
100+
P: 227
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

P: 20
@chathura86
How to use in PHP?
Give any practical example.
Apr 16 '10 #3

Dormilich
Expert Mod 5K+
P: 8,639
@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
Expert 5K+
P: 5,058
@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

P: 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
Expert 5K+
P: 5,058
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

P: 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
Expert 5K+
P: 5,058
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

P: 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

Post your reply

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