How to avoid looping sql query in php?

Question

What's the best way to make a select in this case, and output it without using nested queries in php? I would prefer to make it with a single query.

My tables look like this:

table1

id | items |
---|-------|
1  | item1 |
2  | item2 |
3  | item3 |
.. | ..    |


table2

id | itemid | comment  |
---|--------|----------|
1  | 1      | comment1 |
2  | 1      | comment2 |
3  | 2      | comment3 |
4  | 3      | comment4 |
5  | 3      | comment5 |
6  | 3      | comment6 |
.. | ..     | ..       |

This is the output i'm looking for.

item1
comment1
comment2

item2
comment3

item3
comment4
comment5
comment6

Thanks in advance

Answer 1

the SQL would look like this:

SELECT
    items,
    comment
  FROM
    table1 JOIN
    table2 ON table1.id = table2.itemid

(with minor variances based on SQL flavor)

The (pseudo) code would look something like this

var previousCategory : string;
previousCategory := "";
foreach (var item : string in list)
begin
  if (item != previousCategory)
  begin
    Output(Newline() + sqlResult["item"] + Newline());
  end
  Output(sqlResult["comment"] + Newline())
end

Sorry it's not PHP, I don't have a PHP compiler handy, so I figured a pesudo-code would work well enough.

Answer 2

Use JOINs.

SELECT table1.items,table2.comment 
FROM table1
LEFT JOIN table2 ON table2.itemid=table1.id

I used a LEFT JOIN so it will return also items having no comments. Use an INNER JOIN if you don't want that behavior. You will have some minor work on formatting the results, but you should be able to do it on your own.