Attempt - which returns all registrations that has reportDate = CURRENT WEEK.
SELECT *
FROM registrations
LEFT JOIN reports on registrations.registration=reports.registration
WHERE reports.reportDate
between cast(timestampadd(SQL_TSI_DAY, -(dayofweek(curdate())-2), curdate()) as date)
and cast(timestampadd(SQL_TSI_DAY, 7-(dayofweek(curdate())-1), curdate()) as date)
I want to achieve the following, but I do not know how to achieve it using PHP.
First, get all of "registration" from table Registrations,
then check if the registrations exist in the table Reports WHERE reportDate is equal to the current WEEK
(i.e. current week being Monday-Sunday, at todays time and date that would equal to the date 26th Aug to 1st Sept).
finally, display all the registrations in a select input (must be echoed as the select input is further down the html file) and the ones that have a reportDate = current week, have them visible but disabled.
Registrations
| id | registration |
|---|---|
| 1 | EXAMPLE |
| 2 | EXAMPLE2 |
| 3 | EXAMPLE3 |
Reports
| id | registration | reportDate |
|---|---|---|
| 1 | EXAMPLE | 2024-08-19 |
| 2 | EXAMPLE | 2024-08-22 |
| 3 | EXAMPLE2 | 2024-08-22 |
| 4 | EXAMPLE3 | 2024-08-22 |
| 5 | EXAMPLE | 2024-08-27 |
| 6 | EXAMPLE3 | 2024-08-27 |
in the example above, EXAMPLE and EXAMPLE3 registration should be visible but disabled and EXAMPLE2 should be clickable in the select field based on the time of posting and the CURRENT WEEK of this question. Which should turn out like the code below
<select oncopy="return false" id="inspectReg" name="inspectReg" class="reg-control">
*Somewhat...a for each loop? or while?*
<option value="EXAMPLE" disabled>EXAMPLE</option> (record exists dated 27th - so disabled)
<option value="EXAMPLE2">EXAMPLE2</option> (record for current week does not exist)
<option value="EXAMPLE3" disabled>EXAMPLE3</option> (record exists dated 27th - so disabled)
</select>
Can you try this one
SELECT
r.registration,
CASE WHEN rp.registration IS NOT NULL THEN 'disabled' ELSE '' END AS status
FROM registrations r
LEFT JOIN reports rp
ON r.registration = rp.registration
AND rp.reportDate BETWEEN CAST(TIMESTAMPADD(DAY, -(DAYOFWEEK(CURDATE())-2), CURDATE()) AS DATE)
AND CAST(TIMESTAMPADD(DAY, 7-(DAYOFWEEK(CURDATE())-1), CURDATE()) AS DATE);