Using Stored Procedures & MySQLI in PHP 5

January 7th, 2007 by rvdavid Leave a reply »

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:

//script excerpt 1.1
$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.”

// script excerpt 1.2
$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.

// Script excerpt 1.3 - Running SP by using MySQLI::multi_query() or MySQLI::real_query()

// 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).

// Script excerpt 1.4 - Running SP by using MySQLI::query()
// 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

if you enjoyed this post, make sure you subscribe to my RSS feed!
You can also follow me on Twitter here.

Related posts:

  1. The Singleton Pattern with PHP
Advertisement

36 comments

  1. Kevin says:

    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.

  2. Ben Rowe says:

    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.

  3. rvdavid says:

    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.

  4. DevGirl says:

    I found your site through google, thanks for being a life saver xoxo :)

  5. dipti jain says:

    How can I get OUT parameters from stored procedures using mysqli?

  6. rvdavid says:

    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

  7. Gareth says:

    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

  8. rvdavid says:

    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.

  9. Martin says:

    Great Tip!
    Thank you very much

  10. PHP Sucks says:

    Thanks for this I was looking for a nice solution this saved my life.

    Thanks Again

  11. 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?

  12. Veerpartap says:

    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

  13. Neil Young says:

    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!!

  14. rvdavid says:

    @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 :)

  15. Sergio says:

    Muy Bueno el ejemplo! Gracias Saludos

  16. especht says:

    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.

  17. Farhan says:

    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.

  18. beely says:

    Great info you had post, and thank for guidelines of how to retrieve resultset from stored procedure. It help me alot of understanding. I wonder whether any solution for mySQL (not with mySQLi extension) could able to return resultset from the stored procedure?

  19. Himanshu says:

    Great!
    It was the solution I was looking for since long.
    Thanks.

  20. tarek says:

    i am new to PHP.

    i have this code that calls an Update stored procedure

    require(“../config.inc.php”);

    $mysqli = new mysqli($server,$username,$password,$db);

    if (mysqli_connect_errno()) {
    echo “connection error”;
    exit();
    }

    /*
    Call my stored procedure the first time

    update news set News_Date = NewsDate , News_Title = NewsTitle , News_Details = NewsDetails where News_ID =NewsID
    NewsDate varchar(100), NewsTitle varchar(200), NewsDetails longtext
    */

    $date= date_create(changeDate(“$Date”).” “.date(“H:i:s”));
    $shortDate = date_format($date,’Y-m-d H:i:s’);

    /* print ( $shortDate .”/”. $Title .”/”. $Det .”/”. $I);*/

    if ($mysqli->multi_query(“call News_Update_ByID((string)$shortDate,(string)$Title,(string)$Det,(int)$I)”))
    {

    LoadNews($I);

    }
    else
    {

    print (“Nothing Updated.”);
    exit();
    }

    the first parameter i pass is a datetime.

    the query doesnt work. please help.

  21. tarek says:

    would you please post a sample code of how to use an update Stored Procedure (update news set News_Date = NewsDate , News_Title = NewsTitle , News_Details = NewsDetails where News_ID =NewsID ) within php using mysqli.

    Thanks in advance.

  22. gros says:

    could you please show how to use mutli_query for an INSERT statement, and then retrieve insert_id.
    thanks

  23. Alex says:

    Thanks a lot – your article was the solution for my mysqli problem!

  24. Edo Williams says:

    I am a bit new to PHP and MYSQL. I have been having some issues getting my stored procedure to work.

    What I am trying to do is use a query result to execute a stored procedure.

    logic
    Query 1 executes returning id value
    Query 2(stored procedure) needs the id value to return result.

    code
    //I run through the first query and save it into an array
    $query_security = “SELECT m.id FROM gfd.masterlist m”;
    $result_s = $link->query($query_security, MYSQLI_STORE_RESULT);
    $i = 0;
    while(list($id, $filename, $begin, $send) = $result_s->fetch_row()) {
    $filenameID_result[$i]["id"]=$id;
    $i++;
    }

    mysqli_free_result($result_s);

    for($i=0; $i<count($filenameID_result); $i++) {
    $query = “CALL sp_CompareSetOHCL(‘”.$filenameID_result[$id]‘”)”;
    $result_id = mysqli_query($link, $query);
    while(mysqli_next_result($link)){
    while($data = mysqli_fetch_assoc($result_id)){
    $o .= ‘
    ‘.$filenameID_result[$i]["id"].’
    ‘;
    }
    }
    }

    I come from the background of SQL server where I can store this in a results set and not have to deal with memory errors. Most of the error messages i am getting is memory and non execute.
    Can someone help me.
    Thanks

  25. kanna says:

    Thanks a lot!!!!!!!!

  26. I liked your page please check mine too, about cool pages …

  27. Jonathan says:

    Why has it taken me four miserable years to find this post. Thanks a million!!

    I’ve been opening a new connection for every query for the last four years.

    Why of why has this information not made it onto the php.net manuals?

    Thanks again.

  28. Thanks a lot man! It seemed virtually impossible to assemble a working solution from the official php documentation. I really appreciate, that your solution works (unlike many other out there).

    Keep up the excellent work, cheers!

  29. 99developer says:

    hi, i have called MS-SQL SP through ADO-DB library having in-Out parameter..
    you can check out this.

    http://developer99.blogspot.com/2011/07/calling-ms-sql-sp-from-php.html

  30. This was a very interesting read, we have been using stmts for a few months now and it really is a great improvement thus far.

Leave a Reply

Notify me of followup comments via e-mail. You can also subscribe without commenting.