rcouser Posted May 8, 2015 Share Posted May 8, 2015 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_statusFROM wearer_wardrobes t1LEFT JOIN wardrobes t2 ON t1.wardrobe_id = t2.idLEFT JOIN employment_status_options t3 ON t2.employment_status = t3.idWHERE t1.wearer_id = $order_wearer_idif(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 Quote Link to comment Share on other sites More sharing options...
Muddy_Funster Posted May 8, 2015 Share Posted May 8, 2015 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... Quote Link to comment Share on other sites More sharing options...
rcouser Posted May 8, 2015 Author Share Posted May 8, 2015 (edited) 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 Quote Link to comment Share on other sites More sharing options...
Barand Posted May 8, 2015 Share Posted May 8, 2015 (edited) 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 Quote Link to comment Share on other sites More sharing options...
Muddy_Funster Posted May 8, 2015 Share Posted May 8, 2015 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) ) ) Quote Link to comment Share on other sites More sharing options...
rcouser Posted May 8, 2015 Author Share Posted May 8, 2015 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. Quote Link to comment Share on other sites More sharing options...
rcouser Posted May 8, 2015 Author Share Posted May 8, 2015 for exampleSELECT 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_dateFROM wearer_wardrobes t1LEFT JOIN wardrobes t2 ON t1.wardrobe_id = t2.idLEFT JOIN employment_status_options t3 ON t2.employment_status = t3.idWHERE t1.wearer_id = $order_wearer_idAND ( (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) )) Quote Link to comment Share on other sites More sharing options...
Muddy_Funster Posted May 8, 2015 Share Posted May 8, 2015 what do you meen "Over Write"? Quote Link to comment Share on other sites More sharing options...
rcouser Posted May 8, 2015 Author Share Posted May 8, 2015 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) Quote Link to comment Share on other sites More sharing options...
Psycho Posted May 8, 2015 Share Posted May 8, 2015 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) ) ) Quote Link to comment Share on other sites More sharing options...
rcouser Posted May 8, 2015 Author Share Posted May 8, 2015 Brilliant Psycho, thats exactly what I need. Thanks very much everyone for your help. Quote Link to comment Share on other sites More sharing options...
Solution Psycho Posted May 8, 2015 Solution Share Posted May 8, 2015 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 ) ) Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.