Trying to get mysqli_fetch_assoc working with bind_param

Question

This works, it returns my array in the form:

{"results":[{"cat_id":"8",
"cat_name":"dental hygienist"},
{"cat_id":"5","cat_name":"stocktaker"},
{"cat_id":"9","cat_name":"builder"}]}

My php code is :

$query2 = "SELECT DISTINCT cat_id,cat_name FROM review WHERE public_or_private = 2";
$result2 = mysqli_query($con,$query2);
$rows = array();
while($row = mysqli_fetch_assoc($result2)) {
    $rows['results'][] = $row;
}  
echo json_encode($rows);

But how can I get similar to work with parameterised queries?

mysqli_query(), mysqli_store_result() or mysqli_use_result()... I've tried several things like this but I keep getting null.

My php code:

$query3 = "SELECT DISTINCT cat_id,cat_name FROM review WHERE user_id = ? AND public_or_private = 0";
$stmt3 = $con->prepare($query3) or die(mysqli_error($con));
$stmt3->bind_param('i', $user_id) or die ("MySQLi-stmt binding failed ".$stmt3->error);
$stmt3->execute() or die ("MySQLi-stmt execute failed ".$stmt3->error);
$result3 = $stmt3->get_result();
$rows = array();
while ($row = mysqli_fetch_assoc($result3)) {
    $rows['results'][] = $row;
} 
echo json_encode($rows);

Your parameterized query is different. If you make it the same do you get no results as well? — user3783243, May 27 '18 at 12:07
@user3783243 possibly where you are from, but not where OP and I are from. — Nick, May 27 '18 at 12:18
@user3783243 You're first comment got me to thinking, this answer is useful: https://stackoverflow.com/questions/13105373/php-error-cannot-pass-parameter-2-by-reference, will post answer when I get it solved. — CHarris, May 27 '18 at 12:21
Ah, @Nick dictionary I saw redirected the `s` to the `z` page. (I also have only used the `z` version) — user3783243, May 27 '18 at 12:21
I'd start off with the same query, `SELECT DISTINCT cat_id,cat_name FROM review WHERE public_or_private = ?` then bind the `2`. — user3783243, May 27 '18 at 12:28

score 0 · Answer 1 · answered May 27 '18 at 13:12

I don't know why but this is what worked for me:

$numbervar = 0;
                $query3 = "SELECT DISTINCT cat_id,cat_name FROM review WHERE user_id = ? AND public_or_private = ?";
                $stmt3 = $con->prepare($query3) or die(mysqli_error($con));
                $stmt3->bind_param('ii', $user_id,$numbervar) or die ("MySQLi-stmt binding failed ".$stmt3->error);
                $stmt3->execute() or die ("MySQLi-stmt execute failed ".$stmt3->error);
                $result3 = $stmt3->get_result();

                $rows = array();

                while ($row = mysqli_fetch_assoc($result3)) {

                    $rows['results'][] = $row;

                } 

echo json_encode($rows);

Trying to get mysqli_fetch_assoc working with bind_param

1 Answers1