how do I JOIN when my shared column is already in the WHERE

A

Anonymous

Guest
I am really enjoying php and mySQL, but the following has me frustrated. This php page uses a catid fed into it from another page. catid appears in both the bands table and the categories table . In the categories table it corresponds to a description of the band category (like rock or metal) called catname. Using the format of "SELECT * FROM bands,categories WHERE bandid=$bandid AND approved='Y' AND bands.catid = categories.catid" works just fine on another page where the information displayed is based upon a band's bandid. However using this format with my page below (see code) only results in my query dieing and displaying the error message.
Code:
$catid =$_POST['catid'];
$query = "SELECT * FROM bands WHERE approved ='1' AND catid ='$catid' ORDER BY bandname";
$result = mysql_query($query)
	or die ("Couldn't execute query.");
echo "<h1>$catname bands</h1>";
echo "<table cellspacing ='0'>";
echo "<tr><td colspan='0'></td></tr>";
while ($row = mysql_fetch_array($result))
{
	extract($row);
	  		 echo "<tr>\n
			 <td><a href='banddisplay.php?bandid=$bandid'>$bandname</a></td>\n
			 </tr>\n";
		echo "<tr><td colspan='3'></td></tr>\n";
}
echo "</table>\n";
?>
This leads me to surmise that the fact that my WHERE is based upon catid (and not some other column) is leading to my problem. Obviously though I want to be able to refer to catname to have this page work dynamically.

Any ideas?
 
$query = "SELECT * FROM bands WHERE approved ='1' AND catid ='$catid' ORDER BY bandname";

it might just be the case on my host, however I usually have to remove the space after approved, i.e. approved='1' AND catid='$catid'

Regards,
Andrew
 
Thanks for your reply, but that wasn't part of the problem at all. The reasons my code wasn't working are:

1.) mySQL was puzzled by the reference to catid (since it occurs in both tables in question). Changing that to bands.catid solved that problem.

2.) Even with that problem solved, $catname was not displaying. That was because the row was only extracted while in a loop. Moving the extracting statement out of the loop allowed me to access it and display it.

I am posting the above because it helps me to realize what the problem is and might help anyone with a similar problem. Thanks. :eek:
 
Back
Top