Most of the queries I have on my PHP pages do not accept user input, so I haven't used prepared statements for any of them, just the standard MySQLi->query method. For the search function, I need to sanitize my inputs.
My search function, however, does not work. No error gets thrown, every method returns true, but $res->num_rows is always 0. The query gets executed succesfully from phpMyAdmin.
function searchArticles($query, $page, $othpage){
$db = new mysqli($db_host, $db_user, $db_pswd, $db_name);
if($db){
$q = "SELECT id, title, summary, timestamp, author, urltitle
FROM articles
WHERE title LIKE ? OR summary LIKE ? OR content LIKE ? OR tags LIKE ?";
$stmt = $db->prepare($q);
$query = '\'%'.$query.'%\'';
$stmt->bind_param("ssss", $query, $query, $query, $query);
$stmt->execute();
$res = $stmt->get_result();
$textfill = "";
$total_rows = $res->num_rows;
if($total_rows > 0){
while($a = $res->fetch_assoc()){
/* Doing stuff */
}
}
else {
$textfill .= "No results found";
}
$db->close();
/* Other stuff */
return $textfill;
}
return "";
}
I quickly bodged it to run with the classic query and it works flawlessly.
function searchArticles($query, $page, $othpage){
$db = new mysqli($db_host, $db_user, $db_pswd, $db_name);
if($db){
$q = "SELECT id, title, summary, timestamp, author, urltitle
FROM articles
WHERE title LIKE ? OR summary LIKE ? OR content LIKE ? OR tags LIKE ?";
$query = '\'%'.$query.'%\'';
$newq = str_replace('?', $query, $q);
$res = $db->query($newq);
$textfill = "";
$total_rows = $res->num_rows;
if($total_rows > 0){
while($a = $res->fetch_assoc()){
/* Doing stuff */
}
}
else {
$textfill .= "No results found";
}
$db->close();
/* Other stuff */
return $textfill;
}
return "";
}
What is wrong with my code? Why does it work the with "unsecure" method and it does not with the prepared statement?
