Getting the result

[jerrylouise]

hi im having difficulty displaying the results of my counter. What the following code currently dose is display the story, auther, and poster, but no mater what i try that i know it wont display the result of $num_rows witch is supposed to be the amount of comments the story has. this is really fustrating me.

//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.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>'; 
}
?>

[NogDog]

I think maybe you want:

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"], [color=red][b]$num_rows[/b][/color]);

[jerrylouise]

NogDog wrote:

I think maybe you want:

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"], [color=red][b]$num_rows[/b][/color]);

Still no result.

[jerrylouise]

With the following it just says

Resource id #27

$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(comment) FROM storys_comments,storys WHERE storys.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"], $num_rows);
}  

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

[NogDog]

Try changing $num_rows in the printf() statement to mysql_result($num_rows, 0)

[jerrylouise]

ok now it just print out 2 for every single artical instead of the achual storys comment ammount i think its counting everycomment.

Ok so the logic sould be

count comments Form story_comments Where storys_comments.id = storys.id

so it sould go into the story comment database and count the amount of comments that match the story_id of the comment and display the ammount on screen. Right now it just prints the total ammount of comments.

$num_rows = mysql_query("select * count(omments) from storys_comments, storys where storys.story_id=storys_comments.id");

^ im thinking here it sould be counting the story_id or something

then to display it

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"], $num_rows);

IF i put in mysql_result($num_rows, 0)) i get this result

Warning: mysql_result(): supplied argument is not a valid MySQL result resource in /home/fhlinux168/d/dev.cossycomforts.com/user/htdocs/includes-blocks/stories.php on line 67

[jerrylouise]

Its only printing out the total ammount of comments on every single line instead of each entrys ammount of comments.

//Connect to stories table //
$sql = mysql_query('SELECT * FROM `storys` ORDER BY `id` DESC');
$result = mysql_query("SELECT * FROM storys_comments WHERE story_id = id", $server);
$num_rows = mysql_query("SELECT COUNT(comment) FROM storys_comments, storys WHERE storys_comments.story_id = storys.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"], mysql_result($num_rows, 0));
}  

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

[NogDog]

I think maybe what you're after is something like (untested):

$sql = "
SELECT s.`id`, s.`title`, s.`author`, s.`poster`, COUNT(c.`story_id`) AS `ct`
   FROM `storys` s LEFT JOIN `storys_comments` c  ON s.`id` = c.`story_id`
   GROUP BY c.`story_id`
";
$result = mysql_query($sql) or die("Query failed($sql): " . mysql_error());
if(mysql_num_rows($result))
{
   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>';
   $i = 0;
   while($row = mysql_fetch_array($result))
   {
      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["ct"]
      );
   }  

   print '</table>';
}
else
{
   print '<p class="error">Data error: no records found.</p>';
}

[jerrylouise]

ok it prints the storys now but its only printing 3 of the storys for some reason instead of them all.

[jerrylouise]

okies i gots it working now, perhaps you can help implement the next part. This is to display a drop down box with a preview of the comments the ajax page already works just posting it into <td></td> dosnt work.

<a href="javascript:ajaxpage('story/comment_preview.php?id=%s', 'infobox');">Rules</a><div class="%s"></div>

it sould go here

   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><a href="javascript:ajaxpage('story/comment_preview.php?id=%s', 'infobox');">Rules</a><div class="%s"></div></td></tr>", 
         $row["id"], 
         $row["title"], 
         $row["author"], 
         $row["poster"], 
         $row["ct"] 
      ); 

[NogDog]

Possibly try changing the GROUP BY expression to GROUP BY s.id (I'm getting outside of my SQL comfort zone here, so I'm not sure if this will fix it or screw it up further 😉 ).