How to correctly formulate a 3 table mysql query

Question

I need the best way of performing the following task. If possible, take it all the way down to giving me variables for output so i can better see whats going on, a lot of times you guys assume im smarter than i am.

Table 1, holds user information, first and last name, email etc. The primary auto_increment key in table 1 is id. Table 2, holds a series of first and last names, its key is xid, and the table 1 id (used to display only the owners list) is uid. Table 3, holds config data for the user's list in table 2. Since you can have multiple configs per list per user, it wasnt prudent to include it in table 2. So table 3 has cxid (the table 2 key of xid, marking which list its for), and then uid, marking which table 1 user can access that config. Its auto_increment key is called cid.

So to break it down

Table 1(user) - Primary key = id
Table 2(list) - Primary key = xid, and user field is uid.
Table 3(config) - Primary key = cid, list field is cxid, and user field is uid.

What i need to do, is formulate a mysql query on table 3, so that for every row, it extracts the cxid, and uid, then queries table 2 for the specific list. Once it finds the list, it needs to extract all data from that row in table 2, so that it can perform a function with that data.

Im not exactly sure where to start on writing this the most efficient way, im sure there is some kind of easy way of doing this. For the time being, the above values are just being passed in the URL, but i am going to change that to sessions, but just assume that before the query even starts, that ive defined each variable as

$variable = $_GET['variable'];

So the variables are $id, $uid, $cxid, $xid

Answer 1

This should do what you want...

SELECT *
FROM user AS [u]
JOIN list AS [l]
    ON l.uid = u.id
JOIN config AS [c]
    ON c.uid = u.id
    AND c.cxid = l.xid
WHERE u.id = $id
AND l.xid = $xid
AND c.cxid = $cxid

I would urge you to use consistent primary key names and better foreign key names to improve maintainability and predictability.

CREATE TABLE user
(
id INT NOT NULL
,CONSTRAINT pk_userId PRIMARY KEY (id)
)


CREATE TABLE list
(
id INT NOT NULL
,userId INT NOT NULL
,CONSTRAINT pk_listId PRIMARY KEY (id)
,CONSTRAINT fk_userList FOREIGN KEY (userId) REFERENCES user(id)
)


CREATE TABLE config
(
id INT NOT NULL
,userId INT NOT NULL
,listId INT NOT NULL
,CONSTRAINT pk_configId PRIMARY KEY (id)
,CONSTRAINT fk_userConfig FOREIGN KEY (userId) REFERENCES user(id)
,CONSTRAINT fk_listConfig FOREIGN KEY (listId) REFERENCES list(id)
)

Answer 2

From the way you've described it, it seems like the table relationships are:

• one user has many lists
• one list has many configs

If I understand the problem correctly, you want to go through each config entry, and retrieve the set of lists for that config.

if I use your terms, the schema looks something like:

• User(id)
• List(xid, uid)
• Config(cid, cxid, uid)

where id = uid, and xid = cxid.

And the information you want to retrieve is, for each record in Config, all of its data as well as all of the List data corresponding to that record (do you need the User data as well?)

This looks like a straight forward relationship, so you can retrieve all that data with one SQL:

select c.*, l.*, u.*
from Config c
join List l
on c.cxid = l.xid
join User u
on u.id = l.uid

This query will get you every record in the Config table, containing all the data for the List that owns that config, also containing all the data for the User who owns that list.

If you wanted to retrieve data for one specific config entry, you might append the sql to read as such (note that you only need the $cid value, as that is the deepest primary key in this relationship):

select c.*, l.*, u.*
from Config c
join List l
on c.cxid = l.xid
join User u
on u.id = l.uid
where c.cid = $cid

So, in PHP terms, you might want to approach it as such:

// here is the sql query
$query = "select c.*, l.*, u.* from Config c join List l on c.cxid = l.xid join User u on u.id = l.uid";

// here are the results from MySQL
$result = mysql_query($query);

// and now for each result, throw it into an array called $row
while($row = mysql_fetch_assoc($result))
{
  // get some data from that row, for example, Config.`data`, List.`functionname`, and User.`FirstName`
  $config_data = $row['data'];
  $function_name = $row['functionname'];
  $first_name = $row['FirstName'];

  // now do whatever you'd like with this data, before looping to the next record...
  some_function($config_data, $function_name, $first_name);
}