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

Borealid · Accepted Answer · 2010-07-15 00:03:22Z

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

edited Jul 15, 2010 at 0:03

answered Jul 14, 2010 at 21:21

Borealid

99.3k9 gold badges111 silver badges123 bronze badges

Sign up to request clarification or add additional context in comments.

8 Comments

blackbull77 Over a year ago

Table1: blog_entries id posted subject body quicktext Table2: blog_comments id blog_id posted name comment

Borealid Over a year ago

@blackbull7: you should put that in the original question, in a more readable format. I've updated my answer to match your schema.

blackbull77 Over a year ago

I updated the question. How who I be able to pull it off with a while loops in php or do I use another function?

Borealid Over a year ago

@blackbull77: That query doesn't require a loop: it gets the count for all entries. I don't really understand what you're asking now. If you want to get the number of comments for a particular entry, just SELECT COUNT(id) FROM blog_comments WHERE blog_id=foo.

blackbull77 Over a year ago

I'm not talking about SQL. I'm talking about display the comment count in PHP. Right now, $record1 is looping 3 of the articles, how do I display the second query showing the number of comments when $record1 is using a while function in PHP?

|

Codex73 · Accepted Answer · 2010-07-15 00:47:49Z

1

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.

answered Jul 15, 2010 at 0:47

Codex73

5,80613 gold badges58 silver badges77 bronze badges

Comments

blackbull77 · Accepted Answer · 2010-07-14 23:39:59Z

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.

Collectives™ on Stack Overflow

2 sql in one php while loop

3 Answers 3

8 Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

3 Answers 3

8 Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related