Jump to content

help with JOIN query


simcoweb

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!


Link to comment
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, ????
Link to comment
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.
Link to comment
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.
Link to comment
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]
Link to comment
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.
Link to comment
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!
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.