Sunday, January 7th, 2007
Using Stored Procedures & MySQLI in PHP 5
For the occasions that you want to use a stored procedure you’ve written in MySQL 5 through PHP , the semantic tool to use is PHP’s built in mysqli objects/package.
According to the php.net: “The mysqli extension allows you to access the functionality provided by MySQL 4.1 and above.”
MySQLI stands for MySQL Improved, and it most certainly is with the addition of prepared statements and extensible Object Oriented interface.
MySQLI is a package that contains three integrated objects:
- MySQLI - The MySQL Connection object. Has methods specific to database connection and connection related operations and query execution.
- MySQLI_Stmt - The SQL statement object. Has methods specific to binding variables to prepared statements and results along with storage and SQL query related properties.
- MySQLI_Result - The Resultset object. Has methods specific to the resultset produced by the executed SQL statement.
Enabling mysqli on PHP 5 in Windows XP
I run Windows XP on my development machine with the following environment:
- PHP 5.1.2
- MySQL 5.0.22
- Apache 2.0.55
First off, I had to edit my php.ini file and enable the mysqli extension by typing in “extension=php_mysqli.dll”.
So once this is done, you can start using the built-in mysqli object in PHP 5.
This should work without hickups, but there’s always the rare exception. If you are having trouble installing mysqli or require more info about PHP’s built-in mysqli object , then please go here.
Testing that MySQLI is enabled
As a test, you could create a new instance of mysqli:
$mysqli = new MySQLI(‘host’,‘user’,‘pass’,‘dbname’);
If mysqli is set up properly, it should not give you an error saying mysqli is undefined/not found.
Assumptions
At this stage, I’ll assume that you have a working server with MySQLI set up and can now do things with the MySQLI object.
Executing Stored Procedures
As an example, say that you had to created a stored procedure which returns a record in its resultset and now need to use it in PHP.
First thing’s first, you shouldn’t treat calls to stored procedures which return a resultset as you would normal SQL queries. The reason being is that when executed, a stored procedure will give you two resultsets back. One with the actual resultset and another which sends the status of the stored procedure (OK/ERR).
So when you do something like calling another query later on in your script (as demonstrated below in script excerpt 1.2), this will produce a MySQL error: “Lost connection to MySQL server during query”. Or if you didn’t free any results, it will produce the following error “Commands out of sync; you can’t run this command now.”
$mysqli = new MySQLI(‘host’,‘user’,‘pass’,‘db’);
$result = $mysqli->query("CALL sp_somestoredproc()");
$data = $result->fetch_assoc();
$result->free();
// use data from the first result to get other results.
// granted this is not the best way, but well, lets just do it for example’s sake.
$result2 = $mysqli->query("SELECT category_name
FROM categories
WHERE category_id = {$mysqli->escape_string($data[’category_id’])}");
Why does this happen?
When you use the first result from the stored procedure (remember stored procs produce 2), you’ve still got another result waiting in line.
This is not allowed and the mysql database regards this as an incorrect order of commands (commands out of sync). The error makes sense. Why are you trying to create another resultset whilst there is still an unused result in the queue? Here’s the solution!
Buffer results/Use Result/Free buffered results
In order to resolve issues we are experiencing with stored procedures, we need to handle the second resultset being dished out by the stored procedure. To do this, we need to do the following
- buffer the resultsets
- use or assign gathered data from the first resultset
- free the first resultset
- then loop through the remaining resultset(s) and free them with each iteration.
To buffer results, you can use either multi_query or real_query methods of the mysqli object.
// create a new instance of mysqli
$mysqli = new MySQLI(‘host’,‘user’,‘pass’,‘db’);
$query = $mysqli->multi_query("CALL sp_somestoredproc()"); // automatically buffers resultsets and assigns true or false on fail to $query
// OR $mysqli->real_query() - remember the main point of this is NOT to execute multple queries, but to acquire buffered results.
//check if the query was successful
if ($query) {
//asign the first result set for use
$result = $mysqli->use_result();
//use the data in the resultset
$data = $result->fetch_assoc();
//free the resultset
$result->free();
//clear the other result(s) from buffer
//loop through each result using the next_result() method
while ($mysqli->next_result()) {
//free each result.
$result = $mysqli->use_result();
if ($result instanceof mysqli_result) {
$result->free();
}
}
// use data from the first result to get other results.
// granted this is not the best way, but well, lets just do it for example’s sake.
$result2 = $mysqli->query("SELECT category_name
FROM categories
WHERE category_id = {$mysqli->escape_string($data[’category_id’])}");
}
Using MySQLI::Query()
In my tests, using MySQLI::Query($sql) does not break anything as long as you remember to clear the excess results, contrary to everyone out there who are telling people to use mysqli_real_query and/or mysqli_multi_query and that using mysqli_query is wrong (even in php bug reports and the notoriously “noisy” documentation found at php.net).
So until it can be proven that MySQLI::query() is in fact the wrong method to use for SPs, I’ll keep his example up which lets you use stored procedures with MySQLI::Query(). I think the key to handling stored procedures lies within the cleaning up of the second result set and not the actual usage of methods (buffered or not).
// create a new instance of mysqli
$mysqli = new MySQLI(‘host’,‘user’,‘pass’,‘db’);
$result = $mysqli->query("CALL sp_somestoredproc()"); assign results to $result.
//check if the query was successful
if ($result) {
//use the data in the resultset
$data = $result->fetch_assoc();
//free the resultset
$result->free();
//clear the other result(s) from buffer
//loop through each result using the next_result() method
while ($mysqli->next_result()) {
//free each result.
$result = $mysqli->use_result();
if ($result instanceof mysqli_result) {
$result->free();
}
}
// use data from the first result to get other results.
// granted this is not the best way, but well, lets just do it for example’s sake.
$result2 = $mysqli->query("SELECT category_name
FROM categories
WHERE category_id = {$mysqli->escape_string($data[’category_id’])}");
}
Errata
- Changed $mysqli->next_results() to $mysqli->next_result() (Reporeted by Gareth on September 13th, 2007)
on Friday, January 12th, 2007 at 12:59 am:
oh great! my next project is using stored procedures, I’ll be able to use this.
I think it’s weird that php doesn’t handle the mysqli results automatically. So much for improved.
I could see myself getting into a bad situation if I came across this. My thinking is stoerd procedure sql == normal query sql - or at least thought that it handled the same.
on Monday, February 5th, 2007 at 3:57 pm:
MySQLi has always been one of those things I want to use, but because of the platform restrictions I have, I’ve never would be able to implement it.
From what you’ve shown, I’d prefer the mysqli library as its object based, with an object based result set.
If only more server administrators wouldn’t be so scared of upgrading to php 5.
on Tuesday, February 13th, 2007 at 10:27 pm:
If you find object based connection packages are of your tastes, make sure to check out PDO also.
Apparently, it’s the “fastest of them all” when it uses prepared statements.
I agree with you on the slow move to PHP 5. I used to host myself and my clients on a host who did not want to budge - They’re a nice bunch of people and all that, but in the end of it, they were just “too big” to upgrade.
So I left them and took my clients with me.
on Sunday, February 18th, 2007 at 9:20 pm:
I found your site through google, thanks for being a life saver xoxo :)
on Wednesday, September 5th, 2007 at 10:38 pm:
How can I get OUT parameters from stored procedures using mysqli?
on Thursday, September 6th, 2007 at 9:56 am:
To get anything out of a query, you’d have to attach it to the resultset being returned by your stored procedure. You could add it to your SELECT statement in your store procedure like in the following example. I’ve used the CONCAT() function in MYSQL to build the query. When I get the result, I can refer to the OUTPARAM as `some_param` as if it was any other field returned from the database.
eg SET @SQL = CONCAT(”SELECT `foo`, `bar`, “,OUTPARAM,” as `some_param` FROM `sometable`”)
HTH
on Thursday, September 13th, 2007 at 8:15 am:
many many thanks for posting this, i’m new to MySQL, slowly getting to grips with it as it’s very different to the SQL Server / T-SQL flavours i’m used to.
been wrestling with a PHP problem unknowingly due to a stored procedure call for hours - your explanation & example here is priceless, miles better than anything i found in the MySQL manuals or elsewhere on the web
fyi - v. minor flaw in your code example - “while ($mysqli->next_results())” should be next_result() i think?
thanks again, i’ll be checking back here for more useful advice!
GW
on Thursday, September 13th, 2007 at 10:24 am:
You are welcome Gareth, I’m glad that you found this useful.
Regarding the method next_results, you are, of course, right.
I in turn would like to thank you for sending in your correction.
on Friday, April 25th, 2008 at 7:55 pm:
Great Tip!
Thank you very much
on Sunday, May 18th, 2008 at 4:01 am:
Thanks for this I was looking for a nice solution this saved my life.
Thanks Again
on Monday, May 19th, 2008 at 7:10 pm:
I want to create stored procedure in PHP using mysql() or mysqli() . Can i create stored procedure without using mysql console? Like
mysql_query(”create procedure test (in name varchar(20), in email varchar(100), out param) as ……………”);
Beause i get error when i execute the above query in phpmyadmin. Or is there any seting to do in phpmyadmin to run Stored procedure?
on Monday, May 26th, 2008 at 3:54 am:
hi all…….I just read lots of stuff over the net after that i m not able to get how to create SP in php/mysql and use its. result.
can any body mail me the link or examples.
for it.
I want to use it in my project.
Thanks
on Wednesday, September 3rd, 2008 at 3:31 am:
I spent a good two hours trying to find out why my stored procedures were not working and this answers my questions perfectly.
Thank you very much!!
on Friday, September 5th, 2008 at 9:02 am:
@mayurika Looks like you’ve mixed up some of the Stored Proc syntax. Although there are some Stored Proc examples already out there, I’ll provide an example for you to use as reference in the coming days/weeks.
@Veerpartap as mentioned above, I’ll provide some examples in the coming days/weeks.
@neil You’re welcome, I’m glad it helped clear things up :)
on Wednesday, October 1st, 2008 at 11:25 pm:
Muy Bueno el ejemplo! Gracias Saludos
on Thursday, October 2nd, 2008 at 4:04 am:
Have you had a chance to post anything about creating the stored procedure from within a PHP page? I’m having difficulty using phpMyAdmin and don’t have access to the SSH command environment. Please let me know if you have any suggestions on how to create the procedure from within a PHP page.
on Wednesday, October 8th, 2008 at 7:31 pm:
Hi i am unable to call and fetch the records two time the next one is giving error,
i ma using mysqli with stored procedure in PHP5.
any Help please.