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

2
votes

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!

4
phpmysqlforeign-keys

4 Answers

3
votes

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)

1
votes

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
1
votes

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;
1
votes

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 =