MySQL 查询连接未完全检索

发布于 2024-11-19 14:55:45 字数 658 浏览 0 评论 0原文

在我的网站上，我正在尝试编写一个类似于 Facebook 和 Twitter 时间线的功能，用户可以“关注”另一个用户，并接收他们的“广播”。数据库表是：

**members**
--------
id
fullname
following

**broadcasts**
-----------
id
mem_id
broadcast (the content)
broadcast_date

成员表中的“以下”是存储用户 ID 的 varchar 文本。因此，如果我关注用户 4、5 和 6 (4,5,6,) 将出现在以下列中。我的问题是，我从数据库中查询的内容只是从我关注的第一个用户检索“广播”，而不是其他用户。

$sql_broadcasts = mysql_query("
    SELECT *
    FROM members 
        JOIN broadcast 
            ON(broadcast.mem_id = members.following)
    WHERE members.id=$id
    ORDER BY broadcast_date DESC
    LIMIT 10;
");

其中 $id 是 $_SESSION['id']。我盯着这段代码看了很长时间，有人能发现我做错了什么吗？提前致谢

原文

On my website I am trying to program a feature, similar to facebook and twitters timeline, where a user can 'follow' another user, and receive their 'broadcast'. The database tables are:

**members**
--------
id
fullname
following

**broadcasts**
-----------
id
mem_id
broadcast (the content)
broadcast_date

"following" in the members table is a varchar text that stores user ID's. So if I'm following users 4 5 and 6 (4,5,6,) would appear in the following column. My problem is, what I'm querying from the database, it's only retrieving the 'broadcasts' from the first user that I am following and no other user else.

$sql_broadcasts = mysql_query("
    SELECT *
    FROM members 
        JOIN broadcast 
            ON(broadcast.mem_id = members.following)
    WHERE members.id=$id
    ORDER BY broadcast_date DESC
    LIMIT 10;
");

where $id is $_SESSION['id']. I've been staring at this code for a long time, can anyone spot what I'm doing wrong? thanks in advance

分享到QQ

分享到微博

如果你对这篇内容有疑问，欢迎到本站社区发帖提问参与讨论，获取更多帮助，或者扫码二维码加入 Web 技术交流群。

发布评论

需要登录才能够评论，你可以免费注册一个本站的账号。

随梦而飞# 2024-11-26 14:55:45

联接不是这样工作的。

假设broadcast.mem_id是数字类型，数据库将默默地转换members.follower_array；这将从 4、5、6 中生成 4，因此有一条记录匹配。

您将需要 am:n 关系才能正常工作，一张包含成员的表（您已经拥有）；另一个包含关注者的表，为每个关注者插入一条记录，其中包含正在关注的成员的成员 ID 和正在关注的成员的成员 ID。这样一个成员就可以有 0-n 个关注者。

Table member
id

Table follower
member_id
member_id_follower

然后你可以做类似的事情

SELECT
  ...
FROM
   member AS mb
JOIN
   follower AS fl ON fl.member_id = mb.id
WHERE
   mb.id = 123

Joins do not work that way.

Assuming that broadcast.mem_id is a numeric type the database will silently cast members.follower_array; this will make 4 from 4,5,6, so one record matches.

You will need a m:n relation for this to work, one table with members (which you already have); and another table with followers, for each follower you insert a record which contains the member id of the member who is being and the member id of the member who is following. That way a member can have 0-n followers.

Table member
id

Table follower
member_id
member_id_follower

Then you can do something like

SELECT
  ...
FROM
   member AS mb
JOIN
   follower AS fl ON fl.member_id = mb.id
WHERE
   mb.id = 123

回复收藏 0 原文

听风念你 2024-11-26 14:55:45

首先，您必须在子查询中获取所有以下 id 并将其传递给父查询！

$sql_broadcasts = mysql_query("SELECT *
FROM broadcast where mem_id IN( Select following from members where id = $id)
ORDER BY broadcast_date DESC
LIMIT 10;
");

其中以下字段包含所有以下 id 逗号分割，例如 4,5,6

First of all you have to get all following ids in sub query and passing it to parent one!

$sql_broadcasts = mysql_query("SELECT *
FROM broadcast where mem_id IN( Select following from members where id = $id)
ORDER BY broadcast_date DESC
LIMIT 10;
");

where following filed contains all following ids comma split e.g 4,5,6

回复收藏 0 原文

~没有更多了~