dannyluked Posted July 30, 2009 Share Posted July 30, 2009 Hi, I have two tables; Blog id title 1 A 2 B 3 C Comments id title comment 1 A Blah, Blah, Blah... 2 A Blah, Blah, Blah... 3 B Blah, Blah, Blah... Then I want the output to be: A - (2) comment('s) B - (1) comment('s) C - (0) comment('s) If anyone knows how to do this could they please give me full php code (form <?php to ?>!). MySQL version 5.1 This would be a great help as I have no idea. Thank you in advance Quote Link to comment https://forums.phpfreaks.com/topic/168199-solved-what-is-the-correct-query-driving-me-mad/ Share on other sites More sharing options...
abazoskib Posted July 30, 2009 Share Posted July 30, 2009 i wont code it for you, but here's some heads up on the matter. you need to use a select query with a join on the two tables by relating blog.title=comments.title Quote Link to comment https://forums.phpfreaks.com/topic/168199-solved-what-is-the-correct-query-driving-me-mad/#findComment-887247 Share on other sites More sharing options...
xtopolis Posted July 31, 2009 Share Posted July 31, 2009 idea Quote Link to comment https://forums.phpfreaks.com/topic/168199-solved-what-is-the-correct-query-driving-me-mad/#findComment-887330 Share on other sites More sharing options...
dannyluked Posted July 31, 2009 Author Share Posted July 31, 2009 Thanks abazoskib, Ill read up more on it although I have already read about it and dosent seem to show what I want. Ill read again though! I read xtopolis comment and dont know if hes being sarcastic, hopefully not but I'm posting my question on this forum because I thought it was for help. I have learned basic MySQL myself so have never been taught the more complicated things. Hopefully someone else could help me further Quote Link to comment https://forums.phpfreaks.com/topic/168199-solved-what-is-the-correct-query-driving-me-mad/#findComment-887606 Share on other sites More sharing options...
kickstart Posted July 31, 2009 Share Posted July 31, 2009 Hi Does look like a join is what you need, but not certain exactly what you do need. Ie, in your example what does (2), (1) and (0) refer to. When you have "comments" do you mean (for title A) "Blah, Blah, Blah..." for id 1 and "Blah, Blah, Blah..." for id 2 rolled together? All the best Keith Quote Link to comment https://forums.phpfreaks.com/topic/168199-solved-what-is-the-correct-query-driving-me-mad/#findComment-887618 Share on other sites More sharing options...
dannyluked Posted August 2, 2009 Author Share Posted August 2, 2009 Sorry for the slow reply! I want to show how many coments each blog has. Then on a seperate page those comments will be shown. Sort of like this forum shows how many replies each topic has. Thanks Quote Link to comment https://forums.phpfreaks.com/topic/168199-solved-what-is-the-correct-query-driving-me-mad/#findComment-888908 Share on other sites More sharing options...
kickstart Posted August 2, 2009 Share Posted August 2, 2009 Hi Firstly in the Comments table don't use the Blog title (as it could easily be duplicated), use the Blog Id field. To get a list of blogs with the number of columns:- SELECT Blog.id, Blog.title, count(Comments.id) AS CommentCount FROM Blog LEFT OUTER JOIN Comments ON Blog.id = Comments.blogid GROUP BY Comments.blogid If you want all the comments for a particular blog (say id stored as $SelectedBlogId) "SELECT Blog.title, Comments.id, Comments.comment FROM FROM Blog LEFT OUTER JOIN Comments ON Blog.id = Comments.blogid WHERE Blog.id = $SelectedBlogId ORDER BY Comments.id". All the best Keith Quote Link to comment https://forums.phpfreaks.com/topic/168199-solved-what-is-the-correct-query-driving-me-mad/#findComment-888929 Share on other sites More sharing options...
dannyluked Posted August 2, 2009 Author Share Posted August 2, 2009 Thank you ever so much, I am just wondering how I apply this. What is the other code to display it. I get the error message: Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/a1072392/public_html/b/home/content.php on line 25 I am using this code: <?php include "config.php"; mysql_connect($server, $db_user, $db_pass) or die(mysql_error()); mysql_select_db($database) or die(mysql_error()); $result = mysql_query("SELECT Blog.id, Blog.title, count(Comments.id) AS CommentCount FROM Blog LEFT OUTER JOIN Comments ON Blog.id = Comments.blogid GROUP BY Comments.blogid"); while($row = mysql_fetch_array( $result )) { echo "$row"; } ?> Is that the right code?! Thanks for all help so far Quote Link to comment https://forums.phpfreaks.com/topic/168199-solved-what-is-the-correct-query-driving-me-mad/#findComment-888935 Share on other sites More sharing options...
kickstart Posted August 2, 2009 Share Posted August 2, 2009 Hi Have you changed the database structure to have the blogid instead of the blog title in the comments table? Change the line with the query to this for now to get it to output the SQL error message. $result = mysql_query("SELECT Blog.id, Blog.title, count(Comments.id) AS CommentCount FROM Blog LEFT OUTER JOIN Comments ON Blog.id = Comments.blogid GROUP BY Comments.blogid") or die(mysql_error()); All the bst Keith Quote Link to comment https://forums.phpfreaks.com/topic/168199-solved-what-is-the-correct-query-driving-me-mad/#findComment-888944 Share on other sites More sharing options...
watsmyname Posted August 2, 2009 Share Posted August 2, 2009 Thank you ever so much, I am just wondering how I apply this. What is the other code to display it. I get the error message: Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource in /home/a1072392/public_html/b/home/content.php on line 25 I am using this code: <?php include "config.php"; mysql_connect($server, $db_user, $db_pass) or die(mysql_error()); mysql_select_db($database) or die(mysql_error()); $result = mysql_query("SELECT Blog.id, Blog.title, count(Comments.id) AS CommentCount FROM Blog LEFT OUTER JOIN Comments ON Blog.id = Comments.blogid GROUP BY Comments.blogid"); while($row = mysql_fetch_array( $result )) { echo "$row"; } ?> Is that the right code?! Thanks for all help so far well do you have field blogid in comments table?? in the first post there is no blogid field. And according to your post my sql will be like this (with no blogid in comments table.) <?php //your database stuffs $result = mysql_query("select b.title, count(c.title) as countcomment from Blog b, Comments c WHERE b.title=c.title"); while($row=mysql_fetch_array($result)) { echo $row["title"]." - ($row[countcomment]) comment('s)<br>"; } ?> Quote Link to comment https://forums.phpfreaks.com/topic/168199-solved-what-is-the-correct-query-driving-me-mad/#findComment-888951 Share on other sites More sharing options...
dannyluked Posted August 2, 2009 Author Share Posted August 2, 2009 To start with I do have blogid now! and it allows duplicate entries so... Also the table called 'Comments' in my firt post is actually 'blogcomments' It gave this error: "Unknown column 'blogcomments.id' in 'field list'" For this code: <?php include "config.php"; mysql_connect($server, $db_user, $db_pass) or die(mysql_error()); mysql_select_db($database) or die(mysql_error()); $result = mysql_query("SELECT blog.id, blog.title, count(blogcomments.id) AS CommentCount FROM blog LEFT OUTER JOIN blogcomments ON blog.id = blogcomments.blogid GROUP BY blogcomments.blogid") or die(mysql_error()); while($row = mysql_fetch_array( $result )) { echo "$row"; } ?> So I changed the code to this (I changed the blogcomments.id to blogcomments.blogid): <?php include "config.php"; mysql_connect($server, $db_user, $db_pass) or die(mysql_error()); mysql_select_db($database) or die(mysql_error()); $result = mysql_query("SELECT blog.id, blog.title, count(blogcomments.blogid) AS CommentCount FROM blog LEFT OUTER JOIN blogcomments ON blog.id = blogcomments.blogid GROUP BY blogcomments.blogid") or die(mysql_error()); while($row = mysql_fetch_array( $result )) { echo "$row"; } ?> But it just said: "ArrayArrayArray" Quote Link to comment https://forums.phpfreaks.com/topic/168199-solved-what-is-the-correct-query-driving-me-mad/#findComment-888952 Share on other sites More sharing options...
watsmyname Posted August 2, 2009 Share Posted August 2, 2009 To start with I do have blogid now! and it allows duplicate entries so... It gave this error: "Unknown column 'blogcomments.id' in 'field list'" For this code: <?php include "config.php"; mysql_connect($server, $db_user, $db_pass) or die(mysql_error()); mysql_select_db($database) or die(mysql_error()); $result = mysql_query("SELECT blog.id, blog.title, count(blogcomments.id) AS CommentCount FROM blog LEFT OUTER JOIN blogcomments ON blog.id = blogcomments.blogid GROUP BY blogcomments.blogid") or die(mysql_error()); while($row = mysql_fetch_array( $result )) { echo "$row"; } ?> So I changed the code to this (I changed the blogcomments.id to blogcomments.blogid): <?php include "config.php"; mysql_connect($server, $db_user, $db_pass) or die(mysql_error()); mysql_select_db($database) or die(mysql_error()); $result = mysql_query("SELECT blog.id, blog.title, count(blogcomments.blogid) AS CommentCount FROM blog LEFT OUTER JOIN blogcomments ON blog.id = blogcomments.blogid GROUP BY blogcomments.blogid") or die(mysql_error()); while($row = mysql_fetch_array( $result )) { echo "$row"; } ?> But it just said: "ArrayArrayArray" well look in while loop you have echoed $row, which is array do it like this instead echo $row["title"]." - ($row[CommentCount]) comment('s)<br>"; Quote Link to comment https://forums.phpfreaks.com/topic/168199-solved-what-is-the-correct-query-driving-me-mad/#findComment-888953 Share on other sites More sharing options...
dannyluked Posted August 2, 2009 Author Share Posted August 2, 2009 Thank you very much, this topic is finally nearly solved! Just one small problem!!! It now displays exactly how i want it too using: <?php include "config.php"; mysql_connect($server, $db_user, $db_pass) or die(mysql_error()); mysql_select_db($database) or die(mysql_error()); $result = mysql_query("SELECT blog.id, blog.title, count(blogcomments.blogid) AS CommentCount FROM blog LEFT OUTER JOIN blogcomments ON blog.id = blogcomments.blogid GROUP BY blogcomments.blogid") or die(mysql_error()); while($row = mysql_fetch_array( $result )) { echo $row["title"]." - ($row[CommentCount]) comment('s)<br>"; } ?> I just want to know how I limit the amount of outputs to 3 and order them by 'blog.id desc' Again thanks to 'whatsmyname' and 'kickstart' Quote Link to comment https://forums.phpfreaks.com/topic/168199-solved-what-is-the-correct-query-driving-me-mad/#findComment-888959 Share on other sites More sharing options...
dannyluked Posted August 2, 2009 Author Share Posted August 2, 2009 Sorry, stupic question i know the answer too! For anyone who wants to know this is the final code: <?php include "config.php"; mysql_connect($server, $db_user, $db_pass) or die(mysql_error()); mysql_select_db($database) or die(mysql_error()); $result = mysql_query("SELECT blog.id, blog.title, count(blogcomments.blogid) AS CommentCount FROM blog LEFT OUTER JOIN blogcomments ON blog.id = blogcomments.blogid GROUP BY blogcomments.blogid order by id desc LIMIT 3") or die(mysql_error()); while($row = mysql_fetch_array( $result )) { echo "$row[title] - ($row[CommentCount]) comment('s)<br>"; } ?> Thanks 'whatsmyname' an 'kickstart' Quote Link to comment https://forums.phpfreaks.com/topic/168199-solved-what-is-the-correct-query-driving-me-mad/#findComment-888960 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.