CODEWITHSUNDEEP
phphtmlpostgresqlpdo
ryangus
ryangus

Reputation: 748

PHP PDO Multiple queries is Bad Practice?

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...):

Test 1 with multiple queries Test 2 with 1 query

Upvotes: 2

Views: 422

Answers (1)

Imre L
Imre L

Reputation: 6249

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]);
}

Upvotes: 4

Related Questions