PDO variables

PHP
1

How do I get this snippet (which works):

WHERE 
(t1.store = :store1 AND sub_category = :sub_cat1) OR 
(t1.store = :store2 AND sub_category = :sub_cat2) OR
(t1.store = :store3 AND sub_category = :sub_cat3)

To look something like this (which doesn’t work):

$data =
'(t1.store = :store1 AND sub_category = :sub_cat1) OR 
(t1.store = :store2 AND sub_category = :sub_cat2) OR
(t1.store = :store3 AND sub_category = :sub_cat3)';

WHERE " . $data . " some more code

I need a WHERE statement to target a varying number of stores and I can’t figure out how to
do that by turning the entire WHERE statement in to a variable using PDO.

Is that even possible in PDO?
Or, do I need separate queries for 1 store, 2 stores, 3 stores, etc?

2

Your real problem is your DB design. There is no such thing as sub category’s. Everything is a category. Some are Parents, some are Children. You need to fix your DB design before you do anything.

http://mikehillyer.com/articles/managing-hierarchical-data-in-mysql/

1 Like
3

sub_category represents a column name in a table

4

I know that. It’s wrong. See the link on how to do it correctly.

5

Query builders can help when building up complicated query strings.

But I agree that your database model could probably use some work.

6

You can’t stick the entire WHERE clause in as a PDO parameter, but you could build it to suit, and concatenate it into the query. You’d just need to figure out a way to keep track of how many parameters you need to pass in to the query.

7

Building the query string is the way I’ve done it a few times, It was a matter of thinking, and thinking hard, about what logic it would need to have, and getting values into what could be used by the control structure. Not always so easy, and prone to code bloat and obscure errors. There is good reason many prefer something like Doctrine. If this is something you may find yourself doing more often than rarely, IMHO it would be worth your time to look into learning one.

For example, something like this can “work” but has always felt hacky to me. (pseudocode)

$query_str = ''; 
// loop through tables 
    // loop through the tables fields 
        // loop through possible match values for that field 
            // $query_str .= ($table . $field . $match); 
        // 
    // 
//

* that’s only a very rough example, actual working code will be a lot more verbose and complex. Nasty stuff.

8

Thx for everyone’s help. This has rocked my world. I’ll need a few days to think and read. In addition to Hierarchical Data, I’m reading up on Data Normalization and Foreign Keys.

2 Likes
9

This topic was automatically closed 91 days after the last reply. New replies are no longer allowed.