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:
The table "users" is basic and looks like this:
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!
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)
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
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;
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 =