Using a prepared statement in php & mysqli

Question

I am using prepared statements for the first time. And i cannot get the select to work. For some reason, it returns all the records but i cannot get them into variables. I know it returns all the records because if i add echo '1'; to the loop it echo's 1 for each record.

Any assistance would be great. The code is below:

function builditems($quote_id){
        if ($stmt = $this->link->prepare("SELECT * FROM `0_quotes_items`  WHERE `quote_id` = ?")) {
            // Bind a variable to the parameter as a string.
            $stmt->bind_param("i", $quote_id);
            // Execute the statement.
            $stmt->execute();
              while ($row = $stmt->fetch()) {
                  echo $row['id'];
                }


            // Close the prepared statement.
            $stmt->close();         
            }
        }

UPDATE: in the error log, i see the following error after adding the while ($row = $stmt->fetch_assoc()) { like suggested:

PHP Fatal error: Call to undefined method mysqli_stmt::fetch_assoc()

I found a link that the same issue was had, but i do not understand how to implement the fix. Any assistance would be great, with regards to a example.

How to remove the fatal error when fetching an assoc array

Answer 1

The PHP MySQLi fetch method does not access query data using brackets notation: $row['id'].

So I see two options to remedy: first find this line:

while ($row = $stmt->fetch()) {

...and modify it to, either, first add the bind_result method, and then access the data a bit differently:

$stmt->bind_result($id, $other, $whatnot); // assuming 3 columns retrieved in the query
while ($row = $stmt->fetch()) {
  echo "$id $other $whatnot<br>";
}

...or, first access the result object's fetch_assoc method and use fetch_assoc instead of fetch:

$result = $stmt->get_result();
while ($row = $result->fetch_assoc()) {

Now you can use table column names as keys to access query data in your loop: $row['id'].

---

PHP MySQLi method fetch requires you to use bind_result. Doing this allows you to call your data by the variable names you've bound it to.

To use the field name as the result array index, such as: $row['id'], you need to use the PHP MySQLi fetch_assoc method. And to use fetch_assoc you need to first get the result object in order to access the fetch_assoc method.