从表中获取搜索结果并引用表

(Using MySQL and PHP)

(使用MySQL和PHP)

I have a search form that will allow my users to type in a string, and search that string on a particular criteria.

我有一个搜索表单,允许我的用户键入一个字符串,并根据特定条件搜索该字符串。

My problem is that a user needs to be able to search for information that is "spread" across multiple tables. For example:

我的问题是用户需要能够搜索跨多个表“传播”的信息。例如:

-Table "users" contains fname, lname, role, username (PK)

-Table“users”包含fname,lname,role,username(PK)

-Table "resident assistant" contains username (FK to users), building, room, region

-Table“常驻助手”包含用户名(FK到用户),建筑物,房间,区域

-Table "area coordinator" contains username (FK to users), office_bldg, office_num

-Table“区域协调员”包含用户名(FK到用户),office_bldg,office_num

And I am allowing my users to search by First Name, Last Name, Building, Region, Office # - So I will need to show results that span across multiple tables (i.e. matching records from "users" and "resident assistant")

我允许我的用户按名字,姓氏,建筑,地区,办公室#进行搜索 - 所以我需要显示跨越多个表的结果(即匹配来自“用户”和“常驻助手”的记录)

I've been experimenting with Joins and Unions, but haven't quite gotten anything useful. I am looking for the most "Universal" SQL statement to handle any search, if that's possible.

我一直在试验加入和联盟,但还没有得到任何有用的东西。我正在寻找最“通用”的SQL语句来处理任何搜索,如果可能的话。

Right now, the only way I can think of doing these searches is by a lot of processing in the PHP, for example, to find a First Name, have a query that returns username, role from "users", and then have a bunch of if statements saying, "if role is this, then search this table where username equals that..."

现在,我能想到做这些搜索的唯一方法是通过PHP中的大量处理,例如,找到一个名字,有一个返回用户名的查询,来自“用户”的角色,然后有一堆如果语句说“如果角色是这个,那么搜索这个表,其中用户名等于......”

Is there a better way to do this?

有一个更好的方法吗?

Vinko-

I am actually not getting an error, the query (with multiple joins) is just returning 0 rows.

我实际上没有收到错误,查询(具有多个连接)只返回0行。

Here is an example query that I am using:

这是我正在使用的示例查询:

select u.fname, u.lname, u.role, u.username, r.building, r.room, r.region, 
a.office, a.office_num
from 
users u 
join `ra_ca` r on (u.username = r.username) 
join `area_coord` a  on (u.username = a.username)
where
u.username = 'behrk2' and r.region = '4'

And here are my table structures:

这是我的表格结构:

CREATE TABLE `users` (
 `fname` varchar(50) NOT NULL,
 `lname` varchar(50) NOT NULL,
 `role` varchar(75) NOT NULL,
 `extension` int(4) default '6226',
 `username` varchar(25) NOT NULL,
 `password` varchar(75) NOT NULL,
 `new_pass` varchar(5) default NULL,
 PRIMARY KEY  (`username`),
 KEY `role` (`role`),
 CONSTRAINT `users_ibfk_1` FOREIGN KEY (`role`) REFERENCES `role` (`role`) ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `ra_ca` (
 `username` varchar(25) NOT NULL,
 `region` tinyint(4) NOT NULL,
 `building` varchar(75) NOT NULL,
 `room` varchar(10) NOT NULL,
 PRIMARY KEY  (`username`),
 KEY `region` (`region`),
 KEY `building` (`building`),
 CONSTRAINT `ra_ca_ibfk_9` FOREIGN KEY (`building`) REFERENCES `building` (`building`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `ra_ca_ibfk_7` FOREIGN KEY (`username`) REFERENCES `users` (`username`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `ra_ca_ibfk_8` FOREIGN KEY (`region`) REFERENCES `region` (`region`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8

CREATE TABLE `area_coord` (
 `username` varchar(25) NOT NULL,
 `region` tinyint(4) NOT NULL,
 `building` varchar(75) NOT NULL,
 `room` varchar(10) NOT NULL,
 `office` varchar(75) NOT NULL,
 `office_num` varchar(10) NOT NULL,
 PRIMARY KEY  (`username`),
 KEY `region` (`region`),
 KEY `building` (`building`),
 CONSTRAINT `area_coord_ibfk_9` FOREIGN KEY (`building`) REFERENCES `building` (`building`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `area_coord_ibfk_7` FOREIGN KEY (`username`) REFERENCES `users` (`username`) ON DELETE CASCADE ON UPDATE CASCADE,
 CONSTRAINT `area_coord_ibfk_8` FOREIGN KEY (`region`) REFERENCES `region` (`region`) ON DELETE CASCADE ON UPDATE CASCADE
) ENGINE=InnoDB DEFAULT CHARSET=utf8

And I do have values in the DB...

我确实在DB中有价值......

1 个解决方案

#1

With something like the following you'll only need to build in code the where clause. This is, the last line of the query.

使用类似下面的内容,您只需要在where子句中构建代码。这是查询的最后一行。

select u.fname, u.lname, u.role, u.username, r.building, r.room, r.region, 
a.office_bldg, a.office_num
from 
users u 
join `resident assistant` r on (u.username = r.username) 
join `area coordinator` a  on (u.username = a.username)
where
u.username = 'foo' and r.region = 'China'

EDIT:

It seems to me that you want all results no matter if there are values in all joined tables. So try left joins instead of inner joins. Try reading up on SQL to know WHAT are these queries doing.

在我看来,无论所有连接表中是否有值,您都希望获得所有结果。所以尝试左连接而不是内连接。尝试阅读SQL以了解这些查询的作用。

select u.fname, u.lname, u.role, u.username, r.building, r.room, r.region, 
a.office_bldg, a.office_num
from 
users u 
left join `resident assistant` r on (u.username = r.username) 
left join `area coordinator` a  on (u.username = a.username)
where
u.username = 'foo' and r.region = 'China'

#1