Results 1 to 17 of 17

Thread: mysqli dataset, 2nd query not working...

  1. #1

    Thread Starter
    Lively Member
    Join Date
    May 2004
    Location
    London
    Posts
    107

    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!

  2. #2
    Freelancer akhileshbc's Avatar
    Join Date
    Jun 2008
    Location
    Trivandrum, Kerala, India
    Posts
    7,652

    Re: mysqli dataset, 2nd query not working...

    Quote Originally Posted by vjmehra View Post
    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.


    If my post was helpful to you, then express your gratitude using Rate this Post.
    And if your problem is SOLVED, then please Mark the Thread as RESOLVED (see it in action - video)
    My system: AMD FX 6100, Gigabyte Motherboard, 8 GB Crossair Vengance, Cooler Master 450W Thunder PSU, 1.4 TB HDD, 18.5" TFT(Wide), Antec V1 Cabinet

    Social Group: VBForums - Developers from India


    Skills: PHP, MySQL, jQuery, VB.Net, Photoshop, CodeIgniter, Bootstrap,...

  3. #3

    Thread Starter
    Lively Member
    Join Date
    May 2004
    Location
    London
    Posts
    107

    Re: mysqli dataset, 2nd query not working...

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

  4. #4

    Thread Starter
    Lively Member
    Join Date
    May 2004
    Location
    London
    Posts
    107

    Re: mysqli dataset, 2nd query not working...

    ...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 :-(

  5. #5
    Freelancer akhileshbc's Avatar
    Join Date
    Jun 2008
    Location
    Trivandrum, Kerala, India
    Posts
    7,652

    Re: mysqli dataset, 2nd query not working...

    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.


    If my post was helpful to you, then express your gratitude using Rate this Post.
    And if your problem is SOLVED, then please Mark the Thread as RESOLVED (see it in action - video)
    My system: AMD FX 6100, Gigabyte Motherboard, 8 GB Crossair Vengance, Cooler Master 450W Thunder PSU, 1.4 TB HDD, 18.5" TFT(Wide), Antec V1 Cabinet

    Social Group: VBForums - Developers from India


    Skills: PHP, MySQL, jQuery, VB.Net, Photoshop, CodeIgniter, Bootstrap,...

  6. #6

    Thread Starter
    Lively Member
    Join Date
    May 2004
    Location
    London
    Posts
    107

    Re: mysqli dataset, 2nd query not working...

    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;
    
    ?>

  7. #7

    Thread Starter
    Lively Member
    Join Date
    May 2004
    Location
    London
    Posts
    107

    Re: mysqli dataset, 2nd query not working...

    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?

  8. #8
    Freelancer akhileshbc's Avatar
    Join Date
    Jun 2008
    Location
    Trivandrum, Kerala, India
    Posts
    7,652

    Re: mysqli dataset, 2nd query not working...

    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...
    }

    If my post was helpful to you, then express your gratitude using Rate this Post.
    And if your problem is SOLVED, then please Mark the Thread as RESOLVED (see it in action - video)
    My system: AMD FX 6100, Gigabyte Motherboard, 8 GB Crossair Vengance, Cooler Master 450W Thunder PSU, 1.4 TB HDD, 18.5" TFT(Wide), Antec V1 Cabinet

    Social Group: VBForums - Developers from India


    Skills: PHP, MySQL, jQuery, VB.Net, Photoshop, CodeIgniter, Bootstrap,...

  9. #9

    Thread Starter
    Lively Member
    Join Date
    May 2004
    Location
    London
    Posts
    107

    Re: mysqli dataset, 2nd query not working...

    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?

  10. #10

    Thread Starter
    Lively Member
    Join Date
    May 2004
    Location
    London
    Posts
    107

    Re: mysqli dataset, 2nd query not working...

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

  11. #11
    Freelancer akhileshbc's Avatar
    Join Date
    Jun 2008
    Location
    Trivandrum, Kerala, India
    Posts
    7,652

    Re: mysqli dataset, 2nd query not working...

    Quote Originally Posted by vjmehra View Post
    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?

    If my post was helpful to you, then express your gratitude using Rate this Post.
    And if your problem is SOLVED, then please Mark the Thread as RESOLVED (see it in action - video)
    My system: AMD FX 6100, Gigabyte Motherboard, 8 GB Crossair Vengance, Cooler Master 450W Thunder PSU, 1.4 TB HDD, 18.5" TFT(Wide), Antec V1 Cabinet

    Social Group: VBForums - Developers from India


    Skills: PHP, MySQL, jQuery, VB.Net, Photoshop, CodeIgniter, Bootstrap,...

  12. #12

    Thread Starter
    Lively Member
    Join Date
    May 2004
    Location
    London
    Posts
    107

    Re: mysqli dataset, 2nd query not working...

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

  13. #13

    Thread Starter
    Lively Member
    Join Date
    May 2004
    Location
    London
    Posts
    107

    Re: mysqli dataset, 2nd query not working...

    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.

  14. #14

    Thread Starter
    Lively Member
    Join Date
    May 2004
    Location
    London
    Posts
    107

    Re: mysqli dataset, 2nd query not working...

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

  15. #15

    Thread Starter
    Lively Member
    Join Date
    May 2004
    Location
    London
    Posts
    107

    Re: mysqli dataset, 2nd query not working...

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

  16. #16

    Thread Starter
    Lively Member
    Join Date
    May 2004
    Location
    London
    Posts
    107

    Re: mysqli dataset, 2nd query not working...

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

  17. #17

    Thread Starter
    Lively Member
    Join Date
    May 2004
    Location
    London
    Posts
    107

    Re: mysqli dataset, 2nd query not working...

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

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  



Click Here to Expand Forum to Full Width