Grouped SQL Queries

jon3k

Does PHP have any inherent ability to handle the output of grouped queries? Or will I have to nest for loops for each member in the group by statement?

jon3k

I'm going to bump this one back to the top.

For example, if you had the query:
SELECT table1.field1, table2.field1 FROM Table1
INNER JOIN TABLE2 ON TABLE1.ID = TABLE2.TABLE1_ID
GROUP BY TABLE1.FIELD1, TABLE2.FIELD1

How would you handle the output? I come from the world of coldfusion, where you can just set a groupby argument in the cfoutput tag. Does PHP not have some functionality to handle this situation?

tsinka

Hi,

do something like this (MySQL):

$sql = "SELECT table1.field1 AS f1, table2.field1 AS f2 FROM Table1 "
      ."INNER JOIN TABLE2 ON TABLE1.ID = TABLE2.TABLE1_ID "
      ."GROUP BY f1, f2";

$res = mysql_query($sql);

while ($row = mysql_fetch_array($res)) {
  echo $row['f1']."---".$row['f2']."<br>\n";
}

Thomas

jon3k

Wouldn't that just print:

Field1Value1----Field2Value1
Field1Value1----Field2Value2
Field1Value1----Field2Value3
Field1Value1----Field2Value4

etc?

Lets say you had a database of products and categories, where products.categories_id is the FK and products.ID is the pk, pretty common scenario.

Lets say I wanted to output a new column heading for each category, then output the products listed in that category, like:

Category1
-product1
-product2
Category2
-product1
-product2
Category3
-product1
[etc...]

Would I have to store the current value of the group by field, then check it during each iteration to make sure it hadn't changed yet, and when it did, output a new column heading? I can't really think of any other way to do it.