Display records on week format

[michelle1404]

I have data stored in datetime format, like this 

 

 

I want to display it Week-wise like this in php

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

Now , in my query i am getting like this

 

 

Can somebody suggest me how can i display it in php

 

 

[requinix]

Can you write (and then post) the code that presents all the results in the format you want except having them all mashed together and not broken apart by week. So like

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

[michelle1404]

This is what i did, 

<?php
ob_start();
include('inc/sessions.php');
include('inc/config.php'); 
include('inc/top.php');

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


?>

<!-- Page content -->
<div id="page-content" class="block">
<table class="table">
<tr><td>Days</td>
<?php
while($row = mysqli_fetch_array($mquery))
{
echo "<tr><td>".$row['dow']."</td>";
echo "<td>".$row['no_one']."</td>";
echo "<td>".$row['no_two']."</td>";
echo "<td>".$row['no_three']."</td></tr>";
}
?>
</tr>
</table>    
  </div> 

[requinix]

So that's a little bit different from what you originally described... I assume the

is what you'll be repeating for each week? One table per week?

 

Here's the idea. Since you're "grouping" the tables by week, use a variable to track which week you're looking at. When it's a new week you start a new table.

 

But before that it helps to rearrange the code a bit. Get the first row before the loop, then fetch new rows at the end.

<?php
$row = mysqli_fetch_array($mquery);
?>
<table class="table">
<tr><td>Days</td>
<?php
while ($row)
{
echo "<tr><td>".$row['dow']."</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);
}
?>
</tr>
</table>

The only big difference is that the fetch happens twice, once for the first row and later for subsequent rows.

 

With that change out of the way, here's the overall structure you'll be creating:

$row = fetch the first row
$week = empty
while ($row) {
	if $week is empty {
		start a new table
		$week = current week
	}

	show the data from $row
	$row = fetch the next row

	if no $row or $week != the week from the new $row {
		end the current table
		$week = empty
	}
}

Take a minute to understand how that works:

1. Start without any $week information

2. When the first $row comes in you'll start a new table and update $week

3. Show that $row and fetch the next one

4a. If the new $row matches the current $week then don't do anything and just continue on

4b. If the new $row does not match the current $week, or if there aren't any more rows at all, then finish off the table

5. Keep going until there are no more rows

 

Steps 2 and 4 are important because HTML tables have starting and ending pieces that you need to make sure are created properly - you can't just output each $row and put some "break" in between them when the week changes.

 

Taking that structure and turning it into PHP code creates this:

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

	echo "<tr><td>".$row['dow']."</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 '</table>';
		$week = null;
	}
}
?>

[michelle1404]

Thanks for the detailed code.  For 1st week, data is not displayig properly ... from 2nd week, it works perfectly

 

[michelle1404]

it is not considering Week-0, 

 

[requinix]

If $week == "0" then !$week will be true. You need to change that particular condition so it only examines whether $week is literally null.

[michelle1404]

Thanks a ton!