Show data for current week but from Monday to Sunday

[ianhaney1]

I am developing a new system that needs to show upcoming collections data for the current week but the current week starts on Monday and ends on Sunday, I can’t seem to get the data returned for what I need, below is the query I currently have but the data is returning from today and up to 7 days

<?php                                            
$hostname="localhost";
$username="username";
$password="password";
$db = "databasename";
$dbh = new PDO("mysql:host=$hostname;dbname=$db", $username, $password);
foreach($dbh->query('SELECT id, location, DATE_FORMAT(colldate,"%d/%m/%Y") AS colldate
FROM collections WHERE colldate > DATE_SUB(NOW(), INTERVAL 1 WEEK) ORDER BY id ASC') as $row) {    
?>

[ianhaney1]

I think I have managed to solve it using the following query

SELECT * 
FROM collections
WHERE WEEKOFYEAR(colldate)=WEEKOFYEAR(CURDATE())

It seems to output the data for this week and not include the row for the date I put in as the 03/04/2019 so guessing the query is right or is it still wrong?

[Mittineague]

For me, calendar weeks begin on Sunday so I have never tried it, but MySQL has a WEEK() function.

https://dev.mysql.com/doc/refman/5.5/en/date-and-time-functions.html#function_week

… The two-argument form of WEEK() enables you to specify whether the week starts on Sunday or Monday …