CODEWITHSUNDEEP
phpmysqlpdo
TIm
TIm

Reputation: 481

PHP PDO prepared statement -- MySQL LIKE query

I am trying to do a search through php's PDO class (mysql driver). I have the following query working with the MySQL client (table names changed to protect the innocent):

SELECT    hs.hs_pk, 
          hs.hs_text, 
          hs.hs_did, 
          hd.hd_did, 
          hd.hd_text, 
          hv.hv_text, 
          hc.hc_text 
FROM      hs 
LEFT JOIN hd 
 ON       hs.hs_did = hd.hd_did 
LEFT JOIN hd 
 ON       hd.hd_vid = hv.hv_id 
LEFT JOIN hc 
 ON       hd.hd_pclass = hc.hc_id
WHERE     hs.hs_text LIKE "%searchTerm%"
LIMIT 25;

This works like a charm regardless of the search term that I use. However, when I move to php, I can't get it to return anything. I have tried several different syntaxes that seem logical to work, but nothing I have tried works. here's my existing code:

$handle = fopen('/foo/bar/test.log', 'w+');
fwrite($handle, "doSearch, with search term: $searchTerm\n");
$sql = 
'SELECT   hs.hs_pk, 
          hs.hs_text, 
          hs.hs_did, 
          hd.hd_did, 
          hd.hd_text, 
          hv.hv_text, 
          hc.hc_text 
FROM      hs 
LEFT JOIN hd 
 ON       hs.hs_did = hd.hd_did 
LEFT JOIN hd 
 ON       hd.hd_vid = hv.hv_id 
LEFT JOIN hc 
 ON       hd.hd_pclass = hc.hc_id
WHERE     hs.hs_text LIKE :searchTerm
LIMIT 25';

try {
 $dbh = new PDO('mysql:host=localhost;dbname=awdb', "user", "password");
 fwrite($handle, "connected to DB\n");
 $prep = $dbh->prepare($sql);
 $ret = $prep->execute(array(':searchTerm' => '"%'.$searchTerm.'%"'));

 while ($row = $prep->fetch(PDO::FETCH_ASSOC)) {
  $i++;
  $result[$i]['subText'] = $row['hs_pk'];
  $result[$i]['subText'] = $row['hs_text'];
  $result[$i]['subDid'] = $row['hs_did'];
  $result[$i]['devDid'] = $row['hd_did'];
  $result[$i]['devText'] = $row['hd_text'];
  $result[$i]['vendorText'] = $row['hv_text'];
  $result[$i]['classText'] = $row['hc_text'];
 }
    $dbh = null;
}   
catch (PDOException $e) {
  print "Error!: " . $e->getMessage() . "<br/>";
  die();
}

I've tried the following as well (SQL WHERE clause & prep->execute lines are all that change):

WHERE hs.hs_text LIKE CONCAT(\'%\', ?, \'%\') 
$ret = $prep->execute(array($searchTerm));

WHERE hs.hs_text LIKE "%:searchTerm%" 
$ret = $prep->execute(array(':searchTerm' => $searchTerm));

WHERE hs.hs_text LIKE ":searchTerm" 
$ret = $prep->execute(array(':searchTerm' => '%'.$searchTerm.'%'));

etc...

Upvotes: 41

Views: 42608

Answers (3)

Micah
Micah

Reputation: 5

$prep = $dbh->prepare($sql);
$ret = $prep->execute(array('searchTerm' => $searchTerm));

Upvotes: -2

TIm
TIm

Reputation: 481

Well, I solved this one. And quite frankly, I'm an idiot... Thank you all for seeing this and giving good feedback. The problem was a typo in a table name (which I changed, so nobody here would be able to see my issue to begin with...). The suggestions did lead me to find the issue, though, so thank you adam, jkndrkn and troelskn.

For the record, the following combination works well:

WHERE aw_hcl_subdevices.hs_text LIKE CONCAT(\'%\', ?, \'%\')
$ret = $prep->execute(array($searchTerm));

Upvotes: -3

troelskn
troelskn

Reputation: 117437

$ret = $prep->execute(array(':searchTerm' => '"%'.$searchTerm.'%"'));

This is wrong. You don't need the double quotes.

WHERE hs.hs_text LIKE ":searchTerm" 
$ret = $prep->execute(array(':searchTerm' => '%'.$searchTerm.'%'));

This is also wrong. Try with:

$prep = $dbh->prepare($sql);
$ret = $prep->execute(array(':searchTerm' => '%'.$searchTerm.'%'));

Explanation: Prepared statements don't simply do a string-replace. They transport the data completely separate from the query. Quotes are only needed when embedding values into a query.

Upvotes: 95

Related Questions