CODEWITHSUNDEEP
phpmysqlwhile-looparchive
Ali
Ali

Reputation: 1058

create monthly archive with PHP and MySQL

I'm trying to create a news archives in my website. i wrote this code:

$sql_result = $db->query("
SELECT *,COUNT(id) AS itemCount 
FROM post 
GROUP BY DATE_FORMAT(date, '%Y-%m') DESC ");

while ( $row = $db->get_row( $sql_result ) ) {
 $datetime = strtotime($row['date']);
 $tday = date("Y-m", $datetime);
 $count = $row['itemCount'];
 echo "Month: {$tday} - News Number: {$count}<br>";
}

And this is the result:

Month: 2013-06 - News Number: 4
Month: 2013-05 - News Number: 3
Month: 2013-04 - News Number: 4
Month: 2013-03 - News Number: 3

My question is, how i can show news title's in every months after that month? for example something like this:

Month: 2013-06 - News Number: 4
 -news number 1 for this month
 -news number 2 for this month
 -news number 3 for this month
 -news number 4 for this month
Month: 2013-05 - News Number: 3
 -news number 1 for this month
 -news number 2 for this month
 -news number 3 for this month
Month: 2013-04 - News Number: 4
 -news number 1 for this month
 -news number 2 for this month
 -news number 3 for this month
 -news number 4 for this month
Month: 2013-03 - News Number: 3
 -news number 1 for this month
 -news number 2 for this month
 -news number 3 for this month

Upvotes: 2

Views: 3647

Answers (4)

Pradip G. Vanparia
Pradip G. Vanparia

Reputation: 53

Test full Example of yearly archive with PHP and MySQL (like blog) 

<link href="CSS/treeview.css" rel="stylesheet" />
    <script src="scripts/jquery-1.11.1.min.js"></script>
    <script>
        $(function () {
            //start the tree in an autocollapsed state
            $('#Decor ul').hide(400);

            $('#Decor li').on('click', function (e) {
                e.stopPropagation(); // prevent links from toggling the nodes
                $(this).children('ul').slideToggle();
            });

            // This code opens all hyperlinks in a new window 
            // and avoids anchors
            $('#Decor a').not('[href="#"]').attr('target', '_blank');
        });
    </script>
</head>

<?php
define("DB_USER","");
define("DB_PASS","");
define("DB_HOST","");
define("DB_NAME","");

$link = mysqli_connect(DB_HOST, DB_USER, DB_PASS, DB_NAME) or die(mysqli_errno());
$s="SELECT *,content_id,COUNT(content_id) AS itemCount FROM content_mast GROUP BY DATE_FORMAT(date_upload,'%Y') DESC";
$sql_result=mysqli_query($link,$s);
?>
<ul id="Decor">
<?php
while($row=mysqli_fetch_array($sql_result))
    {

    $datetime=strtotime($row['date_upload']);
    $tday = date("Y", $datetime);
    $count = $row['itemCount'];

    ?>
     <li class="level1"><?php echo "<u><strong>{$tday} ({$count})</strong></u><br>"; ?>
    <?php
        $s1="select * from content_mast where DATE_FORMAT(date_upload,'%Y')=$tday";
        $q=mysqli_query($link,$s1);
        while($month=mysqli_fetch_row($q))
        {
        ?>
            <ul>
               <li><a href="#"><?php echo date("M",strtotime($month[5]))."<br>"; ?></a></li>
            </ul>
            <?php


        }

    echo "<br>";

}
?>
</ul>
</html>

Upvotes: 0

Pradip G. Vanparia
Pradip G. Vanparia

Reputation: 53

create yearly archive with PHP and MySQL

  $link = mysqli_connect(DB_HOST, DB_USER, DB_PASS, DB_NAME) or die(mysqli_errno());
$s="SELECT *,COUNT(content_id) AS itemCount FROM content_mast GROUP BY DATE_FORMAT(date_upload, '%Y') DESC ";
$sql_result =mysqli_query($link,$s);

while ($row = mysqli_fetch_array($sql_result))
    {
    $datetime = strtotime($row['date_upload']);
    $tday = date("Y", $datetime);
    $count = $row['itemCount'];
    echo "Month: {$tday} - News Number: {$count}<br>";

    for($i=1; $i<=$count; $i++)
    {
    echo "-news number {$i} for this month<br>";
    }
}

enter image description here

about output image.

Output

Upvotes: 0

Nitesh Mishra
Nitesh Mishra

Reputation: 311

Try using this code.

 while ( $row = $db->get_row( $sql_result ) ) {
  $datetime = strtotime($row['date']);
  $tday = date("Y-m", $datetime);
  $count = $row['itemCount'];
  echo "Month: {$tday} - News Number: {$count}<br>";
  $sql_result1 = $db->query("
  SELECT newsTitle 
  FROM post 
  WHERE DATE_FORMAT(date, '%Y-%m')=DATE_FORMAT({$datetime}, '%Y-%m')");
  while($row1 = $db->get_row( $sql_result1 ) ){
   echo "News: {$row1['newsTitle']}";
  }
 }

Upvotes: 1

Jahanzeb
Jahanzeb

Reputation: 613

You can add a loop in your while loop, like this;

while ( $row = $db->get_row( $sql_result ) ) {
  $datetime = strtotime($row['date']);
  $tday = date("Y-m", $datetime);
  $count = $row['itemCount'];
  echo "Month: {$tday} - News Number: {$count}<br>";

  /////////////////////////
  // You can add a sql query here, as you added before while loop, to get each
  // month's data on the basis of unique-id which you have in '$row'
  /////////////////////////

  for($i=1; $i<=$count; $i++)
  {
    echo "-news number {$i} for this month<br>";
  }
}

Upvotes: 0

Related Questions