Fetching rows from database via a prepared statement [duplicate]

Question

I want to use prepared statements to retrieve some result from the database and have tried the below. But It always returns []. Is this the correct way to return rows via a prepared statement?

$stmt = $con->prepare('SELECT bookingId,locationName
    FROM bookings
    WHERE username= ?');
$stmt->bind_param('s', $one);
$stmt->execute();
$stmt->bind_result($id, $loc);
$output = array();
while($row = $stmt->fetch()){
    $output[] = $row;
}
$stmt->close();

print(json_encode($output));

Answer 1

Problem:

Unlike PDO in mysqli the function fetch() does not return a row, it just returns a boolean or NULL, check the docs:

#Value  Description
#TRUE   Success. Data has been fetched
#FALSE  Error occurred
#NULL   No more rows/data exists or data truncation occurred

Solution

$sql = '
SELECT bookingid, 
       locationname 
FROM   bookings 
WHERE  username = ? 
';
/* prepare statement */
if ($stmt = $con->prepare($sql)) {
    $stmt->bind_param('s', $one);
    $stmt->execute();   
    /* bind variables to prepared statement */
    $stmt->bind_result($id, $loc);
    $json = array();
    /* fetch values */
    if($stmt->fetch()) {
        $json = array('id'=>$id, 'location'=>$loc);
    }else{
        $json = array('error'=>'no record found');
    }
    /* close statement */
    $stmt->close();
}
/* close connection */
$con->close();
print(json_encode($json));

Answer 2

You could try the following (instead of using bind_result()):

$result = $stmt->get_result();

$output = array();
while ($row = $result->fetch_assoc()) {
    $output[] = $row['bookingId'];
}

It basically makes your row already an array. Could be that this works better for you. If you're not doing anything else in the while loop, you can do what RobP suggested, and just use fetch_all:

mysqli_fetch_all($result, MYSQLI_ASSOC);