simcoweb Posted October 10, 2006 Share Posted October 10, 2006 ok, I have four tables like this:category member_cat plateau_pros specialties========== =========== ========= ============categoryid --+ id +-- memberid --+ idcategory | memberid >-+ name +-< memberid +-< categoryid title speciality company phone email details imageI 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! Quote Link to comment Share on other sites More sharing options...
GremlinP1R Posted October 10, 2006 Share Posted October 10, 2006 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, ???? Quote Link to comment Share on other sites More sharing options...
simcoweb Posted October 10, 2006 Author Share Posted October 10, 2006 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. Quote Link to comment Share on other sites More sharing options...
roopurt18 Posted October 10, 2006 Share Posted October 10, 2006 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. Quote Link to comment Share on other sites More sharing options...
simcoweb Posted October 10, 2006 Author Share Posted October 10, 2006 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] Quote Link to comment Share on other sites More sharing options...
GremlinP1R Posted October 10, 2006 Share Posted October 10, 2006 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. Quote Link to comment Share on other sites More sharing options...
roopurt18 Posted October 10, 2006 Share Posted October 10, 2006 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.memberidLuckily, mysql_query only runs one query at a time, AFAIK, but why take chances?[b]Always[/b] validate your data! Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.