我试图从2个表中抓取结果,其中一个具有以下结构:
CREATE TABLE `users`
(
`id` bigint (20) NOT NULL AUTO_INCREMENT ,
`firstname` varchar (255) NOT NULL,
`lastname` varchar (255) NOT NULL,
`sex` varchar (255) NOT NULL,
`orientation` varchar (50),
`email` varchar (255) NOT NULL,
`altemail` varchar (255),
`username` varchar (255) NOT NULL,
`password` varchar (255) NOT NULL,
`school` varchar (255) NOT NULL,
`age` date NOT NULL,
`image1` varchar (255),
`image2` varchar (255),
`image3` varchar (255),
`image4` varchar (255),
`image5` varchar (255),
`music` longtext,
`movies` longtext,
`tv` longtext,
`books` longtext,
`saysomething` longtext,
`course` varchar (255),
`YEAR` integer (11),
`verified` varchar (255),
`verifieddatetime` datetime,
`string` varchar (255),
`alerts` varchar (50),
PRIMARY KEY (`id`)
)
和具有以下结构的
CREATE TABLE `timetable`
(
`id` bigint (20) NOT NULL AUTO_INCREMENT ,
`studentid` varchar (255),
`classno` varchar (50),
`classname` varchar (255),
`classnumber` varchar (255),
`prof` varchar (255),
PRIMARY KEY (`id`)
)
用户名作为外键存储在时刻表表中。每个学生在课程表上登记他们的课程。尝试做一个搜索结果,生成与当前用户在同一所学校同一班级的所有人。需要返回用户表中的id、姓、名、image1字段和时间表表中的classname、classnumber。我有以下查询从同一所学校的用户获取结果:
$query = "select users.id, users.firstname, users.lastname, users.age, users.image1, users.school, category.category from users, category where users.image1 !='male.gif' and users.image1 !='female.gif' and users.verified ='yes' and users.username !='$_SESSION[myusername]' and users.school = '$school' and users.school = category.cat_id";
但是我不知道如何抓取类
我没有得到表timetable
在这个查询中变得有趣的地方-它不包括在内。
但是,我有一些设计问题/提示:
-
timetable.studentid
最好是bigint (20) NOT NULL
- 您应该为学生的性别设置一个单独的字段。我想有一张图片显示性别-
male.gif
和female.gif
?因此,这应该从性别领域推导出来,而不是反过来。 - 取决于
$_SESSION[myusername]
来自哪里,您很容易受到SQL注入。不要忘记使用mysql_real_escape()
。
SELECT
users.id,
users.firstname,
users.lastname,
users.age,
users.image1,
users.school,
category.category,
timetable.classname,
timetable.classnumber
FROM
users
INNER JOIN
category
ON
users.school = category.cat_id
INNER JOIN
timetable
ON
timetable.studentid = users.id
INNER JOIN
(
SELECT
classno
FROM
timetable
WHERE
studentid = $id_of_the_user
) classes_of_the_one_user
ON
classes_of_the_one_user.classno = timetable.classno
WHERE
users.image1 != 'male.gif'
AND
users.image1 != 'female.gif'
AND
users.verified = 'yes'
AND
users.school = '$school'