Displaying records in weekly format

[mythri]

I have data stored in datetime format, like this

 

 

I want to display it like this

Date          Monday  Tuesday  Wednesday  Thursday  Friday  Saturday

01/01/2018    8        0        2          4        8        1
to            5        2        4          0        4        7 
06/01/2018    1        6        3          8        3        0


08/01/2018    0        8        1          8        4        8  
to            2        8        1          9        1        7
13/01/2018    2        6        3          8        3        6

....

Now i am displaying as week basis like this

Week1                        Week2 
Monday    | 8 | 5 | 1  
Tuesday   | 0 | 2 | 6
Wednesday | 2 | 4 | 3
Thursday  | 4 | 0 | 8
Friday    | 8 | 4 | 3
Saturday  | 1 | 7 | 0

and query is

SELECT WEEK(dtime) AS week, DAYOFWEEK(dtime) AS dow, no_one, no_two, no_three
FROM MyTable
ORDER BY dtime ASC

How can i change my query to display as i wanted?

[requinix]

What's your code to display the table?

[mythri]

SELECT WEEK(dtime) AS week, DAYOFWEEK(dtime) AS dow, no_one, no_two, no_three
FROM MyTable
ORDER BY dtime ASC

Now am using this code, but not getting how to change this code for weeks date

[requinix]

That is not code. That is a SQL query.

 

What is your code that turns the results of that SQL query into the table you are currently seeing? Because the query is fine and is not what needs to change.

[mythri]

$msql = "SELECT WEEK(dtime) AS week, DAYNAME(dtime) AS dow, date(dtime) AS day,  no_one, no_two, no_three FROM MyTable ORDER BY dtime ASC";
$mquery = mysqli_query($con, $msql);

<?php
$row = mysqli_fetch_array($mquery);
$week = null;
while ($row)
{
	
	
	if (!$week && ($week!="0")) {
		echo '<table class="table">';
		echo '<tr><td><strong>Days</strong></td><td colspan="4"><strong>Week -  '.$row['week'].'</strong></td>';
		$week = $row["week"];
	}

	echo "<tr><td>".$row['dow']."</td>";
	echo "<td>".date('d-m-Y', strtotime($row['day']))."</td>";
	echo "<td>".$row['no_one']."</td>";
	echo "<td>".$row['no_two']."</td>";
	echo "<td>".$row['no_three']."</td></tr>";

	$row = mysqli_fetch_array($mquery);
	if (!$row || $week != $row["week"]) {
		echo '</tr></table>';
		$week = null;
	}
}
?>

This is what i am doing

Edited February 20, 2018 by mythri

[mythri]

@requinix :

Can you please have a look at my code and suggest me where i need to change

[mac_gyver]

because your data is not normalized and stored one data item per row, it is not possible to (easily) do this in the sql query. you will need to pre-process the retrieved data and pivot/index it first by the week number, then the no_one/no_two/no_three column name, then the day of the week number. you would then be able to loop over the pre-processed data and output it the way you want.

 

if your data was normalized, it would be possible to do this in the query by ordering the data by - week_number (using week()), position_number (a column holding the 1,2,or 3 value), day_number (using either dayofweek() or weekday()).

Edited February 21, 2018 by mac_gyver