Mysql Question posted in
The official MySQL documentation can be found here.

Mysql – Check if a record from one table exists in another and disable option

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>
Tags:

2

Answers


  1. 0

    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);
    Login or Signup to reply.
  2. 0

    Your main mistake is your WHERE clause. If a registration has no match in reports, then you outer join an empty reports row (i.e. all columns are null). Then, in your WHERE clause, you restrict your results to rows of particular reports.reportDates. This dismisses all outer joined rows and your join is turned thus into a mere inner join. When outer joining rows, then have your criteria in the ON clause, not in the WHERE clause.

    Anayway, when checking the existence of a match we prefer EXISTS over joins:

    SELECT 
  rg.*,
  EXISTS
    (
      SELECT null
      FROM reports rp
      WHERE rp.registration = rg.registration 
      AND YEARWEEK(rp.reportDate, 1) = YEARWEEK(CURDATE(), 1)
    ) AS has_current_week_report_date
FROM registrations rg;

    Demo: https://dbfiddle.uk/6LaRUFgs

    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search