2 sql in one php while loop.

Question

I'm a bit of a novice to PHP and MySQL. I'm creating my own little news blog for the heck of it. I got to display the information I wanted, but what I want to do is count the number of comments. I made 2 tables, the first table is the article and the other is the comments. I not to sure of how to figure this out. Can anyone help?

$input_now1 = "SELECT blog_entries.id, blog_entries.posted, blog_entries.subject, blog_entries.quicktext
  FROM blog_entries
  ORDER BY blog_entries.posted DESC
  LIMIT 0, 3;";

$result1 = mysql_query($input_now1);

?>
<div id="middle">

  <div id="box1">
    <?php
      while($record1 = mysql_fetch_assoc($result1))
      {
        ?> 
        <h2 style="padding-top:5px;"><?php echo $record1['subject']; ?></h2>
        <div id="header1" style="position:relative;"></div>
        <div id="maintext" style="position:relative;">
          <p><?php echo $record1['quicktext']; ?></p>
          <a href="blog_articles.php?ID=<?php echo $record1["id"];?>">View Article - <?php echo date("D jS F Y g:iA",strtotime($record1["posted"]));?></a>
        </div>
        <div id="text_footer"></div>
        <?php 
      }
      mysql_free_result($result1);
    ?>
  </div>
  <?php
    include 'include/sidebar.php';
  ?>        

</div>

Table1 blog_entries: id posted subject body quicktext

Table2 blog_comments: id blog_id posted name comment

Answer 1

We'd need to know your database schema to be sure, but it's probably something like this...

SELECT COUNT(comment.id) AS numcomments,
    entry.id,entry.posted,entry.subject,entry.quicktext
FROM `blog_comments` comment, `blog_entries` entry
WHERE comment.blog_id=entry.id
GROUP BY entry.posted, entry.subject, entry.quicktext

Answer 2

I understand what your saying, Maybe this would be better if I did this has an exampleenter code here....

    $input_now1 = "SELECT blog_entries.id, blog_entries.posted, blog_entries.subject, blog_entries.quicktext
      FROM blog_entries
      ORDER BY blog_entries.posted DESC
      LIMIT 0, 3;";

    $result1 = mysql_query($input_now1);

    $input_now2 = "SELECT COUNT( blog_id )
FROM blog_comments WHERE blog_id = 'blog_entries.id'";

    $result2 = mysql_query($input_now2);
    $record2 = mysql_fetch_assoc($result2)
    ?>
    <div id="middle">

      <div id="box1">
        <?php
          while($record1 = mysql_fetch_assoc($result1))
          {
            ?> 
            <h2 style="padding-top:5px;"><?php echo $record1['subject']; ?></h2>
            <div id="header1" style="position:relative;"></div>
            <div id="maintext" style="position:relative;">
              <p><?php echo $record1['quicktext']; ?></p>
              <p>Comments: (<?php echo $record2['blog_id']; ?></p>
              <a href="blog_articles.php?ID=<?php echo $record1["id"];?>">View Article - <?php echo date("D jS F Y g:iA",strtotime($record1["posted"]));?></a>
            </div>
            <div id="text_footer"></div>
            <?php 
          }
          mysql_free_result($result1);
        ?>
      </div>
      <?php
        include 'include/sidebar.php';
      ?>        

    </div>

I'm sorry if I'm not clear on your end. I been trying to figure this out for a couple of days and I don't know if I over thinking it.

Answer 3

I would just add something like this anywhere within boundary of the loop, kind of move your code around. After loop starts you will collect ID then you'll be able to run COUNT against and echo the specific count. Not been a loop it will not process unless the loop has ran again...

    <?php       
    $input_now2 = "SELECT COUNT( blog_id ) FROM blog_comments WHERE blog_id = 
'$result1[id]'";
    $comments_count = mysql_query($input_now2);
    echo '<p>' . $comments_count . '</p>';  
    ?>

You can put something similar as above anywhere inside your loop for $result1....

Hope I have understood what your trying to do.