mysqli link is dropping

Question

I have a function that takes 2 variables, one for the database connection and the other for a query. For some reason, the mysqli link isn't happy unless it is inside the function; It isn't enough to just pass the link.

Has anyone else had this happen and what can I do to avoid instantiating the db object inside the function.

This does not work but if I put the object inside the function, it does.

---

function test($db, $query)
{
  if($db->multi_query($query)){
    do{
      if($result = $db->store_result()){
        while($row = $result->fetch_row()){
          print_r($row);
        }
        $result->free_result();
        $result->close();
      }
      if($db->more_results()){
        // print something here.
      }
    }while($db->next_result());
  }
  $db->close();
}

Here is how I am calling them

$db = new mysqli('xxxx','xxxx','xxxx','xxxx');
$q1 = ("SELECT * FROM table1");
$q2 = ("SELECT * FROM table2");

test($db,$q1);
test($db,$q2);

Without using the mysqli link inside the function, I will never see query #2 come back. The only thing I get are errors on the second query. (multi_query(): Couldn't fetch mysqli)

---

I just ran your code and here is what I get from the error log 

[Fri Jan 08 08:12:43 2010] [error] [client 127.0.0.1] PHP Warning: mysqli::multi_query(): Couldn't fetch mysqli in C:\Program Files\Apache Software Foundation\Apache2.2\htdocs\test\1.php on line 24

[Fri Jan 08 08:12:43 2010] [error] [client 127.0.0.1] PHP Warning: mysqli::close(): Couldn't fetch mysqli in C:\Program Files\Apache Software Foundation\Apache2.2\htdocs\test\1.php on line 38 [Fri Jan 08 08:12:43 2010]

[error] [client 127.0.0.1] PHP Warning: mysqli::multi_query(): Couldn't fetch mysqli in C:\Program Files\Apache Software Foundation\Apache2.2\htdocs\test\1.php on line 24 [Fri Jan 08 08:12:43 2010]

[error] [client 127.0.0.1] PHP Warning: mysqli::close(): Couldn't fetch mysqli in C:\Program Files\Apache Software Foundation\Apache2.2\htdocs\test\1.php on line 38 [Fri Jan 08 08:12:43 2010]

[error] [client 127.0.0.1] PHP Warning: mysqli::multi_query(): Couldn't fetch mysqli in C:\Program Files\Apache Software Foundation\Apache2.2\htdocs\test\1.php on line 24 [Fri Jan 08 08:12:43 2010]

[error] [client 127.0.0.1] PHP Warning: mysqli::close(): Couldn't fetch mysqli in C:\Program Files\Apache Software Foundation\Apache2.2\htdocs\test\1.php on line 38 [Fri Jan 08 08:12:43 2010]

[error] [client 127.0.0.1] PHP Warning: mysqli::multi_query(): Couldn't fetch mysqli in C:\Program Files\Apache Software Foundation\Apache2.2\htdocs\test\1.php on line 24 [Fri Jan 08 08:12:43 2010]

[error] [client 127.0.0.1] PHP Warning: mysqli::close(): Couldn't fetch mysqli in C:\Program Files\Apache Software Foundation\Apache2.2\htdocs\test\1.php on line 38

---

Here is what I am running now but still only returns a single query which happens to be the first one listed 'Notes'

function query_db( $link, $query ) {

 /* execute multi query */
 if ( $link->multi_query( $query ) ) {
    do {
        /* store first result set */
        if ($result = $link->store_result() ) {
      while( $row =  $result->fetch_row() ) {
       print_r( $row );
      }
      echo '<br/>';
               $result->close();
        }
     if( $link->more_results() ) {
     // ...
     }
    } while ( $link->next_result() );
 }
}

$db = new mysqli( 'xxxx', 'xxxx', 'xxxx', 'xxxx' );
query_db( $db, 'CALL Notes();'
query_db( $db, 'CALL Users();'
query_db( $db, 'SELECT * FROM test';

---

$db = new mysqli('xxxx','xxxx','xxxx','xxxx');

$f1 = fa($db,"CALL phones();");
$f2 = fa($db,"CALL users();");

OK, very simply.. We query the database 2x. Once to get all phones then assign the return set to var $f1. We do the same thing for $f2. When I run this code, $f2 never has a result. it's always empty. However, when I do a print_r on each call independently, I get the right results. This is really weird! You should be able to replicate this very same behavior with the code we have posted below. Just try and assign the returned set to each variable, then try and echo the results.

Answer 1

The connection is closed after the first call to this function, that's why you aren't getting the results from the second call.

Also, why are you using multi_query if you aren't passing in two or more queries in at a time? You have two options:

You can concatenate the two SQL strings and call the function ONCE with the queries concatenated together. That's why we're using multi_query here.

OR ( this is what I prefer to do ):

Get rid of the $link->close(); line and call the function as many times as you wish. ;)

This way, the function can act as a wrapper function for both query and multi_query in the sense that you can pass in as many queries as you want, but also as little as one query too.

When using stored procedures, the next_result() method returns the return value of the function, then if you call it again, it returns the actual resultset, that's why i have the optional parameter called $sp.

Here's the updated code:

$db = new mysqli( 'xxxx', 'xxxx', 'xxxx', 'xxxx' );

function query_db( $link, $query, $sp = false ) {
 $set_array = array();
 /* execute multi query */
 if ( $link->multi_query( $query ) ) {
    do {
        /* store result set */
        if ($result = $link->store_result() ) {
            $set_array[] = $result;
        }
        if( $link->more_results() ) {
            // ...
        }
    } while ( $link->next_result() && ( $sp ? $link->next_result() : true ) );
 }
 if( count( $set_array ) ) 
     return $set_array;
 return false;
}

$table_set = query_db( $db, 'show tables;');
$notes_set = query_db( $db, 'CALL Notes();', true ); // stored procedure.
$users_set = query_db( $db, 'CALL Users();', true ); // stored procedure.

Usage:

foreach( $users_set as $set ) {
    while( $row = $set->fetch_row() ) {
        print_r( $row );
        echo '<br/>';
    }
}