PHP PDO Multiple queries is Bad Practice?

Question

I am working on a site and I need to populate some fields with categories, so right now I query the database multiple times:

$dataCat=new database_data();
$a=$dataCat->query_fecthAll("SELECT name,value,id FROM categories.categories WHERE categories.value LIKE ? ORDER BY id ASC",array('1'));

$b=$dataCat->query_fecthAll("SELECT name,value,id FROM categories.categories WHERE categories.value LIKE ? ORDER BY id ASC",array('2'));

$c=$dataCat->query_fecthAll("SELECT name,value,id FROM categories.categories WHERE categories.value LIKE ? ORDER BY id ASC",array('3'));

$d=$dataCat->query_fecthAll("SELECT name,value,id FROM categories.categories WHERE categories.value LIKE ? ORDER BY id ASC",array('4'));

$e=$dataCat->query_fecthAll("SELECT name,value,id FROM categories.categories WHERE categories.value LIKE ? ORDER BY id ASC",array('5'));

I get everything as expected and then I use the values on checkboxes, radios, selects, etc, for example:

echo '<select><option value="">tipo de inmueble</option>';
                            foreach($b as $x){
                                echo '<option value="'.$x['id'].'">'.ucwords($x['name']).'</option>';
                            }
                            echo '</select>';

On 1 page I need to populate more than 5 fields and it seems to me, that multiples queries is not the way to go, so I went ahead and tried some different queries like this:

SELECT value, json_agg(id || ',' || name), json_agg(concat(id,' => ',quote_literal(name))) FROM categories.categories WHERE categories.value != 'null' GROUP BY value ORDER BY value ASC

I was aiming for getting tables with arrays, but it was a pain to populated the fields that I needed it, I tough about hierarchical queries but I can't figure how to use a portion of the result for the part I need. Let's say I got 30 results from query, How can I use the first 3 rows to populate the first form element, then another portion to populate the second form element and so on.

My questions: Is it doable? Should I stick with multiple queries? Does affect performance? There is another way to go here?

Thanks in advance for any pointers.

UPDATE

Ok, I got this working now, this is the query:

$query=$dataCat->query_fecthAll("SELECT value,array_agg(id) as id,array_agg(name) as name FROM categories.categories WHERE categories.value != ? GROUP BY value ORDER BY value ASC",array('null'));

And then I process the result with PHP, the name column have different lengths so I loop a bit:

$strToRep=array('{','}');
for( $i= 0 ; $i < count($query) ; $i++ ){

    //Clean the Array results
    $cleanId=str_replace($strToRep,'',$query[$i]['id']);
    $cleanName=str_replace($strToRep,'',$query[$i]['name']);

    //Create Arrays for ID and Name
    $a=explode(',',$cleanId);
    $b=explode(',',$cleanName);

    //Combine them into one array
    $newArray[] = array_combine($a, $b);

    //reorder the array in the desire way (alphabetical ASC by value)
    asort($newArray[$i]);
    };

    //test it!
    print_r($newArray);

    //Use it!
    echo '<select required><option value="">tipo de negociacion</option>';
        foreach($newArray[0] as $key => $x){
            echo '<option value="'.$key.'">'.ucwords($x).'</option>';
        }
        echo '</select>';

All looks good, now I tested it on chrome and I got this (I'm not sure if this is the best way to know which one got better performance, first time testing performance here...):

Measure it and tell if it fits your performance demands/requirements. If it does - you're good. — zerkms
– zerkms, Commented Jul 19, 2016 at 22:46
Why not a single query with LIKE ANY (?) and then include the category in your SELECT? — Nick
– Nick, Commented Jul 19, 2016 at 23:11
the table is not that long, it's gonna be max 150 rows, but my problem is that I cannot find the way to narrow the results on any particular select or checkbox, I will check the select with any and see what I can do — ryangus
– ryangus, Commented Jul 20, 2016 at 13:11

Imre L · Accepted Answer · 2016-07-20 00:50:35Z

4

It is bad practice because there is too much code duplication.

Doing multiple queries can be justified if this is faster but usually it is not so. (You need to measure it with reasonably big data sets, )

You can always break data to suitable chunks after query:

$elements = [1, 2, 3];

$result = $dataCat->query_fecthAll('SELECT name, value, id FROM categories.categories '
        . 'WHERE categories.value IN (?' . str_repeat(', ?', count($elements) - 1)
        . ') ORDER BY id ASC', $elements);

$fields = [];
foreach ($result as $item) {
    $fields[$item['value']][] = $item;
}

// rendering elements could also be in a loop 
// but you probably need more data for element i.e name, type, ...
foreach ($elements as $elem) {
    echo renderElement($fields[$elem]);
}

answered Jul 20, 2016 at 0:50

Imre L

6,29126 silver badges32 bronze badges

Sign up to request clarification or add additional context in comments.

3 Comments

ryangus Over a year ago

yep, that's what i tough, too much duplication, I just need the name and id after the loop, I will test this code today and see how it goes, this still being multiple queries right?

Imre L Over a year ago

my example uses only 1 query total

ryangus Over a year ago

I see now, I tough it was like a loop, but it just repeating $elements, very nice, I will take this as an answer, thanks for your help.

Collectives™ on Stack Overflow

PHP PDO Multiple queries is Bad Practice?

1 Answer 1

3 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

3 Comments

Your Answer

Sign up or log in

Post as a guest

Related