[SOLVED] What is the correct query? Driving me mad...

[dannyluked]

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

[abazoskib]

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

[xtopolis]

idea

[dannyluked]

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

[kickstart]

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

[dannyluked]

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

[kickstart]

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

[dannyluked]

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

[kickstart]

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

[watsmyname]

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>";
}
?>

[dannyluked]

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"

[watsmyname]

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>";

[dannyluked]

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'

[dannyluked]

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'