PDO MySQL bindParam in WHERE?

Question

I am trying to create a filter function in php, that changes an SQL based on the filters that have been set, currently I have this:

if(isset($_GET['filter1']))
{
    $arguements[] = "guide_type = 1";
}
if(isset($_GET['filter2']))
{
    $arguements[] = "guide_type = 2";
}
if(isset($_GET['filter3']))
{
    $arguements[] = "guide_type = 3";
}
if(isset($_GET['filter4']))
{
    $arguements[] = "guide_type = 4";
}
if(!empty($arguements))
{
    $filters = implode(' OR ',$arguements);

    $get_all_guides = $DB_con->prepare('SELECT * FROM customer_stories 
                                INNER JOIN customer_stories_guide_types
                                ON customer_stories.guide_type = customer_stories_guide_types.unique_id
                                WHERE :filters AND moderated = 1');

                                $get_all_guides->bindParam(':filters', $filters, PDO::PARAM_STR);

                                echo $get_all_guides->debugDumpParams();

The readout I get from $get_all_guides->debugDumpParams(); is SQL: [227] SELECT * FROM customer_stories INNER JOIN customer_stories_guide_types ON customer_stories.guide_type = customer_stories_guide_types.unique_id WHERE :filters AND moderated = 1 Params: 1 Key: Name: [8] :filters paramno=-1 name=[8] ":filters" is_param=1 param_type=2

I get no results when any of these filters are set and I don't know why

An arbitrary chunk of SQL in a string is *not* a parameter that can be bound - it has to be a specific value, e.g. `WHERE table.field = :param` — CD001, Feb 01 '17 at 13:57
You can only bind values, not logic. Your query becomes `WHERE 'guide_type = 1 OR guide_type = 2'`. Build up your query properly. — aynber, Feb 01 '17 at 13:58
Name your field as "filter[]" and then use the solution from the linked answer. — Your Common Sense, Feb 01 '17 at 14:14

score -1 · Answer 1 · answered Feb 01 '17 at 14:04

-1

This works:

WHERE '.$filters.' AND moderated = 1

instead of

WHERE :filters AND moderated = 1

answered Feb 01 '17 at 14:04

Aaranihlus

143
2
13

It should be surrounded by parenthesis because it's mixing and/or logic. – aynber Feb 01 '17 at 14:05
Are you going to say why? – Aaranihlus Feb 01 '17 at 14:08
Well, on a second thought this solution would work and it's safe. However, it is not efficient. – Your Common Sense Feb 01 '17 at 14:15

PDO MySQL bindParam in WHERE?

1 Answers1