LIKE queries using Prepare and Execute

PHP
1

What I am trying to do is take a phrase (entered in a search form), split it into individual words and then search for those words in several columns of a table. All this using the LIKE clause (I cannot use fulltext due to restrictions on my client’s hosting) and Prepare() and Execute().

This is my attempt but it only seems to search for the first word entered:


$searchphrase = $_POST['query'];
$dbh = new PDO($dsn, $user, $pass);
$searchphrase = trim($searchphrase);
$searcharray = explode(" ", $searchphrase);
for ($i = 0; $i < count($searcharray); $i++) {
    if ($i == 0) {
        $newsearchphrase = "%" . $searcharray[0] . "%";
    } else {
        $newsearchphrase .= " OR %" . $searcharray[$i] . "%";
    }
}
$sql = 'SELECT * FROM item WHERE 1=1 AND description LIKE :searchphrase OR pagetitle LIKE :searchphrase OR productcode LIKE :searchphrase';
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$stmt = $dbh->prepare($sql);
$stmt->bindParam(':searchphrase', $newsearchphrase, PDO::PARAM_STR);
$stmt->execute();

I’m sure there is something really simple I am missing. What am I doing wrong?

2

Echo the query and you’ll see.

(Hint: “OR %” = bad.)

3

even though i don’t do php, i can read it a little bit

this part –


if ($i == 0) {
     $newsearchphrase = "%" . $searcharray[0] . "%";
    } else {
     $newsearchphrase .= " OR %" . $searcharray[$i] . "%";
    }

can be simplified by starting your WHERE clause off with 1=0 instead of 1=1

with 1=0 you can add ORs freely, you don’t have to have special logic for the first one

1=1 would be used to add ANDs freely

:slight_smile:

4

r937: His code will construct a string like…
OR pagetitle LIKE %Something% OR %somethingelse% OR description LIKE …
see a missing word or two?

5

yeah, there’s that problem as well…

:slight_smile:

6

Thanks for your responses. I’m not too great on SQL syntax esp. when it comes to using LIKE. However, when I tried this query in phpmyadmin, it worked:

SELECT * FROM item WHERE 1=1 AND description LIKE '%word1%' OR '%word2' OR pagetitle LIKE '%word1%' OR '%word2' OR productcode LIKE '%word1%' OR '%word2'

Is this not what my php code is producing? I’m so confused.:frowning:

7

It may have worked, but did it actually return any matches for word2? Think of it like this:

SELECT * FROM item //Okay, open table item.
WHERE 1=1 AND  //1=1 is True.
description LIKE '%word1%' OR //Anything in description that matches word1.
'%word2' OR  //Huh? This isnt a boolean statement.
pagetitle LIKE '%word1%' OR // Anything in pagetitle that matches word1.
'%word2' OR  //Huh? This isnt a boolean statement. 
productcode LIKE '%word1%' OR // Anything in productcode that matches word1.
 '%word2'   //Huh? This isnt a boolean statement.
8

i think so, yeah, but it would be wrong

it “works” but it won’t produce the results you want

9

So it should be:

SELECT * FROM item WHERE 1=0 OR description LIKE '%word1%' OR description LIKE '%word2' OR pagetitle LIKE '%word1%' OR pagetitle LIKE '%word2' OR productcode LIKE '%word1%' OR productcode LIKE '%word2'

What is the best way to pass an array through to Prepare and Execute?

10

Well, here’s how i’d do it.


$searchstring = "description LIKE '%".implode("%' OR description LIKE '%",$searcharray)."%'";
$searchstring .= " OR pagetitle LIKE '%".implode("%' OR description LIKE '%",$searcharray)."%'";
$searchstring .= " OR productcode LIKE '%".implode("%' OR productcode LIKE '%",$searcharray)."%'";

$sql = 'SELECT * FROM item WHERE :searchphrase'; 
$dbh->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); 
$stmt = $dbh->prepare($sql); 
$stmt->bindParam(':searchphrase', $searchstring, PDO::PARAM_STR); //NOTE: I'm not 100% positive if this statement is friendly to what you're trying to do. You might need to sanitize before this point, and directly put the string into the query.)
$stmt->execute();
11

Looks like it should work but returns no results for any search

12

try

$sql = 'SELECT * FROM item WHERE '.$searchstring;

dont bind a parameter, and run the query. Just to see if it’s the binding that’s messing with it.

13

Yep that works so it’s the binding that’s the problem. What’s up with that? Surely it should be the same query?

14

The binding is probably throwing out the quote marks which is causing the query to fail. I’m trying to think of how to work around it… but short of sanitizing the array before binding it, i cant think of one using PDO… perhaps someone else can offer a suggestion?

15

Thanks so much for your help. Is there a way of displaying the exact sql query after binding? Obviously if I just do ‘echo $sql’ it displays :searchstring instead of the full query.

16

$stmt->debugDumpParams(); after your execute should do the trick…