Mysql多表查询语句

发布于 2024-11-09 04:29:33 字数 866 浏览 0 评论 0原文

我有一个如下表现

Ads Table
user_id ad_id   title             message   repost_days_id   repost_times_id
   1      1     Message Title 1   Message   1                1
   1      1     Message Title 2   Message   0                0

Repost Days Table:
repost_days_id   Value
1                5

Repost Times Table:
Repost_times_id   value
1                 10

在我想查询广告表以显示带有广告标题广告消息重新发布天数和重新发布时间的广告。例如，对于广告 1，它将是

Message Title 1
Message Repost Days=10
Repost Times=5.

如何为此编写 sql 语句？这就是我所做的，没有显示任何结果。

$q = "
select
    a.ad_id,
    a.title,
    a.message,
    rt.value,
    rd.value,
from
    ad_repost_times rt,
    ad_repost_days rd, 
    ads a 
where
    a.user_id='{$_SESSION['user_id']}' 
and rt.repost_times_id=a.repost_times_id 
and rd.repost_days_id=a.repost_days_id
";

原文

I have a table as follows

Ads Table
user_id ad_id   title             message   repost_days_id   repost_times_id
   1      1     Message Title 1   Message   1                1
   1      1     Message Title 2   Message   0                0

Repost Days Table:
repost_days_id   Value
1                5

Repost Times Table:
Repost_times_id   value
1                 10

Now I want to query the ads table to show ads with the ads title ads message repost days and repost times. For example it for ad 1 it will be

Message Title 1
Message Repost Days=10
Repost Times=5.

How do I write the sql statement for that? This is what I have done and is not displaying any result.

$q = "
select
    a.ad_id,
    a.title,
    a.message,
    rt.value,
    rd.value,
from
    ad_repost_times rt,
    ad_repost_days rd, 
    ads a 
where
    a.user_id='{$_SESSION['user_id']}' 
and rt.repost_times_id=a.repost_times_id 
and rd.repost_days_id=a.repost_days_id
";

分享到QQ

分享到微博

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

发布评论

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

小草泠泠 2024-11-16 04:29:33

该查询无法满足 a.user_id='{$_SESSION['user_id']}' 条件，因为 ads< 中没有 user_id 字段/代码>表。

回复收藏 0 原文

迷路的信 2024-11-16 04:29:33

字段列表末尾似乎有一个额外的逗号。这首先会导致执行查询时出现问题。确保在尝试运行会提醒您问题的查询后检查错误。
您的 a.user_id='{$_SESSION['user_id']}' 表示您在 SQL 查询中提交时，您的用户 ID 周围会有大括号。如果 $_SESSION['user_id'] 包含整数 1234，那么您的查询将类似于 a.user_id='{1234}'。（请注意，如果您在所有代码中都犯了相同的错误，则此“错误”实际上可能不是问题的原因。）
以下内容不是导致您的问题的直接原因，但出于安全原因，它很重要，它可以避免#2中的问题：您需要删除变量替换a.user_id='{$_SESSION['user_id']}'，并更改它是与 a.user_id=? 绑定的参数，以避免 SQL 注入攻击（同时也消除了导致问题 #2 的拼写错误）。
要处理 repost_days_id 和 repost_times_id 中的默认值零，您需要使用 LEFT JOIN，如下所示：
<前><代码>$q = "
选择
a.ad_id，
a.标题，
a.消息，
rt.值，
rd值
从
广告一
左连接 ad_repost_times rt ON rt.repost_times_id=a.repost_times_id
左连接 ad_repost_days rd ON rd.repost_days_id=a.repost_days_id
在哪里
a.user_id='{$_SESSION['user_id']}'
”;
对于 id 为 0 的行，rt.value 和 rd.value 的结果将为 null。
请注意，我已经对表格进行了重新排序（ads a 需要放在第一位，否则您需要在某些地方使用 RIGHT JOIN 而不是 LEFT JOIN< /code>)，并且表名之间不应再有逗号。（LEFT JOIN 替换逗号）。

It appears that you have an extra comma at the end of the field list. This would cause a problem executing your query in the first place. Make sure you're checking for errors after you try to run the query that would alert you to the problem.
Your a.user_id='{$_SESSION['user_id']}' means there will be braces around your user id when you submit it in the SQL query. If $_SESSION['user_id'] holds the integer 1234, then your query will look like a.user_id='{1234}'. (Note that this "error" may not actually be the cause of your problem if you made the same error all over your code.)
The following isn't a direct cause of your problem, but it's important for security reasons, and it would have avoided the problem in #2: you need to remove the variable substitution a.user_id='{$_SESSION['user_id']}', and change it a parameter binding with a.user_id=? in order to avoid an SQL injection attack (and also coincidentally eliminate the typo causing issue #2).
To handle the default value of zero in the repost_days_id, repost_times_id you need to use a LEFT JOIN, like so:
```
$q = "
select
    a.ad_id,
    a.title,
    a.message,
    rt.value,
    rd.value
from
    ads a
    LEFT JOIN ad_repost_times rt ON rt.repost_times_id=a.repost_times_id
    LEFT JOIN ad_repost_days rd ON rd.repost_days_id=a.repost_days_id
where
    a.user_id='{$_SESSION['user_id']}' 
";
```
The results for rt.value and rd.value will be null for rows that have 0's for the id.
Note that I have reordered the tables (ads a needs to come first, otherwise you need to use RIGHT JOIN in certain places instead of LEFT JOIN), and that there should no longer be a comma between the table names. (LEFT JOIN replaces the comma).