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

Codes here !

Moderators: macek, egami, gesf

sincitybrant
New php-forum User
New php-forum User
Posts: 6
Joined: Tue Apr 01, 2003 5:20 pm
Location: Las Vegas
Contact:

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

Postby sincitybrant » Sat Apr 05, 2003 5:22 pm

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: Select all

$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?

User avatar
bezmond
Moderator
Moderator
Posts: 312
Joined: Sat Apr 05, 2003 4:33 am
Location: Mansfield, UK
Contact:

Postby bezmond » Sat Apr 05, 2003 11:50 pm

$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

sincitybrant
New php-forum User
New php-forum User
Posts: 6
Joined: Tue Apr 01, 2003 5:20 pm
Location: Las Vegas
Contact:

I've gotten it working, a couple of things were wrong

Postby sincitybrant » Tue Apr 08, 2003 2:32 pm

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. :o


Return to “mySQL & php coding”

Who is online

Users browsing this forum: No registered users and 3 guests