[SOLVED] Accessing elements from a join query

[ninedoors]

Is this the right way to access the array elements of my join query?

 

<?php
//start the table
echo "<table width=600px border=2 bordercolor=#001E5B cellpadding=2 cellspacing=1>";

//print the proper column names in table headers:
echo "\n<tr>".
 "\n\t<th><font size=2>ID</font></th>".
 "\n\t<th><font size=2><a href=main_update.php?change=name&number=$num>Movie</a></font></th>".
 "\n\t<th><font size=2><a href=main_update.php?change=loc&number=$num>Location</font></th>".
 "\n\t<th><font size=2><a href=main_update.php?change=type&number=$num>Type</font></th>".
 "\n\t<th><font size=2><a href=main_update.php?change=imdb&number=$num>IMDB Link</font></th>".
 "\n\t<th><font size=2><a href=main_update.php?change=cast&number=$num>Cast</font></th>".
 "\n\t<th><font size=2><a href=main_update.php?change=genre&number=$num>Genres</font></th>".
 "\n</tr>";

//get info from movies table
$query = "SELECT movies.id, movies.name, movies.location, movies.type, movies.thumb_path, movies.imdb_link, 
		roles.actor1, roles.actor2, roles.actor3, roles.actor4, roles.actor5, genres.drama, genres.documentary, 
		genres.comedy, genres.action, genres.adventure, genres.family, genres.horror, genres.romance, genres.sport, 
		genres.scifi, genres.tv FROM (movies LEFT JOIN roles ON movies.id = roles.id) LEFT JOIN genres ON movies.id = genres.id 
		LIMIT $start, $limit";
$result = mysql_query($query);
while ($row = mysql_fetch_assoc($result))
{
$id = $row['movies.id'];
$movie = stripslashes($row['movies.name']);
$location = $row['movies.location'];
$link = $row['movies.imdb_link'];
$pic = $row['movies.thumb_path'];
$type = $row['movies.type'];

//get cast
$cast = array();
if($row['roles.actor1'] != 'none')	{
								$cast[] = "<a href=view/cast.php?actname=".$row['roles.actor1'].">".$row['roles.actor1']."</a>";									
								}
if($row['roles.actor2'] != 'none')	{
								$cast[] = "<a href=view/cast.php?actname=".$row['roles.actor2'].">".$row['roles.actor2']."</a>";
								}
if($row['roles.actor3'] != 'none')	{
								$cast[] = "<a href=view/cast.php?actname=".$row['roles.actor3'].">".$row['roles.actor3']."</a>";
								}
if($row['roles.actor4'] != 'none')	{
								$cast[] = "<a href=view/cast.php?actname=".$row['roles.actor4'].">".$row['roles.actor4']."</a>";
								}
if($row['roles.actor5'] != 'none')	{
								$cast[] = "<a href=view/cast.php?actname=".$row['roles.actor5'].">".$row['roles.actor5']."</a>";
								}
$cast_string = implode(', ', $cast);

//get genres
$genre = array();
if($row['genres.action'] == 1)	{
							$genre[] = "<a href=view/genre.php?genrename=action>Action</a>";
							}
if($row['genres.adventure'] == 1)	{
								$genre[] = "<a href=view/genre.php?genrename=adventure>Adventure</a>";
								}
if($row['genres.comedy'] == 1)	{
							$genre[] = "<a href=view/genre.php?genrename=comedy>Comedy</a>";
							}
if($row['genres.documentary'] == 1)	{
								$genre[] = "<a href=view/genre.php?genrename=documentary>Documentary</a>";
								}
if($row['genres.drama'] == 1)	{
							$genre[] = "<a href=view/genre.php?genrename=drama>Drama</a>";
							}
if($row['genres.family'] == 1)	{
							$genre[] = "<a href=view/genre.php?genrename=family>Family</a>";
							}
if($row['genres.horror'] == 1)	{
							$genre[] = "<a href=view/genre.php?genrename=horror>Horror</a>";
							}
if($row['genres.romance'] == 1)	{
							$genre[] = "<a href=view/genre.php?genrename=romance>Romance</a>";
							}
if($row['genres.sport'] == 1)	{
							$genre[] = "<a href=view/genre.php?genrename=sport>Sport</a>";
							}
if($row['genres.scifi'] == 1)	{
							$genre[] = "<a href=view/genre.php?genrename=scifi>Sci-Fi</a>";
							}
if($row['genres.tv'] == 1)	{
						$genre[] = "<a href=view/genre.php?genrename=tv>TV</a>";
						}
$genre_string = implode(', ', $genre);

//bulid rest of table
echo 	'<tr>
		<td align="center" class="geor12"><a href=main_update.php?number='.$id.'><img src="thumbnails/'.$pic.'" border=0></a></td>
		<td align="center" class="geor12">$movie</td>
		<td align="center" class="geor12">$location</td>
		<td align="center" class="geor12">$type</td>
		<td align="center" class="geor12"><a href="$link" target=_blank>$movie</a></td>
		<td align="center" class="geor12">'.$cast_string.'</td>
		<td align="center" class="geor12">'.$genre_string.'</td>
	</tr>';
}//end while loop

//close table
echo "</table>";
?>

 

All I am getting is blank variables.  The number of rows echoed is correct, which tells me that the query is working but my variable call somehow isn't.  Any thoughts?

 

[ninedoors]

I just swicthed the mysql_fetch_assoc with mysql_fetch_array so I could try numeric indexes and it worked.  Still wondering how you would do it with associative indexes though.  Is it possible?  Or does PHP atuomatically make the indexes numeric so that there are never any conflicts with column name duplicates?  Anyone know?