1

I have a prepare statement that finds the search criteria against my database which has an Articles table and Text and Title columns inside of it.

$stmt = $pdo->prepare('SELECT * FROM articles WHERE text OR title LIKE :search');

My problem is that the statement only searches by title and completely ignores text.

I tried it like so and it works but I also need title

$stmt = $pdo->prepare('SELECT * FROM articles WHERE text LIKE :search');
Maris Tiny D
  • 13
  • 2
  • 1
    `WHERE text LIKE :search1 OR title LIKE :search2` – RiggsFolly Dec 19 '17 at 15:42
  • 2
    To search the **same** term in both columns: `.. WHERE text LIKE :search OR title like :search` – FirstOne Dec 19 '17 at 15:43
  • @FirstOne If I remember correctly you cannot use the same name i.e. `:search` twice – RiggsFolly Dec 19 '17 at 15:44
  • @FirstOne These worked for me, thanks a lot! – Maris Tiny D Dec 19 '17 at 15:45
  • @RiggsFolly It worked for me, the :search is just what my text box is called – Maris Tiny D Dec 19 '17 at 15:46
  • @RiggsFolly you can ;) You just bind once to that 'placeholder' and it will be 'assigned' to each occurrence of it in the query. – FirstOne Dec 19 '17 at 15:48
  • 1
    @RiggsFolly you can sometimes* (?) [php pdo prepare repetitive variables](https://stackoverflow.com/questions/7603896/php-pdo-prepare-repetitive-variables). My previous comment was based on a local test - as I've used it before. But then I decided to search related posts about it and found the linked question. And from [PDO::prepare](http://php.net/manual/en/pdo.prepare.php): _You cannot use a named parameter marker of the same name more than once in a prepared statement, unless emulation mode is on._ – FirstOne Dec 19 '17 at 16:01
  • @FirstOne Ahh thats it. I never use _emulation mode_ but at least now I know why I thought it was not possible. ___Thanks for the clarification___ – RiggsFolly Dec 19 '17 at 16:03

1 Answers1

0

You can use the same placeholder as many times as you want *. You just have to use proper SQL syntax. See below:

$stmt = $pdo->prepare('SELECT * FROM articles WHERE text LIKE :search OR title LIKE :search');
//                                                       ^^^^^^^^^^^^

And then just bind once to it:

$stmt->bindValue(':search', $searchTerm); // replace $searchTerm with your variable name

The value will fill both places.

I assume you're already adding % to the variable you're binding.

* From PDO::prepare:

You cannot use a named parameter marker of the same name more than once in a prepared statement, unless emulation mode is on.

So, as long as emulation mode is on, you're good to go. Otherwise, you'd have to name and bind them separatelly:

$stmt = $pdo->prepare('SELECT * FROM articles WHERE text LIKE :search1 OR title LIKE :search2');
$stmt->bindValue(':search1', $searchTerm);
$stmt->bindValue(':search2', $searchTerm);
FirstOne
  • 6,033
  • 7
  • 26
  • 45