mysq count

[autly]

<?php
mysql_connect("localhost", "root", "") or die(mysql_error());
mysql_select_db("evaluate") or die(mysql_error());
$sql = ("SELECT * FROM video ORDER BY video_id desc");
$result = @mysql_query($sql) or die(mysql_error());
echo '<table width="480" border="0" align="center" cellpadding="0" cellspacing="0">
 <tr>';
$i = 0;
while($row = mysql_fetch_array($result))

{ 
if($i++%3==0) echo '</tr><tr>'; echo '<td><table width="160" border="0" cellpadding="0" cellspacing="0">
 <tr>
 <td width="33%" height="100%"><a href="preview.php?id='.$row['video_id'].'"><img width="120" height="70" border="0" src="http://127.0.0.1/image/'.$row['video_image'].'"></a></td>
 </tr>
 <tr>
<td width="33%" height="100%"><a href="preview.php?id='.$row['video_id'].'"><strong>Title:</strong> '.$row['video_title'].'</a></td>
 </tr>
 <tr>
<td width="33%" height="100%"><a href="preview.php?id='.$row['video_id'].'"><strong>Apraksts:</strong> '.$row['video_description'].'</a></td>
 </tr>
</table></td>';
}
echo '</tr></table>';
?>

 

Man shitam vajag vel pievienot komentaaru skaitu, jeb

$query = "SELECT article_id, COUNT(comment_text) FROM video_comments GROUP BY article_id='1'";
$result = mysql_query($query) or die(mysql_error());
$row = mysql_fetch_array($result);
echo $row['COUNT(comment_text)'];

 

Kaa buutu pareizaak, un efektivaak to izdariit?

Edited December 1, 2008 by autly

[marcis]

LEFT JOIN

[autly]

LEFT join izspratu, bet iisti neiebraucu kaa to pielitot manaa gadijumaa, varbuut vari kaadu pimeeru iedot?

[codez]

Vislabāk ir izveidot lauku - komentāru skaits un tajā skaitīt cik attiecīgajam objektam ir komentāru.

[marcis]

SELECT v.*, COUNT(c.comment_text) FROM `video` v LEFT JOIN `video_comments` c ON c.article_id=v.video_id GROUP BY v.video_id ORDER BY v.video_id DESC