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 · Accepted Answer · 2015-03-17 19:33:38Z

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.

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?
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.
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.
What are the column names in the table? Is one of them named id, hence $row['id']?
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.

Collectives™ on Stack Overflow

Using a prepared statement in php & mysqli

1 Answer 1

8 Comments

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

8 Comments

Linked

Related