How to auto count previous 6 months annoucement record in SQL put in the separate box?

I have facing big problem to auto count previous 6 months annoucement record in SQL put in the separate box. Is using loop to count the annoucement record? Anyone can guide me or give me an example to do it? If can, better using my code to edit and let me refer. Thanks a lot.

Below is my coding:

    <?php
    $sql_select = 'SELECT * FROM announcement where id = 20' ; //This I try to test id = 20 announcment to call out data

    $query_select = db_conn_select($sql_select);
    foreach ($query_select as $rs_select) {
    $title = $rs_select['title'];
    $date = $rs_select['posted_date'];
    $contents = $rs_select['contents'];
     }

     ?>

   <div class="row">
   <div class="col-md-12">
  <div class="box box-success box-solid">
    <div class="box-header with-border" style="text-align: center;" >
      <h3 class="box-title" ><?php echo $title ?> (<?php echo $date ?>)</h3>

       <div class="box-tools pull-right">
       <button type="button" class="btn btn-default btn-xs collapse-box" data-toggle="collapse" data-target="#collapseExample1" aria-expanded="false" aria-controls="collapseExample"><i class="fa fa-minus"></i></button>
      </div>

    </div>

   <div id="collapseExample1" class="collapse" style="overflow: auto; text-align: center;">
    <?php echo $contents ?>
    </div>

    </div>

  </div>

 </div>

Below is my database information （I want get the posted_date previous 6 months annoucement record to show）：

My coding output show like the below:

Actually I want the output like below show auto count previous 6 months announcements put in the separate box :

Answer

There are two problems:

1) Your query will only return one record. You need to change it so it fetches a set of records from the last 6 months.

The general pattern for finding the last 6 months is easy to find online

yourdatecolumn >= CURDATE() - INTERVAL 6 MONTH

(credit to this answer specifically in this case, but there are dozens of examples available). N.B. You didn’t specify, but I’m assuming your DBMS is MySQL, since that’s most commonly used with PHP. If you have a different DBMS, you can find an equivalent answer online with a few seconds of searching.

2) you’re ending your loop too soon. The current code is ok for one record, but will fail when there is more than one. It will loop over all the records, but then keep assigning their values the same variables, so after the loop finishes you end up with just the last record’s values inside those variables. And you are not using a loop to create the HTML multiple times.

To fix that, just bring the HTML inside the loop. Here’s the final version:

<?php
$sql_select = 'SELECT * FROM announcement where posted_date >= CURDATE() - INTERVAL 6 MONTH';

$query_select = db_conn_select($sql_select);
foreach ($query_select as $rs_select) {
  $title = $rs_select['title'];
  $date = $rs_select['posted_date'];
  $contents = $rs_select['contents'];
  ?>

<div class="row">
<div class="col-md-12">
  <div class="box box-success box-solid">
    <div class="box-header with-border" style="text-align: center;">
      <h3 class="box-title">
        <?php echo $title ?> (
        <?php echo $date ?>)</h3>

      <div class="box-tools pull-right">
        <button type="button" class="btn btn-default btn-xs collapse-box" data-toggle="collapse" data-target="#collapseExample1" aria-expanded="false" aria-controls="collapseExample"><i class="fa fa-minus"></i></button>
      </div>

    </div>

    <div id="collapseExample1" class="collapse" style="overflow: auto; text-align: center;">
      <?php echo $contents ?>
    </div>

  </div>

</div>

</div>
<?php 
} 
?>

That way it will create the same HTML repeatedly for each record, and use the current values of your variables within each version.