Need help with MySQL 'JOIN' query

[simcoweb]

I need to do a query where someone clicks on a category link that carries a 'category=4' id which is passed to the next page containing the query. My tables are set up like this:

[pre]
category        member_cat        members        specialties
==========      ===========        =========      ============
categoryid --+  id            +--  memberid  --+    id
category    |  memberid    >-+    name        +-<  memberid
            +-< categoryid        title            speciality
                                    company
                                    phone
                                    email
                                    details
                                    image
[/pre]

The query needs to pull all members that are associated with that category id which, in turn, is then displayed via HTML in a neat little profile for each person it returns from the query. Need some guidance on this in the form of a working example so I can put it into my PHP memory banks. Thanks!

[fenway]

This should get you started:

[code]SELECT m.*
FROM member_cat AS mc ON ( mc.categoryid = c.categoryid )
LEFT JOIN member AS m ON ( m.memberid = mc.member_id )
WHERE mc.categoryid = '4'[/code]

[simcoweb]

Ok, cool. I do see in the query where you've set the category id at '4'. The category id will be passed with the url so it won't always be '4'. There's 1 thru 4 for the category id's being passed like this:

[quote]http://www.mysite.com/category.php?categoryid=1,2,3 or 4[/quote]

How would this affect your query code?

[simcoweb]

Ok, I inserted the query and made some minor adjustments. Unfortunately it's not producing any results. Here's the full snippet of code:

[code]$sql = "SELECT p.*
FROM member_cat AS mc ON ( mc.categoryid = c.categoryid )
LEFT JOIN plateau_pros AS p ON ( p.memberid = mc.member_id )
WHERE mc.categoryid = '4'";
$results = mysql_query($sql);
//$num_rows = mysql_num_rows($results);
$sql2=("SELECT photo FROM plateau_pros WHERE photo='photo'");
//$result2 = mysql_query($sql2) or die(mysql_error());
//$image = mysql_result($result2, 'photo');
//mysql_fetch_array($results);
if ($num_rows == 0) {
  echo "There are currently no professionals associated with this category.";
} else {[/code]

After the 'else' it runs the code to display the results in a bunch of HTML. Also, please note I modified your code references as the actual table names were slightly different than what I posted. The only change is this:

'members' should be 'plateau_pros'

[Barand]

1 ) Having commented out the line

[code]//$num_rows = mysql_num_rows($results);[/code]

then $num_rows is always going to be 0.


2 ) Why have
[code]$sql2=("SELECT photo FROM plateau_pros WHERE photo='photo'");[/code]

when you already selected all the columns from plateau_pros.

Incidentally, where is "photo" in the tables list you posted?

[simcoweb]

Barand:

I commented that out because it was producing an error. Not 100% what it said but something about it not being a valid argument.

The 2nd query was also an experiment at a time that I was having trouble getting the images to show up. That's been rectified now.

I know the code is sloppy as it's always a work in progress while i'm learning and experimenting.

What i'm trying to do now is run the query that matches all the members that are associated with a particular category whereas the category ID is passed in a hyperlink.

[Barand]

pass multiple id's as a simple comma-delimited list

[code]http://www.mysite.com/category.php?categoryid=1,2,3,4[/code]

Then

[code]<?php
$idlist = $_GET['categoryid'];

$sql = "SELECT c.category, p.*
        FROM category c
        INNER JOIN member_cat AS mc ON ( mc.categoryid = c.categoryid )
        INNER JOIN plateau_pros AS p ON ( p.memberid = mc.member_id )
        WHERE mc.categoryid IN ($idlist) ";
?>[/code]