Jump to content

Display records on week format


Go to solution Solved by requinix,

Recommended Posts

I have data stored in datetime format, like this 

 

post-169154-0-44910700-1517636812_thumb.png

 

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

 

post-169154-0-69388300-1517637677_thumb.png

 

Can somebody suggest me how can i display it in php

 

 

Link to post
Share on other sites

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
Link to post
Share on other sites

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> 

post-169154-0-80196600-1517640815_thumb.png

Edited by michelle1404
Link to post
Share on other sites
  • Solution

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;
	}
}
?>
  • Like 1
Link to post
Share on other sites
This thread is more than a year old.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • 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.