[SOLVED] join two tables

[bradkenyon]

i have a volunteers table, which each volunteer will be assigned to a venue. i have a venue table which has the venue name w/ their own unique id (id).

 

and in the volunteers table, the venue id will be stored within the venue_id column.

 

i want to display venue name when i display that volunteer's info.

 

table for volunteers, is volunteers_2009: id, etc..., venue_id

 

table for venues, is venues: id, venue_name

 

so i would have to compare volunteers_2009.venue_id to venues.id

 

How would I properly write out the join to display the venue_name?

 

Thanks in advance, I appreciate it.

[Barand]

SELECT vol.name, ven.venue_name

FROM volunteers_2009 vol

JOIN venues ven ON vol.venue_id = ven.id

[bradkenyon]

this is my function and it seems to be printing out the same venue_name in each <td></td>, it prints out the same venues in each row of the table, it doesn't specify the venue for that appropriate volunteer, can you see what i am doing wrong?

 

<?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);
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>';
				$query_venues = "SELECT volunteers_2009.id, venues.venue_name FROM volunteers_2009 JOIN venues ON volunteers_2009.venue_id = venues.id";
				$result_venues = mysql_query($query_venues); ?>
				<td> <?php
					while($row_venues = mysql_fetch_array($result_venues)) {
						if($row_venues['venue_name'] != '') {
							// print venue assigned
							print $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>';
}
}
?>

[Barand]

As you have a loop within a loop within a loop it will print all volunteers each time it reads a volunteer in the outer loops.

[bradkenyon]

should i put the query statement outside that while loop?

[Barand]

Not sure what you are trying to achieve. For example, why the "while ($i <= 0)" loop?

[bradkenyon]

this is the code i have now.

 

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.

 

<?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>';
}
}
// -----------------------------------------------------
?>

 

 

 

[attachment deleted by admin]

[Barand]

There should be one query, one while loop

[bradkenyon]

So I could list all the general volunteers_2009 data, as well as joining the volunteers_2009 and venues table?

 

I want to get the following data:

 

table: volunteers_2009

columns: id, lname, fname, comments, venue_id

 

table: venues

columns: id, venue_name

 

I need to match volunteers_2009.venue_id to venues.id, to display venues.venue_name

 

I hope this makes sense.

[Barand]

So I could list all the general volunteers_2009 data, as well as joining the volunteers_2009 and venues table?

 

The query I posted originally was an example of that - as requested

[bradkenyon]

Thank you.

	$query = "SELECT volunteers_2009.id, volunteers_2009.lname, volunteers_2009.fname, volunteers_2009.venue_id, venues.venue_name FROM volunteers_2009 AS volunteers_2009 LEFT OUTER JOIN venues ON (volunteers_2009.venue_id = venues.id) ORDER by $order $sort";