PDO best practice for query multiple fields with one variable

Question

The following results in an error because i cannot bind one var to multiple placeholders:

$search = "%somename%";
$stmt = $pdo->prepare("SELECT * FROM persons WHERE firstname LIKE :search OR lastname LIKE :search");
$stmt->bindValue(":search", $search, PDO::PARAM_STR);
$stmt->excecute();

my workaround is the following:

$search = "%somename%";
$search1 = $search;
$search2 = $search;
$stmt = $pdo->prepare("SELECT * FROM persons WHERE firstname LIKE :search1 OR lastname LIKE :search2");
$stmt->bindValue(":search1", $search1, PDO::PARAM_STR);
$stmt->bindValue(":search2", $search2, PDO::PARAM_STR);
$stmt->excecute();

I think it is not very efficient like this. I have to copy my var 2 times to be able to bind it 2 times. If i want to query 6 fields i need to copy it 6 times. My feeling is that there must be a better way.

Is there a better workaround for handling this case?

You don't need `$search1` + `$search2`, just use `$search` twice. There might be some better naming instead of `:..1`, `:..2` to inidcate that these placeholders have the same value. — djot, Sep 16 '13 at 20:49

score 1 · Accepted Answer · answered Sep 10 '13 at 09:49

1

my workaround

$pdo->setAttribute(PDO::ATTR_EMULATE_PREPARES, TRUE);

Speaking of efficiency, LIKE-based search is awfully inefficient by design. You efficiency is what you really need, then at least FULLTEXT search have to be used. Or - better - dedicated search engine like SphinxSearch.

answered Sep 10 '13 at 09:49

Your Common Sense

156,878
40
214
345

Thanks, `LIKE` was just used for example. Do i understand correct that it results only in an error because mysqls native prepare statements doesn't support it? – steven Sep 10 '13 at 09:57
Native prepared statements has nothing to do with it, I believe. It's rather PDO implementation. But yes, technically it works in emulation mode only. – Your Common Sense Sep 10 '13 at 10:05
Would you always recommend to use emulation mode for other reasons? Or should I use it only for the given case? – steven Sep 10 '13 at 10:08
Well, personally I prefer to keep the emulation mode on all the time. But I feel the community thinks otherwise. – Your Common Sense Sep 10 '13 at 10:12
For everyone who is interested: I found a thread about the question of using it always or not http://stackoverflow.com/questions/10113562/pdo-mysql-use-pdoattr-emulate-prepares-or-not – steven Sep 10 '13 at 10:26
There are no certain reasons for or against certain approach provided. Just not too meaningful opinions. – Your Common Sense Sep 10 '13 at 10:32

score 0 · Answer 2 · answered Sep 10 '13 at 09:54

0

As MySQL doesn't support nice CTEs, you could try this approach:

select 
    persons.id
    // etc etc
from 
    persons 
        join (
            select 
                :search as mat
            from 
                dual) du 
            on person.firstname like du.mat
            or person.lastname like du.mat

I am not sure how efficient it is compared to passing the same stuff over and over, but it might be a quick hack to get around it.

answered Sep 10 '13 at 09:54

Fluffeh

33,228
16
67
80

^^ this one is my favorite. "Always code as if the person who ends up maintaining your code is a violent psychopath who knows where you live." Even if I've been calmest person in the world, I'd surely become a maniac seeing *this*. For a simple search query, mind you. – Your Common Sense Sep 10 '13 at 10:03
@YourCommonSense I didn't say it was a great way, but it's probably one that no-one else would have tried. No harm in giving options :) – Fluffeh Sep 10 '13 at 10:30

PDO best practice for query multiple fields with one variable

2 Answers2