dragonball dragonball - 1 month ago 9
MySQL Question

How to display data based on post date similar to a timeline using PHP MYSQL

please help me I'm really in a great problem. I want to display posts(articles) under their respective posting date.

For example,

NOVEMBER 05
* Post 1
* Post 2
* Post 3

NOVEMBER 04
* Post 1
* Post 2


I have written a PHP code but it's repeating dates and values twice, like:

NOVEMBER 05
* Post 1
* Post 2
* Post 3
NOVEMBER 05
* Post 1
* Post 2
* Post 3

NOVEMBER 04
* Post 1
* Post 2

NOVEMBER 04
* Post 1
* Post 2


Please help me!

Here's my code:

<?php
include("db.php");
include("function.php");
$result=mysqli_query($con,"select distinct post_submitted_on from posts order by id desc");
while($row=mysqli_fetch_array($result)){
$datetime=$row['post_submitted_on']; //2016-11-05 23:10:45
$date=date("d",strtotime($datetime)); //05
$month=date("F",strtotime($datetime)); //November
$final_date=date("Y-m-d",strtotime($datetime)); //2016-11-05
?>
<h4 class="date">
<i style="vertical-align: baseline;" class="fa fa-calendar-o date"></i> <B class="date">TODAY, <?php echo strtoupper($month);?> <?php echo ordinal("$date");?></B> <!-- prints TODAY, NOVEMBER 05 -->
</h4>
<?php
$sql=mysqli_query($con,"select distinct * from posts where post_date='$final_date' order by id desc"); //selecting all posts based on the current date and load all past entries based on past date
while($r=mysqli_fetch_array($sql)){
?>
<table width="100%" class="hidden-sm hidden-xs">
<tr>
<td>
<?php echo $r['title'];?>
<?php echo $r['url'];?>
</td>
</tr>
</table>
<?php } ?>
<?php } ?>

Answer

I'll just post this as a community wiki since it was mentioned by myself in comments and being the solution to this question.

  • Add a GROUP BY col to your query, since using DISTINCT doesn't always do the job.

Consult the following Q&A on Stack: