Jump to content

Displaying records in weekly format


mythri

Recommended Posts

I have data stored in datetime format, like this

 

post-168283-0-29621900-1519104402_thumb.png

 

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?

Link to comment
Share on other sites

$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

Link to comment
Share on other sites

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()).

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.