-1

I'm writing a script using PHP & MySQL where I can record the shifts I work (HGV driver).

Upon posting the form data PHP calculates shift duration, wages accumulated, overtime, distance driven, etc, and stores it in the MySQL database.

I want to then display all shifts in a table but group them by my pay week which unfortunately starts on a Sunday.

If the pay week was Mon-Sun I wouldn't have this problem as I could use week numbers but I can't due to the week starting on a Sunday.

My code is as follows:

^^^^^^^^^^^^^^^^^^^
// DB Connection // 

// Return the earliest shift in the database //
$result = $db->query("SELECT * FROM `shifts` ORDER BY `shift_start` ASC LIMIT 1");

$data = $result->fetch_assoc();

// Establish the previous Sunday //
$week_from  = strtotime(date('Y-m-d',mktime(0,0,0,date('m',$data['shift_start']),date('d',$data['shift_start']),date('y',$data['shift_start']))) . 'last sunday');

// PHP Loop Goes Here //

Firstly, is the above code the most efficient way of getting the start date (previous Sunday)?

Secondly, what's the best way to loop through the weeks where there are shifts?

TIA

Joundill
  • 6,828
  • 12
  • 36
  • 50
UKWS Ltd
  • 1
  • The MySQL [WEEK()](https://dev.mysql.com/doc/refman/8.0/en/date-and-time-functions.html#function_week) function has different modes of which half start on a Sunday. – KIKO Software Dec 06 '21 at 16:05
  • Can you provide an example of what your database structure & example data looks like? – Sutton Dec 06 '21 at 16:07
  • The shift start & finish time & dates are stored in the table as epoch timestamps. I'm contemplating setting up another table to store a timesheet in which i can create manually and associate the shifts to the timesheets table and return all shifts where timesheet ID = X. I think that will probably be the best option for now – UKWS Ltd Dec 07 '21 at 11:28

1 Answers1

0

This is a two part question, so I will try to cover them separately.

Regarding your first question, I would suggest using the MIN() function when selecting the smallest or earliest value in a database, and ensuring you have an index on the "shift_start" column. More information on the difference between MIN() and ORDER BY/LIMIT can be found here.

Then your query would look a something like this:

SELECT MIN(`shift_start`) FROM `shifts`;

Personally, I also find MIN() far more readable.

Now, for the other (and far more complicated) question:

You've not provided much detail on what your database (or the contents) looks like. Since you're using the PHP date function, I am assuming you're saving the timestamps as UNIX instead of MySQL TIMESTAMP/DATETIME types.

Firstly, I would suggest you migrate to using a TIMESTAMP/DATETIME column type. It'll simplify the query you're attempting to run. If you're unable to change to a TIMESTAMP/DATETIME column, then you can convert a UNIX timestamp to a DATETIME.

MySQL has a YEARWEEK() function that you can use to group by:

SELECT STR_TO_DATE(CONCAT(YEARWEEK(`shift_start`), ' Monday'), '%X%V %W') AS `date`, SUM(`wage`) AS `wage` FROM `shifts` GROUP BY YEARWEEK(`shift_start`);

This will output something similar to:

+------------+------+
|    Date    | Wage |
+------------+------+
| 2021-11-29 |   50 |
| 2021-12-06 |   15 |
+------------+------+
Sutton
  • 300
  • 2
  • 14