Jump to content

if statement within mysql query


rcouser
Go to solution Solved by Psycho,

Recommended Posts

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

Link to comment
Share on other sites

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 by rcouser
Link to comment
Share on other sites

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 by Barand
Link to comment
Share on other sites

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)
    )
)

Link to comment
Share on other sites

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)

Link to comment
Share on other sites

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)
        )
  )
Link to comment
Share on other sites

  • Solution

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
        )
  )
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.