Jump to content


Photo

Need help with MySQL 'JOIN' query


  • Please log in to reply
6 replies to this topic

#1 simcoweb

simcoweb
  • Members
  • PipPipPip
  • Advanced Member
  • 1,102 posts
  • LocationCA

Posted 03 October 2006 - 06:55 PM

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!


#2 fenway

fenway
  • Staff Alumni
  • MySQL Si-Fu / PHP Resident Alien
  • 16,199 posts
  • LocationToronto, ON

Posted 03 October 2006 - 08:51 PM

This should get you started:

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'

Seriously... if people don't start reading this before posting, I'm going to consider not answering at all.

#3 simcoweb

simcoweb
  • Members
  • PipPipPip
  • Advanced Member
  • 1,102 posts
  • LocationCA

Posted 03 October 2006 - 09:43 PM

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:

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


How would this affect your query code?

#4 simcoweb

simcoweb
  • Members
  • PipPipPip
  • Advanced Member
  • 1,102 posts
  • LocationCA

Posted 03 October 2006 - 10:21 PM

Ok, I inserted the query and made some minor adjustments. Unfortunately it's not producing any results. Here's the full snippet of 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 {

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'

#5 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,016 posts

Posted 03 October 2006 - 10:29 PM

1 ) Having commented out the line

//$num_rows = mysql_num_rows($results);

then $num_rows is always going to be 0.


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

when you already selected all the columns from plateau_pros.

Incidentally, where is "photo" in the tables list you posted?
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#6 simcoweb

simcoweb
  • Members
  • PipPipPip
  • Advanced Member
  • 1,102 posts
  • LocationCA

Posted 04 October 2006 - 02:25 AM

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.

#7 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,016 posts

Posted 04 October 2006 - 09:29 AM

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

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

Then

<?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) ";
?>

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users