In general, I'm trying to display the corresponding names of three IDs in three fields of an entry...
I have a table called userdestinations, which has 4 fields: email (which is essentially username), first_bar, second_bar, and third_bar (each are integers representing ID numbers of bars) and date (which really isn't important in this case but is an integer of the form date("Ymd")). I also have a table called barlist, which has many fields including ID and bar_name.
Now, I want to display a list of users and the bars that they are going to (information in userdestinations) but since userdestinations contains bar IDs instead of bar names, I need to get the bar_name for each of the three IDs for each person.
What I am really looking for here is a single query statement that is able to do this.
I 'can' do this using a loop of queries during table creation, but since that probably isn't an efficient way of doing things, I really want to figure out how to do it properly. However, I figure that posting how I did it might help people understand what I am trying to accomplish. So here is how I do this with looping queries, which I want to be able to do in one query:
//This is up outside of the page's html
<?php
$email = $_SESSION['username'];
$query_users_list = "SELECT `email` , `first_bar` , `second_bar` , `third_bar` FROM `userdestinations` WHERE (`first_bar` = '$selected_bar_id' OR `second_bar` = '$selected_bar_id' OR `third_bar` = '$selected_bar_id') $dates";
$users_list = mysql_query($query_users_list, $connBarHopper) or die(mysql_error());
$row_users_list = mysql_fetch_assoc($users_list);
?>
//this part is actually in the middle of the html right where a table is being created
<? do {
$first_bar = $row_users_list["first_bar"];
$second_bar = $row_users_list["second_bar"];
$third_bar = $row_users_list["third_bar"];
$query_first_user_bar = "SELECT `bar_name` FROM `barlist` WHERE `id` = '$first_bar'";
$first_user_bar = mysql_query($query_first_user_bar, $connBarHopper) or die(mysql_error());
$row_first_user_bar = mysql_fetch_assoc($first_user_bar);
$query_second_user_bar = "SELECT `bar_name` FROM `barlist` WHERE `id` = '$second_bar'";
$second_user_bar = mysql_query($query_second_user_bar, $connBarHopper) or die(mysql_error());
$row_second_user_bar = mysql_fetch_assoc($second_user_bar);
$query_third_user_bar = "SELECT `bar_name` FROM `barlist` WHERE `id` = '$third_bar'";
$third_user_bar = mysql_query($query_third_user_bar, $connBarHopper) or die(mysql_error());
$row_third_user_bar = mysql_fetch_assoc($third_user_bar);
?>
<tr>
<td> <a href="user_profile.php?useremail=<? echo $row_users_list['email']; ?>"><? echo $row_users_list['email']; ?></a></td>
<td> <a href="bar_info.php?barid=<? echo $row_users_list["first_bar"]; ?>"><? echo $row_first_user_bar["bar_name"]; ?> </a></td>
<td> <a href="bar_info.php?barid=<? echo $row_users_list["second_bar"]; ?>"><? echo $row_second_user_bar["bar_name"]; ?> </a></td>
<td> <a href="bar_info.php?barid=<? echo $row_users_list["third_bar"]; ?>"><? echo $row_third_user_bar["bar_name"]; ?> </a></td>
</tr>
<? }
while ($row_users_list = mysql_fetch_assoc($users_list)) ;
?>
I hope that makes my goal clear. I want to be able to do that without doing potentially hundreds or thousands of queries each time someone wants to view the information.
If anyone needs any more information or clarification, please let me know, but I didn't want to make the post too large if I could help it. Any help is appreciated.
