PHP prepared SQL with multiple LIKE condition

Question

I want to do a search in a table with search words defined by a user. I'm doing this by splitting the string an constructing the sql.

But i can't seem to make it work. It works fine, if only one word is entered, but with two or more words it's crashing.

$q = $_GET['q']; //Search word
$q = htmlspecialchars($q);
$q_exploded = explode ( " ", $q );
foreach( $q_exploded as $search_each ) {         
    $where .= "content LIKE ? OR ";
    $bind  .= "s";
    $param .= "%$search_each%, ";
} 

$where = rtrim($where,'OR ');
$param = rtrim($param,', ');

$sql = "SELECT ads_id FROM search_index WHERE ".$where."";
echo $sql . "<br>".$param."<br>".$bind."<br>";

$stmt = $dbconn->prepare($sql);
$stmt->bind_param($bind, $param);
$stmt->execute();
$result = $stmt->get_result();

while ($row = $result->fetch_assoc()) {
    echo $row['ads_id'];
}

This is my error

SELECT ads_id FROM search_index WHERE content LIKE ? OR content LIKE ?

%word1%, %word2%

ss

Warning: mysqli_stmt::bind_param(): Number of elements in type definition string doesn't match number of bind variables

what to you have inside $q ? – Yassine CHABLI Mar 29 '19 at 16:06 — Yassine CHABLI, Mar 29 '19 at 16:06
String needs single quote between them. – Eric Mar 29 '19 at 16:10 — Eric, Mar 29 '19 at 16:10
$q is the search word $q = $_GET['q']; //Search word – Christian Mar 29 '19 at 16:30 — Christian, Mar 29 '19 at 16:30
like this $param .= "'%$search_each%', "; ? – Christian Mar 29 '19 at 16:31 — Christian, Mar 29 '19 at 16:31

score 0 · Answer 1 · answered Mar 29 '19 at 16:10

0

You issue is here:

$stmt->bind_param($bind, $param);

What you're doing is:

$stmt->bind_param("ss", $param);

While you may intend param to satisfy both of the strings it doesn't you need to pass a variable for each one. I would try looking into explode for this.

answered Mar 29 '19 at 16:10

Lulceltech

1,662
11
22

I'm quite new to this. Explode how? $stmt->bind_param($bind, explode (",", $param)); – Christian Mar 29 '19 at 16:33
you'll want to explode them into an array and load them into the parameters – Lulceltech Mar 29 '19 at 17:29

score 0 · Answer 2 · answered Mar 29 '19 at 19:46

Someone posted an answer earlier, but deleted it again. That answer actually worked, i just needed to change from mySQLi to DPO.

Solution: $q = htmlspecialchars($q); $q_exploded = explode ( " ", $q );

$where = [];
$bind = [];
foreach ($q_exploded as $idx => $search_each) {         
    $key = ':val' . $idx;
    $where[] = "content LIKE " . $key;
    $bind[$key] = "%$search_each%";
}

$sql = "SELECT ads_id FROM search_index WHERE " . implode(" OR ", $where);
$stmt = $pdo_conn->prepare($sql);
$stmt->execute($bind);
$result = $stmt->setFetchMode(PDO::FETCH_ASSOC);

while ($row = $stmt->fetch()) {
    echo $row['ads_id'] . "<br>";
}

PHP prepared SQL with multiple LIKE condition

2 Answers2