How to retrieve certain value(s) through foreign key linking in MySQL?

Question

sorry for the unclear title but I couldn't come up with anything better.

My dilemma is this:

I have one InnoDB table in my database called "meetings" with the following structure:

• meeting_id (primary key, auto_increment)
• user1_id (foreign key pointing to a user_id in a table called "users")
• user2_id (foreign key pointing to a user_id in a table called "users")
• time (type DATETIME)
• location (type VARCHAR(200))

The table "users" is basic and looks like this:

• user_id (primary key, auto_increment)
• first_name (type VARCHAR(30))
• last_name (type VARCHAR(30))

I have a PHP file with the aim to simply print out a description of the meeting, e.g.:

You saved the following meeting information:

User 1 | User 2 | Time & Date | Meeting location

John Doe | Jane Doe | 2010-10-10 10:10:10 | New York

Now, I simply want to use the meeting ID, call my database (only the "meetings" table) and be able to get the first_name and last_name of the user1 and user2.

Right now, my non-working code looks like this:

$query = "SELECT * FROM meetings WHERE meeting_id = " . $mid;
$data  = mysqli_query($dbc, $query);
$row   = mysqli_fetch_array($data); // ANY CHANGES HERE?

...

echo '<p>You saved the following meeting information::</p>';
echo '<table>';
echo '<tr><th>User 1</th><th>User 2</th><th>Time & Date</th><th>Meeting location</th></tr>';
echo '<td>' . $row['user1_id']['first_name'] . ' ' . $row['user1_id']['last_name'] . '</td>'; // NON-WORKING
echo '<td>' . $row['user2_id']['first_name'] . ' ' . $row['user2_id']['last_name'] . '</td>'; // NON-WORKING
echo '<td>' . $row['date_time'] . '</td>';
echo '<td>' . $row['location'] . '</td>';       
echo '</table>';
...

(How) can I retrieve the first_name/last_name links without making separate calls to the "users" table? When I check phpMyAdmin, the InnoDB foreign key links seem to work fine. Many thanks in advance!

Solution 1

If your query would be

$query = "SELECT time, location, user1.first_name, user1.last_name, user2.first_name, user2.last_name FROM meetings m JOIN users user1 ON m.user1_id = user1.id JOIN users user2 ON m.user2_id = user2.id WHERE meeting_id = " . $mid;

Then the names should be available as

$row['user1.first_name']

and so on. Foreign keys are not magical devices that always bring in related records (nor should they be).

EDIT:
As a side note - having columns that end with numbers such as user1_id and user2_id normally raise a red flag in the mind of people who understand normalization and database design. Basically it boils down to the question - are you ready to accept that your meetings will support only meetings between two people and two people only? Another question that you should answer is: are you sure you want to distinguish between the 'first' and the 'second' participant of the meeting? (current design will make it harder to answer questions such as - list all the meetings for $user. with current table layout you will have to test both fields separately, which might hurt performance)

Solution 2

From the MySQL docs:

Foreign keys in SQL are used to check and enforce referential integrity, not to join tables. If you want to get results from multiple tables from a SELECT statement, you do this by performing a join between them:

SELECT * FROM t1 INNER JOIN t2 ON t1.id = t2.id;

Solution 3

Well, you have to utilize the users table to get the data from it. That's endemic. But you can have only one query, which is what I think you're after:

SELECT u1.first_name, u1.last_name, u2.first_name, u2.last_name, m.time, m.location from meetings m inner join users u1 on m.user_id1 = u1.user_id inner join users u2 on m.user_id2 = u2.user_id

Solution 4

You can't, the user1_id only contains a single number, you have to query the users table to get that information. Luckily, it can be acquired through a join:

SELECT u1.first_name, u1.last_name, u2.first_name, u2.last_name,meetings.time, meetings.location FROM meetings
JOIN users u1 ON meetings.user1_id=u1.user_id
JOIN users u2 ON meetings.user2_id=u2.user_id
WHERE meeting_id = 

View more solutions

Comments

[BeeDog]

Many thanks for the refined query, it really helps. In regards to the other comments you had on the database design, don't worry: the names and so on I had in my example are just 'simplified' variable names, my database doesn't look like that. :) Still, thanks again!

[BeeDog]

Great example! Just one follow-up question though; how do I echo the u1 first name and last name afterwards? I tried with: echo '<td>' . $row['u1.first_name'] . ' ' . '$row['u1.last_name'] . '</td>'; But it won't work. I've tried different variations of this, but I can't figure it out. If I remove the "u1." part inside the echo above, I can print out the u1 name, but for obvious reasons I can't use it to print out u2, since there is no way to differentiate between users u1 and u2.

[J V]

Last time I checked you could just do it the way you describe, but you could also say SELECT u1.first_name AS u1fn then just call up $row['u1fn'], this isn't a good way of doing it however, perhaps you could make a loop to display all contents of $row and their keys... foreach ($row as $key => $value) then find out what the proper key is like. perhaps $row['u1']['first_name'] idk I abstract the SQL from the PHP through a framework most of the time.

[BeeDog]

I tried using $row['userX.first_name'] in my script now, but it doesn't work at all. Here's my query string (with userX replaced with doctor/patient for 'better' abstraction): --- $query = "SELECT patient.first_name, patient.last_name, doctor.first_name, doctor.last_name, appointments.date_time, appointments.description FROM appointments JOIN users patient ON appointments.patient_app_id = patient.user_id JOIN users doctor ON appointments.doctor_app_id = doctor.user_id WHERE appointment_id = " . $aid; --- echo '<td>' . $row['patient.first_name'] . ' ' . $row['patient.last_name'] . '</td>';

[Unreason]

It seems safer to do SELECT time, location, user1.first_name AS user1_first_name ... and then later reference as $row['user1_first_name'] or in your case patient, etc...

[BeeDog]

Yeah, I would agree that it is not the prettiest way of doing it, but at least it's working. Thanks! I'm still baffled to why I can't get the regular userX.first_name way to work...

[BeeDog]

It's working, and since J V and you both 'recommend' it, I think I'll use it for now. Many thanks!

[J V]

The PHP manual comments say that you have to alias them like that or use $row[0] integer keys to reference the values, oh well!