Why do bind_param() inside while loop doesn't work? return Fatal Error

Question

Why do bind_param() inside my while loop doesn't work?
I get

Fatal error: Call to a member function bind_param() on a non-object in ...

What I'm going to do here is to show all data from user table (if $key not set) then,
I want to differentiate shown value using if statement. I have tried to execute prepared query in database manager, and it works. But in this case.. my query seems return false. So, any idea? here is the codes,

function cariTemen($key) {
    require 'settings.php';
    $my_id = $_SESSION['user_id'];

    $stmt = $conn->prepare ("SELECT user_id,nama_asli FROM user WHERE username LIKE ? OR nama_asli LIKE ?");
    $key = '%'.$key.'%';
    $stmt->bind_param('ss',$key,$key);
    $stmt->execute();
    $stmt->bind_result($id,$nama);

    //MODIFIED FROM HERE
    while($stmt->fetch()) {
        $temen_id = $id;
        $username = $nama;

        $temen = $conn->prepare("SELECT id_temenan FROM temenan WHERE temen_id = ? AND user_id = ?");
        $temen->bind_param('ii',$temen_id,$my_id); //ERROR IS HERE
        $temen->execute();
        $temen->store_result();
        $jml= $temen->num_rows;

        if($jml > 0) {

            echo $username.' [Temenan]<br>';

        } else {

            echo $username.' <a href="add.php?user_id='.$temen_id.'"">Temenin</a><br>';
        }
    }

    $stmt->close();
}

Thank you in advance for any help you can provide.

as marcellorvalle said that mysql can not maintain 2 simultaneous queries, In this case, i need to store all the values into an array() variable. so the script would be like this :

$user_id = array(); 
$username = array();
$i = 0;

while($stmt->fetch()) {
    $user_id[$i] = $id;
    $username[$i] = $nama;
    $i++;
}

$stmt->close();

for ($j= 0 ; $j <= $i; $j++) {
    $temen = $conn->prepare("SELECT id_temenan FROM temenan WHERE temen_id = ? AND user_id = ?");
    $temen->bind_param('ii',$user_id[$j],$my_id);
    $temen->execute();
    $temen->store_result();
    $jml= $temen->num_rows;

    if($jml > 0) {

        echo  '<a href="profil.php?user_id='.$user_id[$j].'"">'.$username[$j].'</a> [TeMeNan]<br>';

    } else {

        echo ' <a href="profil.php?user_id='.$user_id[$j].'"">'.$username[$j].'</a> <a href="add.php?user_id='.$user_id[$j].'"">[TeMeNin]</a><br>';

    }

}
$temen->close();

After you do `prepare()` do a `echo $conn->error;` and see what you get — Machavity, Apr 05 '16 at 13:58
var_dump($temen) return false, and $temen->error return "Commands out of sync; you can't run this command now" ... — Lukman Nul Hakim, Apr 05 '16 at 15:32
should I mark it as accepted, if the answer below just guide me how to find the error, but the solution will be different for specified error? — Lukman Nul Hakim, Apr 06 '16 at 07:07

score 0 · Accepted Answer · edited May 23 '17 at 12:24

0

It is not bind_param() doesn't work but prepare().
It doesn't work because prepared queries are unbuffered by default, and thus in order to run another query while you didn't get all the results from the first one, you have to call store_result() on the first query first.
99 times of 100 when you do a nested query, it means that you should have been running a single query with JOIN

edited May 23 '17 at 12:24

Community

1
1

answered Apr 06 '16 at 11:03

Your Common Sense

156,878
40
214
345

score -1 · Answer 2 · answered Apr 05 '16 at 14:19

-1

According to the message $temen seems not to be an object. Try to debug it using var_dump($temen). It seems the $conn->prepare is returning false for some reason and false is not an object.

You can check any error echoing $conn->error to see what happened.

answered Apr 05 '16 at 14:19

marcellorvalle

1,631
3
17
30

var_dump($temen) return false, and $temen->error return "Commands out of sync; you can't run this command now" ... – Lukman Nul Hakim Apr 05 '16 at 15:32
Seems mysql can not maintain 2 simultaneous queries. Maybe you should fetch the first querie on a array, free the statement, and iterate over it. Take a look here: http://stackoverflow.com/questions/614671/commands-out-of-sync-you-cant-run-this-command-now – marcellorvalle Apr 05 '16 at 15:37
*obviously*, mysql can maintain 2 simultaneous queries – Your Common Sense Apr 06 '16 at 06:23
so, whats the problem? – Lukman Nul Hakim Apr 06 '16 at 06:40

Why do bind_param() inside while loop doesn't work? return Fatal Error

2 Answers2