I have a problem with a query with MySQL using prepared statement with PHP. Here is the explanation, step-by-step (remove prenthesis, there are here to focus on the search term).
I have a website with a search box. A user type in it (hiver) without the parenthesis. The results give something good, it finds: (Brassin d'hiver).
The user then search again with the word (d'hiver), nothing is found.
The user then search again with ('hiver) and the search gives (Brassin d'hiver) correctly.
So it seems like there is a problem when an apostrophe is used in the search. I go in my code to look for any problems. Here is the function called when a search is triggered:
<?php
/**
* Function to search within all Beers names.
* @return array $theBeers All Beers information in an array.
*/
function searchBeers($SEARCHQUERY)
{
$SEARCHQUERY = "%".$SEARCHQUERY."%";
$theBeers = array();
// Connect to Database
$this->connect();
/* create a prepared statement */
if($stmt = $this->link->prepare("SELECT id, name FROM `beers` WHERE `beers`.`name` LIKE ? ORDER BY name ASC"))
{
/* bind parameters for markers */
$stmt->bind_param('s', $SEARCHQUERY);
/* execute query */
$stmt->execute();
/* bind variables to prepared statement */
$stmt->bind_result($col1, $col2);
/* fetch values */
$i = 0;
while($stmt->fetch())
{
$theBeers[$i]['id'] = $col1;
$theBeers[$i]['name'] = $col2;
$i++;
}
/* close statement */
$stmt->close();
return $theBeers;
}
return false;
}
?>
If I am using the phpMyAdmin GUI, I am able to easily fix this problem by adding double quotes around the searchquery: "%searchquery%". But using prepared statement, I am unable to do it. There is this ? that I have no idea what to do with it.
Note: Any other kind of search without an apostrophe wors perfectly.
Let me know if you guys want a link directly to the search to test it.