I am trying to build the logic to create a multi word LIKE statement for use with PDO.
This takes the search string $str to build the multiple parts of the LIKE section:
$str = $_POST['str'];
$keywords = preg_split('/[\s]+/', $str);
$totalKeywords = count($keywords);
$search = "%$str%";
$sql_str = " AND post_content LIKE :search0 ";
for($i=1 ; $i < $totalKeywords; $i++){
$search_bit = ":search" . $i;
$sql_str .= " AND post_content LIKE $search_bit ";
}
This is the SQL statement - with the $sql_str slotted into the correct point:
$sql = "SELECT d.ID
, d.post_date
, d.post_content
, d.post_cat_id
, d.post_label
, c.fld_cat
FROM tbl_log_days d
, tbl_log_cats c
WHERE d.post_cat_id = c.fld_id " . $sql_str . "
ORDER BY post_date";
Then for binding the variables, I have tried two approaches:
$stmt = $pdo->prepare($sql);
if (!empty($sql_str)) {
foreach ($keywords as $key => &$keyword){
$foo = '%'.$keyword.'%';
$stmt->bindParam(':search' . $key, $foo);
}
}
And also this (without the ampersand before the $keyword in the foreach line):
$stmt = $pdo->prepare($sql);
if (!empty($sql_str)) {
foreach ($keywords as $key => $keyword){
$foo = '%'.$keyword.'%';
$stmt->bindParam(':search' . $key, $foo);
}
}
However, when I search for e.g. “past hill” and check the resulting SQL that is actually run (I enabled query logging in MySQL), it only takes the last word in the search string:
SELECT d.ID
, d.post_date
, d.post_content
, d.post_cat_id
, d.post_label
, c.fld_cat
FROM tbl_log_days d
, tbl_log_cats c
WHERE d.post_cat_id = c.fld_id AND post_content LIKE '%past%' AND post_content LIKE '%past%'
ORDER BY post_date
I have done a var_dump of the $keyword variable when running a search and it returns:
string(4) "hill"
string(4) "past"
I also did a var_dump of the $key variable and confirmed it is incrementing by one each time as it is taken from the $keywords array.
I prefer to use named placeholders in the prepared statement as I have other fields in the search form not included in this example code for the sake of simplicity (e.g. category / date ranges etc). Using named placeholders makes it simpler when adding in all of the different bindParam lines.
I can’t work this one out. Is it possible to do what I’m trying to do?