从mysql中的两个表中获取数据，join在这里不起作用 [英] getting data from two tables in mysql, join wouldn't work here

问题描述

我正在尝试从数据库中获取数据以创建复选框列表，如果特定用户上次已经检查过它们，则将它们设置为 checked="checked"..

I'm trying to get data from the database in order to create a list of checkboxes, and set them as checked="checked" if the specific user has already checked them last time...

所以我有两张表 - 一张是专业表，其中包含专业名称和专业 ID.

So I have two tables - one is the specialties table, which has the specialty name, and specialty ID.

另一个是 user_specialties，它有一个用户 ID (uid) 和专业 ID (sid)...

The other one is the user_specialties, which has a user id (uid) and specialty id (sid)...

问题是我无法获得所有专业的完整列表，以及来自 user_specialties 的相应数据 - 并且不知道要检查哪些复选框...

The thing is I can't get a complete list of all the specialties, with the corresponding data from the user_specialties - and don't know what checkboxes to check...

我尝试了以下方法:

function genspecialties($id = 0) {
  $query =  "select * from specialties s RIGHT JOIN user_specialty us on s.id = us.sid ";

此查询将使我获得与人们拥有它的次数相同的专业，每个用户多次获得相同的专业......不好:/

This query will get me the same specialty the number of times as the people have it, multiple times the same specialty for each user... not good :/

所以我想也许:

$query =  "select * from specialties s RIGHT JOIN user_specialty us on s.id = us.sid where us.uid = '$id' ";

但这也不好，因为(当然)它只带回了用户拥有的特长，而跳过了他没有的特长......

But this wasn't good either, as (of course) it only brought back the specialties that the user have, and skipped those which he do not....

那么，也许有人知道我如何获得所有专业，以及用户是否拥有的参考?我将 mysql 与 PHP 一起使用...谢谢！亚尼盘

So, maybe anyone have any idea how can I get all the specialties, and a reference if the user has it or not? I use mysql with PHP... thanks! Yanipan

推荐答案

如果我理解正确，这应该可以满足您的需求:

If I've understood correctly, this should do what you need:

$result = mysql_query("SELECT (specName, (IF EXISTS (SELECT * FROM user_specialty us WHERE s.id = us.sid AND us.uid = '$id' LIMIT 1))) FROM specialities s;");
while (list($spec, $checked) = mysql_fetch_array($result)) {
    // Do your outputting here.
}

编辑

根据您的要求，我会继续解释查询.可能让您感到困惑的部分是这个子查询:

Per your request, I'll go ahead and explain the query. The part that's probably confusing you is this subquery:

(IF EXISTS (SELECT * FROM user_specialty us WHERE s.id = us.sid AND us.uid = '$id' LIMIT 1))

IF EXISTS 告诉 MySQL 如果紧随其后的部分存在"(包含结果)，则返回 TRUE 或 FALSE.SELECT * FROM user_specialty us WHERE s.id = us.sid AND us.uid = '$id' LIMIT 1 只是告诉 MySQL 在 user_specialty 中查找一行s.id = us.sid(专业 ID 匹配)和 us.uid = '$id'(ID 是用户的).

The IF EXISTS tells MySQL to return either TRUE or FALSE if the part immediately following it 'exists' (contains a result). The SELECT * FROM user_specialty us WHERE s.id = us.sid AND us.uid = '$id' LIMIT 1 simply tells MySQL to find up a row in user_specialty where s.id = us.sid (the speciality ID matches) and us.uid = '$id' (the ID is the user's).

这篇关于从mysql中的两个表中获取数据，join在这里不起作用的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！