Databases
StackOverflow2013
Postsdbo
POJoin query and what do I do with it to display data correctly?
Body
 

Note that there are some explanatory texts on larger screens.

plurals
  1. POJoin query and what do I do with it to display data correctly?
    text
    copied!<p>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.</p> <p>It stores the volunteer's appropriate venue assignment into the column <code>venue_id</code>.</p> <pre><code>table: venues columns: id, venue_name table: volunteers_2009 columns: id, lname, fname, etc.., venue_id </code></pre> <p>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.</p> <p>So I want it to go to the volunteers_2009 table, grab the venue_id, go to the venues table, match up <code>volunteers_2009.venue_id to venues.id</code>, to display <code>venues.venue_name</code>, so in the list it will display the volunteer's venue assignment.</p> <p><a href="http://i35.tinypic.com/16a1cgw.jpg" rel="nofollow noreferrer">alt text http://i35.tinypic.com/16a1cgw.jpg</a></p> <pre><code>&lt;?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) { ?&gt; &lt;table id="box-table-a"&gt; &lt;tr&gt; &lt;th&gt;Name&lt;/th&gt; &lt;?php if($table == 'maillist') { ?&gt; &lt;th&gt;Email&lt;/th&gt; &lt;?php } ?&gt; &lt;?php if($table == 'volunteers_2008' || $table == 'volunteers_2009') { ?&gt; &lt;th&gt;Comments&lt;/th&gt; &lt;?php } ?&gt; &lt;?php if($table == 'volunteers_2009') { ?&gt; &lt;th&gt;Interests&lt;/th&gt; &lt;th&gt;Venue&lt;/th&gt; &lt;?php } ?&gt; &lt;th&gt;Edit&lt;/th&gt; &lt;/tr&gt; &lt;tr&gt; &lt;?php while($row = mysql_fetch_array($result)) { $i = 0; while($i &lt;=0) { print '&lt;td&gt;'.$row['fname'].' '.$row['lname'].'&lt;/td&gt;'; if($table == 'maillist') { print '&lt;td&gt;&lt;a href="mailto:'.strtolower($row['email']).'"&gt;'.strtolower($row['email']).'&lt;/a&gt;&lt;/td&gt;'; } if($table == 'volunteers_2008' || $table == 'volunteers_2009') { print '&lt;td&gt;&lt;small&gt;'.substr($row['comments'], 0, 32).'&lt;/small&gt;&lt;/td&gt;'; } if($table == 'volunteers_2009') { print '&lt;td&gt;&lt;small&gt;1) '.$row['choice1'].'&lt;br&gt;2) '.$row['choice2'].'&lt;br&gt;3) '.$row['choice3'].'&lt;/small&gt;&lt;/td&gt;'; ?&gt; &lt;td&gt; &lt;?php if($row_venues['venue_name'] != '') { // print venue assigned print $row_venues['venue_id'].' '.$row_venues['venue_name'].' '; } else { print 'No Venue Assigned'; } ?&gt; &lt;/td&gt; &lt;?php } ?&gt; &lt;td&gt;&lt;a href="?mode=upd&amp;id=&lt;?= $row[id] ?&gt;&amp;table=&lt;?= $table ?&gt;"&gt;Upd&lt;/a&gt; / &lt;a href="?mode=del&amp;id=&lt;?= $row[id] ?&gt;&amp;table=&lt;?= $table ?&gt;" onclick="return confirm('Are you sure you want to delete?')"&gt;Del&lt;/a&gt;&lt;/td&gt; &lt;?php $i++; } print '&lt;/tr&gt;'; } print '&lt;/table&gt;'; } } // ----------------------------------------------------- ?&gt; </code></pre>
 

Querying!

 
Guidance
An individual column

Larger individual text columns get their own page to allow for proper reading.

SQuiL has stopped working due to an internal error.

If you are curious you may find further information in the browser console, which is accessible through the devtools (F12).

Reload