1

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();
    }
Jane Doe
  • 269
  • 3
  • 9
  • 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 Feb 16 '14 at 02: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 Feb 16 '14 at 03: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 Feb 16 '14 at 03:37
  • @Prix - added new error handlers and they show I'm dying at bind_param – Jane Doe Feb 16 '14 at 03: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 Feb 16 '14 at 04:15
  • @Prix the problem was that the prepare function couldn't handle %?%, so I had to do the string concatenation with $query. Also, bind_param takes a reference and the string concatenation makes it a literal, so a temporary variable was needed. I will edit the OP in a second to reflect that this answer. – Jane Doe Feb 16 '14 at 04:18
  • If you have the error handlers on each part of it you would have catch that easily, also yes as I've early mentioned you had to move the % into the bind param and bind param does not take direct post/get you need to assign them to a variable. – Prix Feb 16 '14 at 04:21
  • Here is a good example you can use as reference: http://stackoverflow.com/questions/18971570/simple-php-sql-login-troubleshooting/18971788#18971788 – Prix Feb 16 '14 at 04:22

2 Answers2

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'].'%';
leonardo_palma
  • 303
  • 2
  • 4
  • 12
  • 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. – Jane Doe Feb 16 '14 at 04:03
  • 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 %). – leonardo_palma Feb 16 '14 at 05:36
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

Robert
  • 2,342
  • 2
  • 24
  • 41