[RESOLVED] Getting the comments.

[jerrylouise]

<?php
// Database connect //
$server = mysql_connect("", "", "");
$connection = mysql_select_db($server);
//Connect to stories table //
$sql = mysql_query('SELECT * FROM `storys` ORDER BY `id` DESC');
$numofrows = @mysql_num_rows($sql);
$result = mysql_query("SELECT * FROM storys_comments WHERE story_id = id", $server);
$num_rows = mysql_num_rows($result);
//Start Loop //
print '<table width="100%" border="0" cellpadding="4" cellspacing="0" >';
	print '<TR class="story_top"><td width="60%">Story</td><td width="15%">Comments</td><td width="15%">Author</td><td width="10%">Poster</td></TR>';
		while($row = mysql_fetch_array($sql, MYSQL_BOTH)) {
//start colour changing loop//
			for($i = 0; $i < $numofrows; $i++)	{
				$row = @mysql_fetch_array($sql);
				// the ifelse //
					if($i % 2) 
						{ 
							print '<TR class="pink">';
						} 
				else 
							{ 
								print '<TR class="wight">';
							}
//the output//
	printf("<td><a href=\"story_display.php?id=%s&usrname=$usrname\">%s</a></td><td>%s</td><td><span style=\"TEXT-ALIGN:center\">%s</span></td><td>%s</td>", $row["id"], $row["title"], $row[$num_rows], $row["author"], $row["poster"]);
		print "</TR>";
//end colour changing loop//
													}
															}
	print "</table>";
//end //
?>

This is the code i gots, what dosnt work is $num_rows prints out the id and not the number of comments the story has, to be truefull i have kinda just glued this into the existing code because they both work quite fine on there own. I will provide the idividual codes below so you can see both working versions.

<?php
// Database connect //
include("../include/database.php");
// do stuff //
$usrname = $_GET['usrname'];
if (isset($_GET['id']) and is_numeric($_GET['id'])) {
$id = $_GET['id'];
} else {
     exit ('invalid id');
}
$sql = mysql_query("SELECT * FROM site_comments WHERE artical_id = $id ORDER BY id DESC");
if (!$sql) {
     exit ('Error retrieving comments..');
}
$numofrows = mysql_num_rows($sql); 
   print '<table width="100%" border="0" cellpadding="4" cellspacing="0" >';
	print '<tr><td width="70%"></td><td width="30%"></td></tr>';
if(mysql_num_rows($sql)) // we got some results 
{ 
   $i = 0; 
   while($row = mysql_fetch_array($sql)) 
   { 
      if($i++ % 2) 
      { 
         print '<tr class="pink">'; 
      } 
      else 
      { 
        print '<tr class="wight">'; 
      } 
      //the output// 
      printf("<td>%s</td><td>%s</td></tr>", $row["3"], $row["2"]); 
   } 
   print '</table>'; 
} 
else 
{ 
   print '<p class="error">No, Comments</p>'; 
}
?>


<table width="100%" border="0" cellspacing="0" cellpadding="4">
	<tr>
	    <td class="addcomment"><a href="javascript:ajaxpage('includes-functions/site_comment_add.php?id=<? print $id ?>&usrname=<? print $usrname ?>', 'comadd');">Add a comment</a></td>
	</tr>
  		<tr>
</table>
<div id="comadd">
</div>

<?php
$link = mysql_connect("", "", "");
mysql_select_db("", $link);
$id = "1";
$result = mysql_query("SELECT * FROM site_comments WHERE artical_id = $id", $link);
$num_rows = mysql_num_rows($result);
echo "(";
echo "$num_rows";
echo ")";
?>

[chyan]

so, what the problem ??

[jerrylouise]

chyan wrote:

so, what the problem ??

I want to merge the comments and story list together so it shows the amount of comments a story has. however i cant seem to work out how to do it.

[chyan]

you can do it by join story table and comment table together using you story_id field as key ,then count the record
i.e: select count(comment) from comment,storytable where storytable.storyid=comment.story_id

that query will return amount of comment that the story had

[jerrylouise]

This is what i have so far, still no go >_<

$connection = mysql_select_db($server);
//Connect to stories table //
$sql = mysql_query('SELECT * FROM `storys` ORDER BY `id` DESC');
$numofrows = @mysql_num_rows($sql);
$result = mysql_query("SELECT * FROM storys_comments WHERE story_id = id", $server);
$num_rows = mysql_query("SELECT count(storys_comments) FROM storys_comments,storys WHERE storys.story_id=storys_comments.story_id");
//Start Loop //
print '<table width="100%" border="0" cellpadding="4" cellspacing="0" >';
	print '<TR class="story_top"><td width="60%">Story</td><td width="20%">Author</td><td width="10%">Poster</td><td width="10%">Comments</td></TR>';
if(mysql_num_rows($sql)) // we got some results 
{ 
   $i = 0; 
   while($row = mysql_fetch_array($sql)) 
   { 
      if($i++ % 2) 
      { 
         print '<tr class="pink">'; 
      } 
      else 
      { 
        print '<tr class="wight">'; 
      } 
      //the output// 
	printf("<td><a href=\"story_display.php?id=%s&usrname=$usrname\">%s</a></td><td><span style=\"TEXT-ALIGN:center\">%s</span></td><td>%s</td><td>%s</td></tr>", $row["id"], $row["title"], $row["author"], $row["poster"], $row=($num_rows));
}  

   print '</table>'; 
} 
else 
{ 
   print '<p class="error">No, Comments</p>'; 
}
?>