Jump to content

Archived

This topic is now archived and is closed to further replies.

simcoweb

help with JOIN query

Recommended Posts

ok, I have four tables like this:

category              member_cat          plateau_pros        specialties
==========      ===========        =========      ============
categoryid --+                id            +--  memberid    --+    id
category                |  memberid    >-+    name        +-<  memberid
                            +-< categoryid        title                speciality
                                                      company
                                                        phone
                                                        email
                                                        details
                                                        image

I want to pull data from the members that match a specific category. I have 4 categories number 1 - 4 for their id's. My link to the category display page is like this:

[quote]http://www.nameofsite.com/category.php?categoryid=1[/quote]

I'm not sure how to get the category id passed into the query so it pulls the proper results. I have this query i'm trying:

[code]$sql = "SELECT firstname, lastname, business, title, city, memberid, photo FROM plateau_pros FROM plateau_pros, members_cat WHERE members_cat.memberid = plateau_pros.memberid ";[/code]

if ($num_rows == 0) {
  echo "There are currently no professionals associated with this category.";
} else {

where the results would be dispursed throughout some HTML code which works fine if I just query SELECT * FROM plateau_pros which draws all the members.

So, 1) what's the correct way i'd set my link to the category.php page to summon the category id, and 2) how should the query be written to cross reference the member's associated with the category id?

Thanks!


Share this post


Link to post
Share on other sites
okay first your links not working, well at leat here at me.

second your code confuses me! and can only see half of it.
why not just say:

$Plat = mysql_querry("SELECT memberid FROM plateau_pros");
$sql = "SELECT * FROM members_cat WHERE memberid LIKE ('$Plat')";

for you number 2, ????

Share this post


Link to post
Share on other sites
Yeah, the link was just an example. If you go here:
[url=http://www.plateauprofessionals.com/index2.php]http://www.plateauprofessionals.com/index2.php[/url]

and click on one of the category links then that's the trigger.

Ok, about the code. I left everything out after the 'else' statements as it's just a bunch of HTML to layout the page and display the results using $var tags. Works fine so didn't bother pasting that into the post.

Your query code looks backwards to me. However, i'm a noobster so all this is a bit backwards. The data I want is in the plateau_pros table, not the members_cat table. The member 'id' is stored in both those tables which is the 'connecting point' between the two. When someone clicks on the category link it should pull all memberid's from the members_cat table then cross reference those with the plateau_pros table to summon the data about each member that would then be displayed in the page. Make sense?

The other question is if i'm passing the category id to the category.php page properly so the query knows which category to pull from.

Share this post


Link to post
Share on other sites
Assuming:
[code]http://www.nameofsite.com/category.php?categoryid=1[/code]

category.php would need something like:
[code]
<?php
  $cat_id = ValidateCatID($_GET['categoryid']); // Double check for a valid value
  // Grab data from plateau_pros, where the member has the specified category id
  $sql = "SELECT p.* FROM member_cat c, plateau_pros p WHERE c.categoryid={$cat_id} "
        . "AND c.memberid=p.memberid";
  $q = mysql_query($sql);
  if($q){
    while($row = mysql_fetch_assoc($q)){
      echo '<pre style="text-align: left;">' . print_r($row, true) . '</pre>';
    }
  }
?>
[/code]

See what that gives you.

Share this post


Link to post
Share on other sites
roopurt, thanks for the post. I think we're getting closer. Here's what it produces:

this line: [quote]$cat_id = ValidateCatID($_GET['categoryid']);[/quote]

gets this error:

[quote]Fatal error: Call to undefined function: validatecatid() in /home2/wwwplat/public_html/category.php on line 9[/quote]

Share this post


Link to post
Share on other sites
HAHA okay then I have it backwards.

$mem = mysql_querry("SELECT memberid FROM members_cat") or die();
$sql = mysql_querry("SELECT * FROM plateau_pros WHERE memberid LIKE ('$mem')")or die();
while ($row= mysql_fetch_array($sql)){$[color=red]field[/color] = $row['[color=red]field[/color]'];}

Then your $[color=red]field[/color] would be your data that it would retreve. Rember it has to stay in the while {} to loop through every one.

Share this post


Link to post
Share on other sites
The function is undefined because it doesn't exist.  You could very well use this line:[code]$cat_id = $_GET['categoryid'];[/code]

I included the function to emphasize that you are using a value that comes from your user [b]and you should always validate such values.[/b]  You and only you, as the developer of that site, know what a valid category ID is.  It could be as simple as:[code]$cat_id = is_numeric($_GET['categoryid']) ? $_GET['categoryid'] : 0;[/code]

But you need to put something, because imagine if the user some how found out a little about your database, like the existence of your [b]plateau_pros[/b] table.

What happens if they give you this URL:
[code]http://www.nameofsite.com/category.php?categoryid=0; DROP TABLE IF EXISTS plateau_pros;[/code]

Your sql statement will turn into:
SELECT p.* FROM member_cat c, plateau_pros p WHERE c.categoryid=0; [b]DROP TABLE IF EXISTS plateau_pros;[/b]AND c.memberid=p.memberid

Luckily, mysql_query only runs one query at a time, AFAIK, but why take chances?

[b]Always[/b] validate your data!

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.