Creating a "Most comments" search

[chomedey]

I have two tables in a database, one with user-submitted entries, and another with comments on those entries.  I would like to create a mysql query that figures out which entries have the most comments, so I can print a list of "most discussed" entries.

 

Can I do this with the tables set up as below?  Do I need multiple queries, as opposed to a join?  I'm stumped on this (fairly new to php and mysql).

 

Thanks.

 

Julian

 

Here are the table columns:

 

entries:

entryID INT UNSIGNED NOT NULL AUTO_INCREMENT,

username VARCHAR(16) NOT NULL,

title VARCHAR(100) NOT NULL,

entry TEXT NOT NULL,

word_count INT,

date_entered DATETIME NOT NULL,

views INT NOT NULL,

total_rating INT NOT NULL,

total_ratings INT NOT NULL,

num_votes INT NOT NULL,

PRIMARY KEY (entryID)

 

comments:

commentID INT UNSIGNED NOT NULL AUTO_INCREMENT,

username VARCHAR(16) NOT NULL,

entryID INT NOT NULL,

comment TEXT NOT NULL,

date_entered DATETIME NOT NULL,

PRIMARY KEY (commentID)

 

[Andy-H]

define('__LIMIT', 10);
$query  = "SELECT e.*, COUNT(c.*) AS totalComments FROM entries e " . 
        . "INNER JOIN comments c USING (entryID) " .
        . "ORDER BY totalComments DESC LIMIT " . __LIMIT;
$result = mysql_query($query)or trigger_error('ERROR: ' . mysql_error(), E_USER_ERROR);

   while($row = mysql_fetch_assoc($result)) :
      echo '<pre>' . print_r($row, true) . '</pre>';
   endwhile;

That work?

 

//EDITED

[chomedey]

Getting:

 

Parse error: syntax error, unexpected '.' in /Users/julianhumphreys/Web Programming/Sites/mds/most_comments.php on line 27

 

where line 27 is

 

  . "INNER JOIN comments c USING (entryID) " .

 

I have no idea what you're doing here?  I don't suppose I could ask you for some comments on what's going on?

 

Thanks.

 

Julian

[Andy-H]

define('__LIMIT', 10);
$query  = "SELECT e.*, COUNT(c.*) AS totalComments FROM entries e "
        . "INNER JOIN comments c USING (entryID) "
        . "ORDER BY totalComments DESC LIMIT " . __LIMIT;
$result = mysql_query($query)or trigger_error('ERROR: ' . mysql_error(), E_USER_ERROR);

   while($row = mysql_fetch_assoc($result)) :
      echo '<pre>' . print_r($row, true) . '</pre>';
   endwhile;

lol how stupid do I look :|

 

I am using http://www.phpfreaks.com/tutorial/data-joins-unions to be honest with you lol

 

I am trying to join the tables and select the comment count with an alias of totalComments to use in the order by clause of the query, I'm pretty sure that we will need a group by clause in order for that to work, but hey, worth a shot lol

 

Anyway, the code should atleast run and display results this time round (y)

[chomedey]

I'm getting this error now:

 

Fatal error: ERROR: You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '*) AS totalComments FROM entries e INNER JOIN comments c USING (entryID) ORDER B' at line 1

[Andy-H]

define('__LIMIT', 10);
$query  = "SELECT e.*, COUNT(c.*) AS totalComments FROM entries AS e "
        . "INNER JOIN comments AS c ON e.entryID = c.entryID "
        . "ORDER BY totalComments DESC LIMIT " . __LIMIT;
$result = mysql_query($query)or trigger_error('ERROR: ' . mysql_error(), E_USER_ERROR);

   while($row = mysql_fetch_assoc($result)) :
      echo '<pre>' . print_r($row, true) . '</pre>';
   endwhile;

//EDIT

Not that good with SQL joins, this is the way I normally do it. Just having random tries here, hopefully it should work.

[chomedey]

I did it a different way in the end, updating a separate total_comments column as each comment is made.

Thanks for your help anyway.

J