0

I am making a search box feature that has this query:

$query="SELECT * FROM article WHERE title LIKE ? OR description LIKE ?";

Then in a foreach loop, I make this array:

$params[]= "%".$keyword."%";
$params[]= "%".$keyword."%";

Then I execute: 

$stmt=$cxn->prepare($query);
$stmt->execute($params);

This WORKS, but...

If, after the foreach loop, I want to add " LIMIT ?,?", I do this:

$query.=" LIMIT ?, ?";
$params[]=$row_number;
$params[]=$items_per_page;

Then I execute the script, and it throws this Fatal Error:

PHP Fatal error:  Uncaught exception 'PDOException' with message 
'SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; 
 check the manual that corresponds to your MySQL server version for the right syntax
 to use near ''0', '2'' at line 1' in ..../index.php:247
  Stack trace:
  #0 .../index.php(247): PDOStatement->execute(Array)
 #1 {main}
 thrown in ..../index.php on line 247

I tried to do add apostrophes around the keywords: $params= "'%".$keyword."%'"; But this didn't work.

Also, I tried the exact same query inside PHPMyAdmin and it worked.

Does anyone know why this is throwing an error?

Thanks a lot in advance.

alexx0186
  • 1,557
  • 5
  • 20
  • 32

3 Answers3

3

Update:

$query="SELECT * FROM article WHERE title LIKE ? OR description LIKE ?";
$query.=" LIMIT ?, ?";
$stmt=$cxn->prepare($query);
$stmt->bindValue(1, "%".$keyword."%");
$stmt->bindValue(2, "%".$keyword."%");
$stmt->bindValue(3, $row_number, PDO::PARAM_INT);
$stmt->bindValue(4, $items_per_page, PDO::PARAM_INT);

$stmt->execute();
xdazz
  • 158,678
  • 38
  • 247
  • 274
  • sir question, can you mix data types in `PHP` inside array? :) – John Woo Sep 04 '12 at 02:01
  • Hi, thanks for your response. Unfortunately, this didn't work. I am trying to figure out how to mix up Bindvalues, along with `?`'s in my code. I got this code from a suggested thread: `$cxn->bindValue(':row_number', (int) trim($row_number), PDO::PARAM_INT);` – alexx0186 Sep 04 '12 at 02:42
  • @alexx0186 If the previous way does not work, then you need to set the datatype explicitly. Check my updated answer. – xdazz Sep 04 '12 at 02:47
  • Thanks a lot for that. The datatype was the soruce of the problem. Regards – alexx0186 Sep 04 '12 at 03:59
1

The reason LIKE isn't working, is because when you use bind parameters with LIKE it's treating '%value%' as a string.

You're going to have to use:

$query="SELECT * FROM article WHERE title LIKE CONCAT('%',?,'%') OR description LIKE CONCAT('%',?,'%')";
Gavin Towey
  • 3,132
  • 15
  • 11
  • Hi thanks for your response. The first SQL query in my post does work. The trouble begins when I add the " LIMIT ?,?" at the end of the query. Regards – alexx0186 Sep 04 '12 at 02:28
0

I think you need to convert the variable you have passed in your limit clause. see this ...to use near ''0', '2'' there are single quotes on the values in the LIMIT clause

try 

$query.=" LIMIT ?, ?";
$params[] = intval($row_number);
$params[] = intval($items_per_page);
John Woo
  • 258,903
  • 69
  • 498
  • 492
  • Hi, thanks for your response. I tried this, but couldn't get it to work. I am trying to figure out how I can mix BindValues along with `?`'s. Regards – alexx0186 Sep 04 '12 at 02:38
  • @alexx0186 why dont you do it like this? one parameter per value (*i mean don't use array*) `$stmt->bindParam(':name', $name); $stmt->bindParam(':value', $value);` – John Woo Sep 04 '12 at 02:44
  • Hi, the reason I'm not sure I can do it like this, is because I don't know how many keywords the user is gonna type. So with my foreach loop, I just keep adding as many `?`'s as needed (instead of binding values). Not sure I can do this with BindParam..? – alexx0186 Sep 04 '12 at 03:09