CODEWITHSUNDEEP
phpmysqlsqlmysqliprepared-statement
Jane Doe
Jane Doe

Reputation: 269

Getting multiple rows from a mysql query with prepared statements?

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();
    }

Upvotes: 1

Views: 2155

Answers (2)

Robert
Robert

Reputation: 2824

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

Upvotes: 1

leonardo_palma
leonardo_palma

Reputation: 323

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'].'%';

Upvotes: 2

Related Questions