jabbamonkey Posted August 1, 2003 Share Posted August 1, 2003 I have three tables I\'m trying to combine. Two tables have information regarding a subject, and the third table is what I call the \"Connection\" table. I have the following query.... SELECT a.comment_id, a.name, a.email, a.comment, a.live, b.article_name, b.article_id, c.article_id, c.comment_id, c.commentjuke_id FROM comments as a, articles as b, connect_commentarticles as c WHERE c.article_id=\'$id\' AND a.comment_id=c.comment_id AND a.live=\'1\'"; Basically, Comments for articles are stored in one table. Articles content is stored in the other, and the third table pulls the two tables together (only three columns, for the article id, the comments id, and it\'s own id). I just want to pull the tables together but keep getting an error. NOTE: the variable \"$id\" is the article id, that will be submitted by the user. Please let me know if you can help! Quote Link to comment https://forums.phpfreaks.com/topic/812-3-tables-1-query/ Share on other sites More sharing options...
michael yare Posted August 1, 2003 Share Posted August 1, 2003 You have only joined a to c. You need to join b to a or c. Quote Link to comment https://forums.phpfreaks.com/topic/812-3-tables-1-query/#findComment-2688 Share on other sites More sharing options...
jabbamonkey Posted August 1, 2003 Author Share Posted August 1, 2003 Would this be right? SELECT a.comment_id, a.name, a.email, a.comment, a.live, b.article_name, b.article_id, c.article_id, c.comment_id, c.commentjuke_id FROM comments as a, articles as b, connect_commentarticles as c WHERE b.article_id=\'$id\' AND c.article_id=b.article_id AND a.comment_id=c.comment_id AND a.live=\'1\'"; Quote Link to comment https://forums.phpfreaks.com/topic/812-3-tables-1-query/#findComment-2690 Share on other sites More sharing options...
michael yare Posted August 1, 2003 Share Posted August 1, 2003 Looks good. You only need 1 articleID in the SELECT clause BTW (not that it matters, but its efficient syntax). Quote Link to comment https://forums.phpfreaks.com/topic/812-3-tables-1-query/#findComment-2691 Share on other sites More sharing options...
jabbamonkey Posted August 1, 2003 Author Share Posted August 1, 2003 I keep getting an error ... Warning: mysql_num_rows(): supplied argument is not a valid MySQL result resource in... Does it matter the order of the WHERE clause? For example, does it matter if \"b.article_id=c.article_id\" or \"c.article_id=b.article_id\"? SELECT a.comment_id, a.name, a.email, a.comment, a.live, b.article_name, c.article_id, c.comment_id, c.commentjuke_id FROM comments as a, articles as b, connect_commentarticles as c WHERE b.article_id=\'$id\' AND c.article_id=b.article_id AND a.comment_id=c.comment_id AND a.live=\'1\'"; Quote Link to comment https://forums.phpfreaks.com/topic/812-3-tables-1-query/#findComment-2693 Share on other sites More sharing options...
michael yare Posted August 1, 2003 Share Posted August 1, 2003 No, the order of the WHERE clauses does not matter. What are you passing to mysql_num_rows? I just tested your syntax with one of my a-b-c tables and it worked. Quote Link to comment https://forums.phpfreaks.com/topic/812-3-tables-1-query/#findComment-2695 Share on other sites More sharing options...
jabbamonkey Posted August 1, 2003 Author Share Posted August 1, 2003 After I set my query; here\'s what I have.... $result2 = mysql_query($query1,$db); $num_results = mysql_num_rows($result2); if($num_results!=0) { do { // PRINT INFORMATION FROM DATABASE } while ($myrow2 = mysql_fetch_array($result2)); } Quote Link to comment https://forums.phpfreaks.com/topic/812-3-tables-1-query/#findComment-2696 Share on other sites More sharing options...
Barand Posted August 4, 2003 Share Posted August 4, 2003 Best way is to echo \"$query1\" to see what is actually being executed. Could be that $id is empty. Quote Link to comment https://forums.phpfreaks.com/topic/812-3-tables-1-query/#findComment-2753 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.