if statement within mysql query

[rcouser]

Hi,

I'm having trouble trying figure out this mysql query. the query is as follows

 

SELECT t2.id, t2.title, t2.gender, t3.title AS employment_status
FROM wearer_wardrobes t1
LEFT JOIN wardrobes t2 ON t1.wardrobe_id = t2.id
LEFT JOIN employment_status_options t3 ON t2.employment_status = t3.id
WHERE t1.wearer_id = $order_wearer_id

if(t1.overwrite_prevent_ordering == 1 && t1.early_date != NULL){
    AND (CURDATE() >= t1.early_date AND CURDATE() < t1.end_date)
}elseif(t1.overwrite_prevent_ordering == 1){
    AND (CURDATE() >= t1.start_date AND CURDATE() < t1.end_date)
}elseif(t1.early_date != NULL && t1.prevent_date != NULL){
    AND (CURDATE() >= t1.early_date AND CURDATE() < t1.prevent_date)
}elseif(t1.early_date != NULL){
    AND (CURDATE() >= t1.early_date AND CURDATE() < t1.end_date)
}elseif(t1.prevent_date != NULL){
    AND (CURDATE() >= t1.start_date AND CURDATE() < t1.prevent_date)
}else{
    AND (CURDATE() >= t1.start_date AND CURDATE() < t1.end_date)
}

ORDER BY t2.title ASC, t3.title ASC, t2.gender ASC

 

Is this possible wth mysql or must I select all results and filter then out with php

Thanks

[Muddy_Funster]

that's not the correct syntax for mysql if statments.

if(condition_Check, action_whenTrue, action_whenFalse)

also, if conditionals are part of the SELECT syntax, not the WHERE as far as I know.

 

What I'm having trouble with is trying to work out what your are doing with all that...

[rcouser]

Hi Muddy_Funster,

Thanks for your reply. Yeah I know the if conditionals are not correct, I only tried to explain the logic like that as i'm not sure how else to do it.

So what i'm trying to do is the following:

The database has a long list of wardrobes

Each wearer is assigned multiple wardrobes but with a date range of when they can view this wardrobe

There might be some cases were they might be able to view this wardrobe before the start_date ie. early_date and some cases were they might be prevented from viewing the wardrobe before the end_date ie. prevent_date

Hope that helps you understand

Edited May 8, 2015 by rcouser

[Barand]

Consider a trivial example to find male employees born between 1960 and 1979 and females born between 1980 and 1999

SELECT id
, name
, dob
, gender
FROM 
    employee
WHERE YEAR(dob) BETWEEN 
    IF(gender = 'M', 1960, 1980) 
    AND 
    IF(gender = 'F', 1979, 1999)
ORDER BY dob

This should demonstrate the IF syntax.

 

Also, you can't use "= NULL" or "!= NULL; the correct syntax is "IS NULL" or "IS NOT NULL"

 

Of course, the above would normally be

SELECT id
, name
, dob
, gender
FROM 
    employee
WHERE 
    (gender = 'M' AND dob BETWEEN 1960 AND 1979)
    OR
    (gender = 'F' AND dob BETWEEN 1980 AND 1999)
ORDER BY dob

Edited May 8, 2015 by Barand

[Muddy_Funster]

so something like the following psudo?

 

WHERE (
(
(Start_Date <= Today OR Early_Date <= Today)
OR
(Start_Date <= Today AND Early_Date IS NULL)
)
AND
(
(End_Date >= Today AND Prevent_Date <= Today)
OR
(End_Date >= Today AND Prevent_Date IS NULL)
)
)

[rcouser]

Thanks folks,

I tried doing something similar Muddy_Funster and kind of got it working but it's when the overwrite_prevent_ordering = 1 comes into play. it needs to overwrite the prevent_date settings.

[rcouser]

for example

SELECT t1.overwrite_prevent_ordering, t2.id, t2.title, t2.gender, t3.title AS employment_status,
DATE_FORMAT(t1.start_date, '%Y-%m-%d') AS start_date,
DATE_FORMAT(t1.end_date, '%Y-%m-%d') AS end_date,
DATE_FORMAT(t1.early_date, '%Y-%m-%d') AS early_date,
DATE_FORMAT(t1.prevent_date, '%Y-%m-%d') AS prevent_date
FROM wearer_wardrobes t1
LEFT JOIN wardrobes t2 ON t1.wardrobe_id = t2.id
LEFT JOIN employment_status_options t3 ON t2.employment_status = t3.id
WHERE t1.wearer_id = $order_wearer_id
AND (
    (CURDATE() >= t1.start_date OR CURDATE() >= t1.early_date)
    AND
    (
        (CURDATE() < t1.end_date AND t1.prevent_date IS NULL) OR (CURDATE() < t1.end_date AND CURDATE() < t1.prevent_date)
    )
)

[Muddy_Funster]

what do you meen "Over Write"?

[rcouser]

When overwrite_prevent_ordering equals 1

 

 (CURDATE() < t1.end_date AND t1.prevent_date IS NULL) OR (CURDATE() < t1.end_date AND CURDATE() < t1.prevent_date)

 

needs to become

 

 (CURDATE() < t1.end_date)

[Psycho]

The format of your WHERE clause appears to be more complicated than it needs to be. Also, FORMAT complicated logic so you can see the structure - it helps a lot. I made some modifications and put in the exception for when overwrite_prevent_ordering equals 1.

 

SELECT t1.overwrite_prevent_ordering, t2.id, t2.title, t2.gender, t3.title AS employment_status,
       DATE_FORMAT(t1.start_date, '%Y-%m-%d') AS start_date,
       DATE_FORMAT(t1.end_date, '%Y-%m-%d') AS end_date,
       DATE_FORMAT(t1.early_date, '%Y-%m-%d') AS early_date,
       DATE_FORMAT(t1.prevent_date, '%Y-%m-%d') AS prevent_date
 
FROM wearer_wardrobes t1
LEFT JOIN wardrobes t2 ON t1.wardrobe_id = t2.id
LEFT JOIN employment_status_options t3 ON t2.employment_status = t3.id
 
WHERE t1.wearer_id = $order_wearer_id
 
  AND (CURDATE() >= t1.start_date OR CURDATE() >= t1.early_date)
  AND
  (
        (
         overwrite_prevent_ordering <> 1
         AND CURDATE() < t1.end_date
         AND (t1.prevent_date IS NULL OR CURDATE() < t1.prevent_date)
        )
        OR
        (
         overwrite_prevent_ordering = 1
         AND CURDATE() < t1.end_date)
        )
  )

[rcouser]

Brilliant Psycho, thats exactly what I need. Thanks very much everyone for your help.

[Psycho]

I just saw there was still some duplication, the "AND CURDATE() < t1.end_date" is duplicated between the OR conditions - so it can be simplified to

 

 

WHERE t1.wearer_id = $order_wearer_id
 
  AND (CURDATE() >= t1.start_date OR CURDATE() >= t1.early_date)
  AND CURDATE() < t1.end_date
  AND
  (
        (
         overwrite_prevent_ordering <> 1
         AND (t1.prevent_date IS NULL OR CURDATE() < t1.prevent_date)
        )
        OR
        (
         overwrite_prevent_ordering = 1
        )
  )