High commented articles

[vividona]

Hi,

 

I need to fetch the top 10 articles higher commented by users

[Mchl]

SELECT * FROM articles ORDER BY numberOfComments DESC LIMIT 10

[vividona]

Hi

 

I tried

SELECT * FROM bhl_contents INNER JOIN bhl_comments ON bhl_contents.artid = bhl_comments.artid ORDER BY bhl_comments.artid LIMIT 10

 

failed

it fetched to me one article (repeated 10 time)

[Andy-H]

 

SELECT * FROM bhl_contents INNER JOIN bhl_comments ON bhl_contents.artid = bhl_comments.artid ORDER BY COUNT(bhl_comments.artid) DESC LIMIT 10

[vividona]

 

SELECT * FROM bhl_contents INNER JOIN bhl_comments ON bhl_contents.artid = bhl_comments.artid ORDER BY COUNT(bhl_comments.artid) DESC LIMIT 10

 

Hi Andy-H

 

It fetched one article

[Andy-H]

 

SELECT * FROM bhl_contents INNER JOIN bhl_comments ON bhl_contents.artid = bhl_comments.artid ORDER BY COUNT(bhl_comments.artid) DESC LIMIT 10

 

Hi Andy-H

 

It fetched one article

 

Show full code please?

[vividona]

I tried it directly  through localhost (phpmyadmin)

[Andy-H]

I think you need to create a PHP script, return the results to an array using mysql_fetch_* and then loop through those results...

 

 

include "db.connection.php";

$query  = "SELECT * FROM bhl_contents INNER JOIN bhl_comments ON bhl_contents.artid = bhl_comments.artid ORDER BY COUNT(bhl_comments.artid) DESC LIMIT 10";
$result = mysql_query($query)or trigger_error("ERROR: " . mysql_error(), E_USER_ERROR);

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

 

[vividona]

Ya, I have my php code and I tried it also

 

$CheckArtisList = $SiteDatabase->dbqueries("SELECT * FROM bhl_contents INNER JOIN bhl_comments ON bhl_contents.artid = bhl_comments.artid ORDER BY COUNT(bhl_comments.artid) DESC LIMIT 10");

		if(!$SiteDatabase->sql_numrows($CheckArtisList) == 1) {
			return false;
		}

		if (isset($_GET['Artid'])) {

		$Artid = intval($_GET['Artid']);

		$ArtiList = $SiteDatabase->dbqueries("SELECT `artid`, `subject`, `body`, `uid`, `username`, `curtime` FROM 
		" . ArticleSystem::BHL_DB_PREFIX . "" . ArticleSystem::BHL_CONT_MGM . " WHERE artid='".$Artid."'");

		}
		echo '<div class="lastarti">
				<div class="nav3">
					Last Articles
				</div>';
		while($row = $SiteDatabase->sql_fetchrow($CheckArtisList))
		{
		$Artid = intval($row['artid']);
		$Sub = $row['subject'];
		$Bod = $row['body'];
		$uid = $row['uid'];

		echo '<ul>
				<li><a href="' . $_SERVER['PHP_SELF'] .'?action=viewarti&artid='.$Artid.'">'.$Sub.'</a> By: <a href="'.$_SERVER['PHP_SELF'].'?action=ubrief&uid='.$uid.'">'.$row['username'].'</a></li>
			</ul>';
		}
		echo '</div>';

[Andy-H]

I think it may be something to do with your database class, I have had the same problem when I tried making a mysql wrapper in OOP a while back, does it work with the code I supplied once a valid connection is made?

[Mchl]

I'd say it has more to do with the fact, that a query with aggregate function ( COUNT() ) and no GROUP BY clause will always return but one row

 

SELECT 
  * 
FROM 
  bhl_contents 
CROSS JOIN (
  SELECT 
    artid
    COUNT(*) AS cnt
  FROM
    bhl_comments
  GROUP BY
     artid
) AS sq
USING (artid)
ORDER BY sq.cnt DESC
LIMIT 10

[vividona]

I resolved it like this

SELECT * FROM bhl_contents INNER JOIN bhl_comments ON bhl_contents.artid = bhl_comments.artid GROUP BY bhl_comments.artid ORDER BY COUNT(bhl_comments.artid) DESC LIMIT 10

 

thank you MchlPosted

thank you Andy-H