I'm making a newsfeed type of thing and I want to select from multiple tables. The two tables I'll focus on for this question are "posts" and "photos".
我正在制作新闻源类型,我想从多个表中进行选择。我将专注于这个问题的两个表是“帖子”和“照片”。
Here's my query for just posts:
这是我对帖子的查询:
mysql_query("
SELECT * FROM posts
WHERE toID='$id' AND state='0'
ORDER BY id DESC LIMIT 10");
My posts table has the following column names:
我的帖子表有以下列名:
Table: posts
id toID fromID post state date
1 1 1 Aloha 0 1
2 1 1 Hello 0 3
My photos table has the following:
我的照片表有以下内容:
Table: photos
id userID photo state date
1 1 2 0 2
2 1 6 0 4
Expected result:
Aloha
2
Hello
6
Maybe something like:
也许是这样的:
SELECT *
(SELECT * FROM posts WHERE toID=$id AND state=0) AND
(SELECT * FROM photos WHERE userID=$id AND state=0)
ORDER BY date
When it selects these from the database it should select from where toID and userID are the same. state should equal 0 for both, (0 means visible) and they should be ordered by date. Also I need to create a new variable to pass to my query, so I can then in my php determine which table the information is coming from. Lastly I would like it to group the photos by date, so let's say a user uploaded 20 photos within a 30 minute period, they will only return one row. I use php time() to store my date.
当它从数据库中选择它们时,应该从哪里选择toID和userID是相同的。状态应该等于0,(0表示可见),它们应按日期排序。此外,我需要创建一个新的变量传递给我的查询,所以我可以在我的PHP中确定信息来自哪个表。最后我希望按照日期对照片进行分组,所以假设用户在30分钟内上传了20张照片,他们只返回一行。我使用php time()来存储我的日期。
1 个解决方案
#1
2
If you want to get all posts and photos together you could use:
如果您想将所有帖子和照片放在一起,您可以使用:
SELECT po.*, ph.* FROM posts po
LEFT JOIN photos ph
ON po.toID = ph.userID
WHERE po.state = 0
AND ph.state = 0
ORDER BY po.id DESC, ph.date DESC
#1
2
If you want to get all posts and photos together you could use:
如果您想将所有帖子和照片放在一起,您可以使用:
SELECT po.*, ph.* FROM posts po
LEFT JOIN photos ph
ON po.toID = ph.userID
WHERE po.state = 0
AND ph.state = 0
ORDER BY po.id DESC, ph.date DESC