CODEWITHSUNDEEP
phpmysqlarrayspdo
South Coast Web
South Coast Web

Reputation: 440

Whats quicker - Multiple PDO queries or Array Query

I am just wondering which way to go with a database query. I have started using PDO recently with mysql. I am writing a small script that checks for manufacturers and then it checks items against each manufacturer. I am stuck whether it would be quicker to place the items in an array and (only use 1 query) then as i loop for the manufacturer array use an array_count_values to get the item quantities or do a seperate query in the loop to count the items. I have about 400 manufacturers and 70000 items at present.

my current code using array is :

$itemquery = $conn->query("SELECT manufacturer FROM item_info_short");
$itemquery->execute();
$itemrow = $itemquery->fetchall(PDO::FETCH_ASSOC);
foreach ($itemrow as $itkey => $itvalue) {
   $items[] = $itvalue[manufacturer];
}
$it_qty = array_count_values($items);

and then for my loop :

$manu_query = $conn->query("SELECT manufacturer FROM manufacturers ORDER BY manufacturer");
while($rowsx = $manu_query->fetch(PDO::FETCH_ASSOC)){
    $rowid = $rowsx[manufacturer];
    $count = $it_qty[$rowid];
    if($count == '') $count = 0;
    echo "<option value=\"$rowsx[manufacturer]\">$rowsx[manufacturer]&emsp;$count Items</option>";
}

As you can see i use 2 PDO queries altogether. The other method would use 401 queries.

I am trying to see which method is best practise and/or quicker.

Thanks in Advance for any advice.

Upvotes: 0

Views: 159

Answers (2)

Your Common Sense
Your Common Sense

Reputation: 158009

  1. Your question is irrelevant to PDO
  2. You're doing it extremely inefficient way, but it's irrelevant to the question you've asked.
  3. The question you have to ask have to be not "which is faster" but "which is proper way".

To get count of manufacturers with cout of their goods, you have to make SQL to count them for you

SELECT manufacturer, count(*) cnt FROM item_info_short GROP BY manufacturer

will return all manufacturers with their goods count
if you want to get manufacturer details along - join this query with manufacturers table

if you need to list all manufacturers with their goods - use LEFT JOIN
something like this

SELECT m.manufacturer, count(i.manufacturer) cnt 
    FROM manufacturers m LEFT JOIN item_info_short i
    ON i.manufacturer = m.manufacturer GROUP BY m.manufacturer

Upvotes: 1

South Coast Web
South Coast Web

Reputation: 440

Thanks 'Your Common Sense' for your assistance but it still did nt show me 0 results against manufacturers that were not in the 'item_info_short' table.

SELECT m.manufacturer, 
      (SELECT COUNT(i.manufacturer) FROM item_info_short i 
                     WHERE m.manufacturer = i.manufacturer) cnt 
      FROM manufacturers m ORDER BY m.manufacturer ASC;

This was the final mysql statement I actually have used which gives me a full list of manufacturers and their quantities from item_info_short including 0 values. In answer to my own question this method is alot quicker than putting into an array first, and i believe this to be the correct way.

Upvotes: 0

Related Questions