Getting multiple rows from a mysql query with prepared statements?

Question

I was going through some tutorials on prepared statements, and I couldn't figure out how to loop through results that contain multiple rows. I am used to the procedural style of

while($row = mysqli_fetch_array($result))

However I cannot for the life of me figure out how to do this with the prepared statement format.

if($stmt = $mysqli->prepare("SELECT * FROM `Products` WHERE name LIKE '%?%'")){

        $stmt->bind_param("s", $query); //query is $_GET['query'], user input

        $stmt->execute();
        $result = null;
        $stmt->bind_result($result);

        while($stmt->fetch()){ //problematic code...
            echo $result;
        }

        $stmt->close();
}

How can I loop through multiple rows resulting from a prepared statement SQL query in PHP?

EDIT (new code as requested):

/* Create a prepared statement */
    if($stmt = $mysqli->prepare("SELECT name, price, description, file FROM `Products` WHERE name LIKE '%?%'")){

        $stmt->bind_param("s", $query) or die('bind_param');
        $query = trim(htmlspecialchars($_GET['query']));
        $stmt->execute() or die('execute');
        $stmt->bind_result($name, $price, $description, $file) or die('bind_result');


        while($stmt->fetch()){
            echo $name;
        }

        $stmt->close();
    }

You have to define all the fields you want to return and then you need to assign a variable for each of the fields at $stmt->bind_result($result); for example if u want to retrieve product name, age you would do $stmt->bind_result($name,$age); and then you can echo $name, " - ", $age, "\n; and at your query they must be present like SELECT name, age FROM. There is an alternative way to return everything but its not only overcomplicated but rarely needed in most cases and will give u an unneeded overhead if you just want 1 or 2 fields to return. — Prix
– Prix, Commented Feb 16, 2014 at 2:50
@Prix I changed the statement to "SELECT name, price, description, file FROM ..." and bind to $stmt->bind_result($name, $price, $description, $file) but then inside the while loop when I attempt to echo $name, nothing happens. I ran this query in phpmyadmin to doublecheck, and it returns rows. — Jane Doe
– Jane Doe, Commented Feb 16, 2014 at 3:00
could you update your question with the new code you're using so I can see? Also use error handlers for your other things like bindparam, execute, as they could be failing as well. Also move the % to the bindparam like this: $stmt->bind_param("s", '%'.$query.'%'); — Prix
– Prix, Commented Feb 16, 2014 at 3:37
@Prix - added new error handlers and they show I'm dying at bind_param — Jane Doe
– Jane Doe, Commented Feb 16, 2014 at 3:52
Post the error message as is so we know what is the issue, or die('Bind param failed: (' . $stmt->errno . ') ' . $stmt->error);. Also how do you expect the bindparam to use $query if you declare $query after the bindparam? It should come before it. And you haven't changed LIKE '%?%' to LIKE ? and $stmt->bind_param("s", $query) to $stmt->bind_param("s", '%'.$query.'%'); — Prix
– Prix, Commented Feb 16, 2014 at 4:15

leonardo_palma · Accepted Answer · 2014-02-16 03:27:17Z

2

Your problem is not in the while, but in the LIKE. Your SELECT should be:

"SELECT * FROM `Products` WHERE name LIKE ?"

And your $query variable:

$query = '%'.$_GET['query'].'%';

edited Feb 16, 2014 at 3:27

answered Feb 16, 2014 at 2:53

leonardo_palma

3332 gold badges4 silver badges12 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Jane Doe Over a year ago

WOW - this was secretly the answer all along. Apparently prepared statements in PHP cannot handle the ? inside %'s, which was causing the bind_param method to fail. Thank you Prix and Robert for helping me find this and sorry leonardo for not noticing this sooner.

leonardo_palma Over a year ago

Your welcome! What happen is that the "bind_param" add quotes to your variable, so on your original SELECT your LIKE was like this '%'word'%' (with quotes inside the %).

Robert · Accepted Answer · 2014-02-16 04:10:31Z

1

replace this

if($stmt = $mysqli->prepare("SELECT * FROM `Products` WHERE name LIKE '%?%'")){

with this

if($stmt = $mysqli->prepare("SELECT * FROM `Products` WHERE name LIKE ?")){

then every thing will be okay :)

the reason is when you use '%?%' php don't understand ? like need Parameter here

edited Feb 16, 2014 at 4:10

answered Feb 16, 2014 at 2:56

Robert

2,9103 gold badges29 silver badges50 bronze badges

4 Comments

Jane Doe Over a year ago

It is complaining that msqli_stmt does not have a fetch_assoc() method

Robert Over a year ago

i need to ask when U use only fetch without while loop do the code work

Jane Doe Over a year ago

When using fetch without the while loop, the code does not print anything out. Although I do know the query works because I tried it in phpmyadmin.

Robert Over a year ago

the problem in like '%?%'

Collectives™ on Stack Overflow

Getting multiple rows from a mysql query with prepared statements?

2 Answers 2

2 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related