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
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.
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.
精彩评论