Writing a simple blog script, I have the following code to display the main blog posts:

<?php
#get info from db

 $sql = "select * from blogpost order by time desc";
 $rs = @mysql_query($sql);

#loop through records and generate table

 while ($row = mysql_fetch_array($rs))
 {

?>

  <table cellpadding="3" cellspacing="0" width="100%">
    <tr>
      <td width="100%"><b><? echo $row["title"]; ?></b><br>
<?php

#get the date/time info

 $datetime = $row["time"];
 $year = substr($datetime,0,4);
 $mon = substr($datetime,4,2);
 $day = substr($datetime,6,2);
 $hour = substr($datetime,8,2);
 $min = substr($datetime,10,2);
 $sec = substr($datetime,12,2);
  $orgdate = date("d.m.y @ g:i a",
                mktime($hour,$min,$sec,$mon,$day,$year));
?>
                       It was: <? echo $orgdate; ?><br>
                       Music: <? echo $row["musicartist"]; ?> - <? echo $row["musictitle"]; ?><br>
                       Mood: <? echo $row["mood"]; ?><br><br>
                       <? echo $row["content"]; ?><br><br>
                        <?#take note of this next line, this is what i'm after...?>
                       <a href="comments.php?ID=<? echo $row["id"]; ?>">x comments</a>  |  <a href="commentsadd.php?ID=<? echo $row["id"]; ?>">Add a comment</a><br>
      </td>
    </tr>
  </table>

<br><br>

<? } ?>

Now then, instead of it out putting 'x comments' I want it to list how many comments there are for that blog post. There are two tables, the blogpost one giving an id, time, content, mood etc and the blogcomments one holding a commentid, blogid (this is the ID of the blog post pulled from the blogpost table), content (etc...).

What I want is to run a count query on the second table to discern how many comments there are for that post, then escape that query and continue to use information from the first table.

Would

<? $query1 = "select count(*) from blogcomments where id = $row["id"];"
$exec = mysql_query($query) or die (mysql_error());

echo $exec ?> comments | <a href="commentsadd.php?ID=<? echo $row["id"]; ?>">Add a comment</a><br>

Do it, or what? Bare in mind I then need to be able to use $row["id"] to pull the ID of the actual post from the blogpost table after, instead of the blogcomments table...

Is this in any way clear? I've slept so little lately... 😛

    <?php

$query1 = "select count(*) from blogcomments where id=$row["id"];"
$exec = mysql_fetch_row($query1) or die (mysql_error());

echo $exec[0];

?>

comments | <a href="commentsadd.php?ID=<? echo $row["id"]; ?>">Add a comment</a><br>

    With the $row['id'] you will need to re-query the DB

      Write a Reply...