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();
}
$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 canecho $name, " - ", $age, "\n;and at your query they must be present likeSELECT 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.$stmt->bind_param("s", '%'.$query.'%');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 changedLIKE '%?%'toLIKE ?and$stmt->bind_param("s", $query)to$stmt->bind_param("s", '%'.$query.'%');