mysqli dataset, 2nd query not working...

I've seen plenty of examples of how to do this and for some reason non seem to work for me.

On test.php I have:

$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.")");
var_dump($result2);

Yep, that is exactly the same query twice, this was just to test my theory there is something wrong with calling the function twice!

$result works correctly
$result2 shows: bool(false)

How is this possible, when they are exactly the same?

The function is as follows:

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

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

The connection string is as follows:

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

I am completely stuck on this, so would really appreciate any help!

Quote:

---

Originally Posted by vjmehra

I've seen plenty of examples of how to do this and for some reason non seem to work for me.

On test.php I have:

$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.")");
var_dump($result2);

Yep, that is exactly the same query twice, this was just to test my theory there is something wrong with calling the function twice!

$result works correctly
$result2 shows: bool(false)

How is this possible, when they are exactly the same?

The function is as follows:

Code:

---

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

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

---

The connection string is as follows:

Code:

---

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

---

I am completely stuck on this, so would really appreciate any help!

---

You are actually closing the connection to the database, which you are supposed to be done only at the very end of the page or when you have no more access to db needed in that page! Comment it out and see whether it is working now. Also, when you post code next time, enclose them in CODE or PHP tags, which improves the readability to the viewers in here.

:wave:

Um yes that fixed it...I feel quite embarrassed not to have noticed that myself!!!

...actually...perhaps more embarrassing...it doesn't work, I made the change, but re-loaded by backup copy (with very old code), the updated code unfortunately still does not work :-(

Hmm... "doesn't work" doesn't shed any ideas to us. So, it would be helpful if you post any error messages(if any) here, to debug.

:wave:

Yep, fair point! This is the error:

<b>Warning</b>: mysqli_fetch_assoc() expects parameter 1 to be mysqli_result, boolean given in <b>C:\FILE PATH\functions.php</b> on line <b>110</b><br />

This is functions.php:

Code:

---

<?php

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

/*function db_result_to_array($db1, $query_string){
//---------------------------------------------------------------------
$result = $db1->query($query_string);
$res_array = array();

while($res_away = $result->fetch_assoc()){

$field_id="'".$field_id."'";

$results[ $result[$field_id]] =$result;

}
//---------------------------------------------------------------------*/


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

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


function get_sub_dataset($db1, $query7,$sub_field_id) {
//---------------------------------------------------------------------
$query1 = $query7;
$result1 = $db1->query($query1);
$num_results = $result1->num_rows;

//$result8 = db_result_to_array($result1);
return $result1;}
$query1->free;
$result1->free;
//---------------------------------------------------------------------



function drop_down($array_name,$field_id, $query_field_id, $field_name,$db1,$query_string,$row,$multiple=0,$query_string2=0,$sub_field_id=0) {
//-------------------------------------------------------------------------------------------------------------------------
$stat_array=get_dataset($db1,$query_string);

//echo "<select name=".$array_name." width='100%'>";

if(empty($multiple)) {

echo "<select name=".$array_name." width='100%'>";
if ($row[$field_id] == $thiscat[$query_field_id]) {}{echo "<option>None Selected</option>";}

foreach ($stat_array as $thiscat)
{
    echo '<option value="' . $thiscat[$query_field_id] . '"';

    if ($row[$field_id] == $thiscat[$query_field_id]) {echo ' selected';}

    echo '>' . $thiscat[$field_name] . '</option>';
}

}

else {echo "<select multiple size =".$multiple." name=".$array_name." width='100%'>";

foreach ($stat_array as $thiscat)
{
    echo '<option value="' . $thiscat[$query_field_id] . '"';

$list_box_array=get_sub_dataset($db1,$query_string2,$sub_field_id);

foreach ($list_box_array as $list_box_row)
{
    if ($list_box_row[$sub_field_id] == $thiscat[$query_field_id]) {echo ' selected';}
}

    echo '>' . $thiscat[$field_name] . '</option>';
}

}

echo "</select>";


}
//-------------------------------------------------------------------------------------------------------------------------


function simple_drop_down($drop_down_name,$field_id,$field_name,$db1,$query_string, $none=0, $default_value=0) {
//-----------------------------------------------------------------------------------------------------------------------------------
echo "<select name=".$drop_down_name.">";
if ($none == 1) {echo "<option>None Selected</option>";}

$stat_array=array();
$rowcnt=0;
$result=get_dataset($db1,$query_string);

while ($row=mysqli_fetch_assoc($result)){
//$db1->stat_array[$row[$field_id]]=$row[$field_name];


//$stat_array=mysqli_fetch_all(get_dataset($db1,$query_string));
//foreach ($stat_array as $thisstat){
echo "<option value=\"".$thisstat[$field_id]."\"";
    if ($default_value == $thisstat[$field_id]) {echo ' selected';}
echo ">".$thisstat[$field_name]."</option>";
$rowcnt++;
}
echo "</select>";
var_dump(get_dataset($db1,"call sp_sub_sector"));
}

//-----------------------------------------------------------------------------------------------------------------------------------

$db1->close;

?>

---

Having played around a bit, I've still go absolutely nowhere, so I'd really appreciate any help...

In my efforts to try and get things working, I've tried to re-look at the function that converts the dataset to an array, however I just get the following error:

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

The SQL is definitely, absolutely, 100% correct, so that is not the issue!

Code:

---

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

$res_array = array();

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

return $res_array;

}
//---------------------------------------------------------------------

---

So there is some sort of problem with the fetch_assoc line...can anyone help?

Check if the query() is returning a result object or a FALSE value. Upon failure, it would return FALSE. I believe, right now you are getting FALSE !

You can do it like this:

Code:

---

$result1 = $db1->query($query_string);
if($result1 === false)
{
  echo 'Error!';
}
else
{
  // do the fetching...
}

---

:wave:

Having played around a bit, I've still go absolutely nowhere, so I'd really appreciate any help...

In my efforts to try and get things working, I've tried to re-look at the function that converts the dataset to an array, however I just get the following error:

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

The SQL is definitely, absolutely, 100% correct, so that is not the issue!

Code:

---

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

$res_array = array();

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

return $res_array;

}
//---------------------------------------------------------------------

---

So there is some sort of problem with the fetch_assoc line...can anyone help?

Not sure why my bit double posted there!

But yes it does return an error, which makes sense, given the error message I was getting before.

But why?

The SQL is correct, even if I hard code it, rather than passing the query string as a variable it doesn't work, so that isn't the problem.

Also the variable $db1 is correct as the get_dataset function seems to work correctly.

This basically makes no sense at all to me, one function is working, yet another using identical (copied and pasted) code doesn't work...

Quote:

---

Originally Posted by vjmehra

The SQL is correct, even if I hard code it, rather than passing the query string as a variable it doesn't work, so that isn't the problem.

---

Hmm... Can you post the query string?

oddly, if I use the get_dataset function (which is essentially the same code), I just get:

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

Yet this works fine, when pulling up data in a table, as opposed to a combo box...

Its a stored procedure, so it won't mean anything to you, but its correct, it has been working absolutely fine, up until I started changing the php.

The SQL has not been changed at all, it works fine and always has done.

even if I hard code a basic select statement, the same error occurs, so I'm confident that is not the issue.

Ok, so I've just tried the following:

Code:

---

$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.")");

---

If I delete the second line, the first command works correctly, if not, I get the usual error (and yes the second line is copied and pasted, just the name of the dataset is changed):

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

Therefore it must be something to do with the second time the function is called somehow (which is why I always closed then re-opened the database connection before).

Currently, this is the state of the function:

Code:

---

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();
}
//---------------------------------------------------------------------

---

Which we know works fine, the first time it is called, but not the second.

Now, someone out there with far more php knowledge than I, hopefully can see a very simple fix here, as I presume its some kind of issue with the dataset not being cleared properly, or something along those lines...

Any ideas, anyone, I'm really stuck here, can't find anything on google that seems to suggest why this might be occurring :-(

Surely someone must have tried to load 2 datasets before, has no-one come across this before?

Help!!! I'm still stuck on this, surely someone has loaded 2 datasets on a page before????