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

bloodyKnuckles · Answer 1 · 2015-03-17T19:33:38.670

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.

edited Mar 17 '15 at 19:33

answered Mar 16 '15 at 21:48

bloodyKnuckles

11,551
3
29
37

I changed it and when i reloaded the form. The entire form was thrown out, All the styling was lost. As soon as i remove the _assoc the problem goes away. Why would the form crash like that? – Marcel Mar 17 '15 at 05:02
The code you posted looks fine. I need to see what calls builditems to investigate. I suggest opening another question sin e this one is solved. – bloodyKnuckles Mar 17 '15 at 09:42
its just a function that is calling at the moment. I intent to return the values in a table. But i cannot get the values from the database. – Marcel Mar 17 '15 at 18:17
What are the column names in the table? Is one of them named *id*, hence `$row['id']`? – bloodyKnuckles Mar 17 '15 at 18:21
Yes one of them is id, it is the auto inc value. I am just trying to get the first out. Before moving to the rest. – Marcel Mar 17 '15 at 18:33
Ah, I see the problem. Your update with link to your question solved it for me. I updated my response, and I have a working example on my end. – bloodyKnuckles Mar 17 '15 at 19:09
I have added the last option you mentioned. the `$result = $stmt->get_result();` and i get the following error when the page loads now. `PHP Fatal error: Call to undefined method mysqli_stmt::get_result()`; – Marcel Mar 18 '15 at 05:31
To debug change your `execute` line to `$stmt->execute() or die($this->link->error);` and report what you get. – bloodyKnuckles Mar 18 '15 at 13:57

Using a prepared statement in php & mysqli

1 Answers1