PHP Sorting data mysql by day per week

Question

I have been trying to find solution to this issue. So here I give an example :

 date       item
-------------------
1-Jan-19    aaa
2-Jan-19    bbb
3-Jan-19    ccc
4-Jan-19    ddd
5-Jan-19    eee
6-Jan-19    fff
7-Jan-19    ggg
8-Jan-19    hhh
9-Jan-19    iii
10-Jan-19   jjj
11-Jan-19   kkk

So far I try with this code, but this its only show data separated by 7 data per row. This code that I use,

 <?php

$i     = 0;
$limit = 7;

$query = mysqli_query("SELECT * FROM mytable");
foreach ($query as $query) {
    if ($i >= $limit) {
        echo "<tr></tr>";
        $i = 0;
    }
    $i++;
?>   
<td><?php echo $query['item'];?></td> <?php } ?>

that show,

 #monday #tuesday #wednesday #thursday #friday #saturday #sunday
   aaa      bbb       ccc       ddd      eee      fff       ggg     
   hhh      iii       jjj       kkk

I want to show data like this,

#monday #tuesday #wednesday #thursday #friday #saturday #sunday
  ---     aaa      bbb         ccc     ddd      eee       fff
  ggg     hhh      iii         jjj     kkk

How to fix this? No need to show name of days, above just for example

if i understand , you want to convert the value of date to the name of day and list above each day the items that bellow to the same day ? — Yassine CHABLI, Jan 22 '19 at 22:19
Yes, You are right. Should I use concat method or what? @MohammedYassineCHABLI — script888, Jan 22 '19 at 22:21
Convert the column `date` data into native date datatype dont use somekind of own format.. If you are limited to change that because the table is already running on a application.. Take a look into [Date and Time Functions](https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html) to be more clear the `STR_TO_DATE()`, `DATE_FORMAT( ) (for the formats to use)` and `WEEK()` functions... Then you need to [pivot](https://stackoverflow.com/questions/7674786/mysql-pivot-table) meaning converting the records into columns. — Raymond Nijland, Jan 22 '19 at 22:25
It's really simple. If you're not going to bother with data types, don't bother with sql. The rest is noise. — Strawberry, Jan 22 '19 at 22:31

GMB · Accepted Answer · 2019-01-23T00:12:00.133

As explained in the comments, this can be done with pure SQL.

You can use conditional aggregation to pivot the data over the days of the week, and group them by week of the year. I used a subquery to avoid repeating conversions (and typing...) in the outer query.

SELECT 
    t.week_year,
    MAX(CASE WHEN t.week_day = 1 THEN t.item END) monday,
    MAX(CASE WHEN t.week_day = 2 THEN t.item END) tuesday,
    MAX(CASE WHEN t.week_day = 3 THEN t.item END) wednesday,
    MAX(CASE WHEN t.week_day = 4 THEN t.item END) thursday,
    MAX(CASE WHEN t.week_day = 5 THEN t.item END) friday,
    MAX(CASE WHEN t.week_day = 6 THEN t.item END) saturday,
    MAX(CASE WHEN t.week_day = 0 THEN t.item END) sunday
FROM (
    SELECT 
        date, 
        item, 
        DATE_FORMAT(date, '%w') week_day, 
        DATE_FORMAT(date, '%Y-%v') week_year 
    FROM mytable
) t
GROUP BY t.week_year
ORDER BY t.week_year

Tips :

date format specifier '%w' is the day of the week, where 0 is Sunday and 6 is Saturday
%v is the week of the year, where Monday is the first day of the week

This answer assumes that the date column is of date datatype (DATE, DATETIME, TIMESTAMP). If not, you would need to add a conversion in the subquery, using STR_TO_DATE.

PHP Sorting data mysql by day per week

1 Answers1