StackOverflow2013

Note that there are some explanatory texts on larger screens.

plurals

POGetting search results from table and referencing tables
text
Body
copied!<p>(Using MySQL and PHP)</p> <p>I have a search form that will allow my users to type in a string, and search that string on a particular criteria.</p> <p>My problem is that a user needs to be able to search for information that is "spread" across multiple tables. For example:</p> <p>-Table "users" contains fname, lname, role, username (PK)</p> <p>-Table "resident assistant" contains username (FK to users), building, room, region</p> <p>-Table "area coordinator" contains username (FK to users), office_bldg, office_num</p> <p>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")</p> <p>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. </p> <p>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..."</p> <p>Is there a better way to do this? </p> <hr> <p>Vinko-</p> <p>I am actually not getting an error, the query (with multiple joins) is just returning 0 rows.</p> <p>Here is an example query that I am using:</p> <pre><code>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' </code></pre> <p>And here are my table structures:</p> <pre><code>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 </code></pre> <p>And I do have values in the DB...</p>

Querying!

Guidance

An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload