PHP MySQL Table w AJAX: Filling in data for the whole year (day by day, only showing 7 days at once)

Question

I’m struggling on how to design this small application. It’s going to be a table info where the columns will be dates and only 7 dates will be shown at any given time (the current day, plus the next six days).

The user NEEDS to be able to scroll to the left (or right, I suppose) and see previous dates with information that has bee entered for specific dates (ie, user wants to see the # of Service Jobs for February 14th, 2015).

What I have so far:

In this case there are 6 rows of information. The user can go in and edit this information. All of this data needs to be posted to a database, and the dates need to account for the whole year (ie, 1/1 to 12/31) and this has to happen every year. I’ve worked with the x-editable plugin a bit and have had some success, but I’m having a difficult time understanding how to account for all the differernt days of the year (365) and how to incorporate that into a MySQL database.

Current'y the 6 dates you see at the top of the img are shown using Javascript. The little dotted lines below the row values are from the X-editable plugin, and when you update this info it is posted to a database with a timestamp.

So my question is: how in the world do I account for 365 days for the year? It's not like I want 365 columns, each representing one day of the year, that seems crazy. (and the same thing will have to happen for 2016, 2017, etc etc...ongoing data).

I'm not sure if I've been taking the right approach on this. Feel like I'm making it much more painful than it needs to be.

Any input on how to design such an application would be greatly appreciated.

Answer 1

From a high level; I would just use an indexed DATE column.

A quick example:

<?php
$date_start = date('Y-m-d'); // Today
$date_end = date('Y-m-d', strtotime($date_start . ' + 6 days'));
// Assuming you have a PDO DB handler setup at this point...
$stmt = $dbh->prepare('SELECT * FROM `table` WHERE `day` BETWEEN ? AND ? ORDER BY `day` ASC');
$stmt->execute(array($date_start, $date_end));
$res = $stmt->fetchAll();
var_dump($res);