This is my MySQL table:
p_id product_name description added_date
-----------------------------------------------------------------------
1 headphone headphone 2012-06-23 10:20:11
2 cdplayer cdplayer 2012-06-23 12:03:51
3 computerbag computerbag 2012-06-23 04:10:38
4 lcd lcd 2012-06-23 08:40:15
5 monitor monitor 2012-06-10 05:11:55
6 mouse mouse 2012-06-10 09:12:45
7 lasermouse lasermouse 2012-06-10 11:54:12
8 keyboard keyboard 2012-05-28 07:18:21
9 camera camera 2012-05-28 10:20:11
10 charger charger 2012-05-28 10:55:33
I want my results to look like:
23 Jun
10 Jun
28 May
This is not exactly the format you want. You have to change a little bit in formatting for example with <ul> and <li> tags.
<?php
$con = mysqli_connect... //Database connection
$result = mysqli_query($con, "SELECT product_name,DATE(added_date) AS only_date FROM YOURTABLENAME ORDER BY DATE(added_date) DESC,p_id ASC");
$currentDate = "";
while($data = mysqli_fetch_object($result)) {
if($currentDate != $data->only_date) {
echo $data->only_date."<br>"; //You have to format this, now it's YYYY-MM-DD
$currentDate = $data->only_date;
}
echo $data->product_name."<br>";
}
?>
$result = mysql_query("SELECT * FROM `test` group by `added_date` order by p_id");
$current_date="";
$i=0;
while ($row = mysql_fetch_array($result))
{
$row_date=explode(" ",$row['added_date']);
if($current_date!=$row_date[0])
{
if($i!=0){echo"</ul>".date("jS F", strtotime("$row_date[0]"))."<ul>";}
//$new_date=explode(" ",$row['added_date']);
$current_date=$row_date[0];
if($i==0)
{
$i++;
echo "<ul>".date("jS F", strtotime("$row_date[0]"));
}
echo "<li>".$row['product_name']."</li>";
}
else
{
echo "<li>".$row['product_name']."</li>";
}
}
First you need to retrieve all your table values using a mysql statement like so. Assigning the result a variable name.
SELECT * FROM table
So if we say $results is the variable that contains all your table values etc.
Then using PHP. You can loop through all the results
foreach($results as $result)
{
$date = date('d F', strtotime($result['added_date']));
$products[$date][] = $result['product_name'];
}
Now that we have all the product names grouped by the date, we need to loop through one more time.
foreach($products as $key=>$product)
{
echo $key;
echo '<ul>';
foreach($product as $productName)
{
echo '<li>' . $productName . '</li>';
}
echo '</ul>';
}
This is the best answer I can give you with the information provided, if you make changes then I will certainly improve this. But this is the basic principle of what needs to be done.