2nd MySQL Dataset not working

[vjmehra]

I have a function:
 

//----------------------------------------------------------------------
$server_name='xxx';
$user_name='xxx';
$password='xxx';
$database_name='xxx';
$db1= new mysqli($server_name, $user_name, $password, $database_name);
//----------------------------------------------------------------------

function get_dataset($db1, $query_string) {
//---------------------------------------------------------------------
$result1 = $db1->query($query_string);
//$num_results = $result1->num_rows;

$num_results = $result1->num_rows;

$res_array = array();

for ($count=0; $row = $result1->fetch_assoc(); $count++) {
$res_array[$count]=$row;
}

return $res_array;


//$result8 = db_result_to_array($result1);
//return $result1;
$query_string->free();
$result1->free();
//$db1->close();
}
//---------------------------------------------------------------------

Now, this works fine, sort of....

On a separate page, I call the function:
 

$result=get_dataset($db1,"call sp_filter_issues_reduced_view(".$subsectorid.",".$bondticker.",".$currencyid.",".$countryid.")");

This works fine, however, I also have a few comboboxes I want to pull datasets into. This wasn't working, so just to test, I tried the following:
 

$result=get_dataset($db1,"call sp_filter_issues_reduced_view(".$subsectorid.",".$bondticker.",".$currencyid.",".$countryid.")");

$result2=get_dataset($db1,"call sp_filter_issues_reduced_view(".$subsectorid.",".$bondticker.",".$currencyid.",".$countryid.")");

So the 2nd, is simply a copy/paste of the first, yet it returns this error:

Fatal error: Call to a member function fetch_assoc() on a non-object in FILE PATH\functions.php on line 33

(line 33 being the line including fetch_assoc() in the function get_dataset, shown above).

Now, from this, I'm assuming somehow my dataset isn't clearing properly, or something like that. If I call the function once it works, twice it doesn't.

This is really bugging me and has been for weeks now, so I'd really appreciate it, if anyone could help out!!!

Edited March 19, 2013 by vjmehra

[Barand]

A function exits on the return statement. Any code after that is not executed.

[vjmehra]

It makes no difference whether I include the return function or not unfortunately...it always keeps coming back to:

 

 

for ($count=0; $row = $result1->fetch_assoc(); $count++) {

$res_array[$count]=$row;
}

 

It doesn't like the second dataset somehow, just keeps coming back with 'Fatal error: Call to a member function fetch_assoc()', however if I just have 1 dataset its fine!

 

I'm sure this must be really obvious to someone who knows what they're talking about, but I'm really stuck :-(

[PaulRyan]

Firstly, a WHILE() should suffice instead of FOR()

 

 

  while($row = $result1->fetch_assoc()) {
    $res_array[] = $row;
  }

 

Secondly, you need to free up the $query and $result1 before returning data from the function

 

 

  $query_string->free();
  $result1->free();
 
  return $res_array;
}

[DavidAM]

Fatal error: Call to a member function fetch_assoc() on a non-object in FILE PATH\functions.php on line 33

non-object -- this is an indication that the query failed for some reason. You need to check after calling mysqli.query to see if you have a valid result and if not, deal with the error mysqli.error.

 

Note that when executing stored procedures in mySql, you get multiple resultsets from one call, so you have to call mysqli.next-result after processing the result data to avoid the "Commands out of sync" error.

 

Is this your real code? Because I would expect the non-object error when you call num_rows() if the query failed, unless you have done something between there and the fetch_assoc() call that is invalidating the result set.

 

In any case, always check the return values and handle any error conditions as you go along.

[vjmehra]

I re-wrote the function as (I think) you are suggesting:

 

 

function get_dataset($db1, $query_string) {
//---------------------------------------------------------------------
$result1 = $db1->query($query_string);
//$num_results = $result1->num_rows;
$num_results = $result1->num_rows;
$res_array = array();
while ($row = $result1->fetch_assoc()) {
$res_array[]=$row;
}

$query_string->free();
$result1->free();
return $res_array;
}
//---------------------------------------------------------------------

 

However now I just get the error:

 

Fatal error: Call to a member function free() on a non-object in C:\Program Files (x86)\Apache Software Foundation\Apache2.2\htdocs\Blue_Ocean\functions.php on line 68

 

If I comment out the ....->free() bits....I just get the fetch_assoc() error again.

[vjmehra]

DavidAM- The query displays correctly if I have one dataset, I simply use the same query for the second dataset and I get the error, so the SQL and result are both fine.

[DavidAM]

1) $query_string is apparently a string (not an object) and as such, it does not have a free() method to be called. That free() statement is useless.

 

2) $result1 is an object, or at least it should be, and so that free() call can stay.

 

3) When you report an error on the board, report THE ENTIRE ERROR MESSAGE. If the message says "Call to a member function fetch_assoc() on a non-object" (as you stated in your first post), then the query has failed!!!!! I said this before, you need to check after you execute query() to see if the query() succeeded. Just because it worked once does NOT mean it will work EVERY SINGLE TIME.

 

If you put the line if (! $result1) die($db1->error); (for debugging purposes, only) immediately after executing the query, you will see the error message from the database server. If this is happening the SECOND time you call the function, and the FIRST time you called it you executed a STORED PROCEDURE, then that error probably says "commands out of sync".

[vjmehra]

I did what you said (hopefully correctly), so this is the code:

 

 

function get_dataset($db1, $query_string) {
//---------------------------------------------------------------------
$result1 = $db1->query($query_string);
//$num_results = $result1->num_rows;
if (! $result1) die($db1->error);
$num_results = $result1->num_rows;
$res_array = array();
while ($row = $result1->fetch_assoc()) {
$res_array[]=$row;

}
//$query_string->free();
$result1->free();
return $res_array;
}
//---------------------------------------------------------------------

 

The error was exactly what you predicted:

 

Commands out of sync; you can't run this command now

 

....so what steps should I take next? I'm not sure I understand the problem here, are you saying this message suggests the issue is with the database, not the php?

[DavidAM]

When mySql executes a stored procedure, the last thing it does is to issue a result-set with the completion status of the procedure. If the procedure itself returns a result-set (i.e. has a SELECT to return rows), then there are TWO result-sets sent back -- the SELECT will be first, and the completion status will be second. When PHP processes only the FIRST result-set (the selected rows), the second result-set is still sitting in the "pipe" waiting for you to retrieve it. Since this "pipe" belongs to the database object, you can NOT send a new request to the database until you clear it out. Just freeing the result object does NOT clear the pipe. You have to retrieve the waiting result set. This behavior is similar to using mysqli.multi_query which allows you to send multiple SELECT (or other) statements in a single call.

 

The process is: query(), (loop to) fetch(), if more_results(), next_result(), store_result() (or use_result()), [back to loop to fetch]. Using your code as an example, if you are sure there will NEVER be additional result-sets that you are interested in, you could do something like this:

function get_dataset($db1, $query_string) {
//---------------------------------------------------------------------
$result1 = $db1->query($query_string);
//$num_results = $result1->num_rows;
if (! $result1) die($db1->error);
$num_results = $result1->num_rows;
$res_array = array();
while ($row = $result1->fetch_assoc()) {
$res_array[]=$row;

}
//$query_string->free();
$result1->free();

#MAD Add these lines to "clear the pipe" after a Stored Procedure Call
while ($db1->more_results()) {
  $db1->next_result();
  #MAD I'm not sure if these two lines are needed or not
  $result = $db1->store_result();
  $result->free();
}


return $res_array;
}
//---------------------------------------------------------------------

There is some caveat about using store_result() vs. using use_result() but I can't remember off the top of my head how that works, and I can't get to my library to check my code base.

 

I still do not understand why you don't get the error when call num_rows() on that invalid object, but that may just be a quirk of the PHP object model, or your error_reporting value.

[vjmehra]

Ok this does appear to be working! Thanks for your help, I'm sort of going through the code, trying to understand why this works, not entirely sure I do to be honest, but I'm glad it does :-)