Join query and what do I do with it to display data correctly?


Join query and what do I do with it to display data correctly?



I have a table that stores all the volunteers, and each volunteer will be assigned to an appropriate venue to work the event. There is a table that stores all the venues.

It stores the volunteer's appropriate venue assignment into the column venue_id.

table: venues columns: id, venue_name  table: volunteers_2009 columns: id, lname, fname, etc.., venue_id 

Here is the function to display the list of volunteers, and the problem I am having is to display their venue assignment. I have never worked much with MySQL joins, because this is the first time I have joined two tables together to grab the appropriate info I need.

So I want it to go to the volunteers_2009 table, grab the venue_id, go to the venues table, match up volunteers_2009.venue_id to venues.id, to display venues.venue_name, so in the list it will display the volunteer's venue assignment.

alt text

<?php // ----------------------------------------------------- //it displays appropriate columns based on what table you are viewing function displayTable($table, $order, $sort) {     $query = "select * from $table ORDER by $order $sort";     $result = mysql_query($query);      // volunteer's venue query     $query_venues = "SELECT volunteers_2009.venue_id, venues.venue_name FROM volunteers_2009 JOIN venues ON volunteers_2009.venue_id = venues.id";     $result_venues = mysql_query($query_venues);      if($_POST) { ?>         <table id="box-table-a">         <tr>             <th>Name</th>             <?php if($table == 'maillist') { ?>                 <th>Email</th>             <?php } ?>             <?php if($table == 'volunteers_2008' || $table == 'volunteers_2009') { ?>                 <th>Comments</th>             <?php } ?>             <?php if($table == 'volunteers_2009') { ?>                 <th>Interests</th>                 <th>Venue</th>             <?php } ?>             <th>Edit</th>         </tr>         <tr>         <?php         while($row = mysql_fetch_array($result))         {             $i = 0;             while($i <=0)             {                 print '<td>'.$row['fname'].' '.$row['lname'].'</td>';                 if($table == 'maillist') {                     print '<td><a href="mailto:'.strtolower($row['email']).'">'.strtolower($row['email']).'</a></td>';                 }                 if($table == 'volunteers_2008' || $table == 'volunteers_2009') {                     print '<td><small>'.substr($row['comments'], 0, 32).'</small></td>';                 }                 if($table == 'volunteers_2009') {                     print '<td><small>1) '.$row['choice1'].'<br>2) '.$row['choice2'].'<br>3) '.$row['choice3'].'</small></td>'; ?>                     <td> <?php                             if($row_venues['venue_name'] != '') {                                 // print venue assigned                                 print $row_venues['venue_id'].' '.$row_venues['venue_name'].' ';                             } else { print 'No Venue Assigned'; } ?>                     </td> <?php                 } ?>                 <td><a href="?mode=upd&id=<?= $row[id] ?>&table=<?= $table ?>">Upd</a> / <a href="?mode=del&id=<?= $row[id] ?>&table=<?= $table ?>" onclick="return confirm('Are you sure you want to delete?')">Del</a></td> <?php                 $i++;             }         print '</tr>';         }         print '</table>';     } } // ----------------------------------------------------- ?> 



Tree library for PHP using left & right ids

1:



PHP MYSQL - Many data in one column
General SQL LEFT Outer join syntax..
MySQL, PHP - Forms Help
SELECT volunteers.id, volunteers.lname, volunteers.fname, volunteers.venue_id, venues.venue_name FROM Volunteers_2009 AS Volunteers LEFT OUTER JOIN Venues ON (Volunteers.venue_id = Venues.id).
multiple keyword search script needs some expert input


PHP MYSQL - Explode help


Can I compare MySQL enums?

2:



SQL query to get greatest distinct date - kind of
select field1, field2, field3 from venues, volunteers_2009 where volunteers_2009.venue_id = venues.id.
Backing up a huge MySQL table using PHP and a cronjob


3:


I cannot add any comment, but the syntax select field1, field2, field3 from venues, volunteers_2009 where volunteers_2009.venue_id = venues.id will only gave you volunteers with a venue; not those without a venue..



76 out of 100 based on 66 user ratings 766 reviews

*