开发者

How to avoid looping sql query in php?

开发者 https://www.devze.com 2023-01-22 16:08 出处:网络
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.

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.

0

精彩评论

暂无评论...
验证码 换一张
取 消

关注公众号