ckroon
Posts: 869
|
| Posted: 06/15/2008, 10:19 AM |
|
Ok. I need agreed to display upcoming events that are dated today ..> up to 4 weeks from today.
So I read the forums and came up with the following code.
SELECT * FROM (onlineclasses_master
LEFT JOIN course_main ON onlineclasses_master.lec_course_id = course_main.courses_main_courseid)
LEFT JOIN users ON onlineclasses_master.lec_teacher_id = users.usersid
WHERE course_main.courses_main_courseid = {s_course}
AND onlineclasses_master.lec_is_active <> '{Expr0}'
AND lec_date > date_sub(current_date(), INTERVAL 4 WEEK)
ORDER BY lec_time_start DESC
No errors.
But it shows me all the dates in the table. Ones from yesterday and ones from 6 weeks from now.
Can someone spot my error?
_________________
Walter Kempees...you are dearly missed. |
 |
 |
wkempees
Posts: 1679
|
| Posted: 06/16/2008, 9:16 AM |
|
MySQL version >= 5.0 ?
Quote :
SELECT * FROM (onlineclasses_master
LEFT JOIN course_main ON onlineclasses_master.lec_course_id = course_main.courses_main_courseid)
LEFT JOIN users ON onlineclasses_master.lec_teacher_id = users.usersid
WHERE
course_main.courses_main_courseid = {s_course}
AND onlineclasses_master.lec_is_active <> '{Expr0}'
AND lec_date > date_add(currdate(), INTERVAL 4 WEEKS)
ORDER BY lec_time_start DESC
minor change in above code.
You could alwas try the same code but only with
WHERE lec_date > date_add(currdate(), INTERVAL 4 WEEKS)
ORDER BY lec_time_start DESC
just to see what result it gives.
changed
date_sub into date_add
current_date() into curdate()
WEEK into WEEKS
lec_date is a datefield and not datetime is it?
Walter
(greetz)
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
|
|
|
mentecky
Posts: 321
|
| Posted: 06/16/2008, 10:32 AM |
|
Quote ckroon:
Ok. I need agreed to display upcoming events that are dated today ..> up to 4 weeks from today.
So I read the forums and came up with the following code.
SELECT * FROM (onlineclasses_master
LEFT JOIN course_main ON onlineclasses_master.lec_course_id = course_main.courses_main_courseid)
LEFT JOIN users ON onlineclasses_master.lec_teacher_id = users.usersid
WHERE course_main.courses_main_courseid = {s_course}
AND onlineclasses_master.lec_is_active <> '{Expr0}'
AND lec_date > date_sub(current_date(), INTERVAL 4 WEEK)
ORDER BY lec_time_start DESC
No errors.
But it shows me all the dates in the table. Ones from yesterday and ones from 6 weeks from now.
Can someone spot my error?
Try this:
SELECT * FROM (onlineclasses_master
LEFT JOIN course_main ON onlineclasses_master.lec_course_id = course_main.courses_main_courseid)
LEFT JOIN users ON onlineclasses_master.lec_teacher_id = users.usersid
WHERE course_main.courses_main_courseid = {s_course}
AND onlineclasses_master.lec_is_active <> '{Expr0}'
AND DATE(lec_date) >= current_date()
AND DATE(lec_date) <= date_add(current_date(), INTERVAL 4 WEEK)
ORDER BY lec_date, lec_time_start DESC
Rick
_________________
http://www.ccselite.com |
|
|
|
wkempees
Posts: 1679
|
| Posted: 06/16/2008, 4:33 PM |
|
OK I misread the post.
Cheers to Rick.
SELECT *
FROM (onlineclasses_master
LEFT JOIN course_main ON onlineclasses_master.lec_course_id = course_main.courses_main_courseid)
LEFT JOIN users ON onlineclasses_master.lec_teacher_id = users.usersid
WHERE course_main.courses_main_courseid = {s_course}
AND onlineclasses_master.lec_is_active <> '{Expr0}'
AND ( DATE(lec_date) >= currdate()
AND DATE(lec_date) <= date_add(currdate(), INTERVAL 4 WEEKS) )
ORDER BY lec_date, lec_time_start DESC
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
|
|
|
ckroon
Posts: 869
|
| Posted: 06/16/2008, 4:42 PM |
|
Cheers to you both. Ricks worked.. cookies for Rick!
Thanks!
_________________
Walter Kempees...you are dearly missed. |
|
|
|
wkempees
Posts: 1679
|
| Posted: 06/16/2008, 8:03 PM |
|
Cookies to Rick.
My last post merely shows the use of the default (non-synonym) operands/functions.
Plus it puts brackets around the date comparison.
My first post however was a misread assuming all dates after 4 weeks ahead.
Walter
_________________
Origin: NL, T:GMT+1 (Forumtime +9)
CCS3/4.01.006 PhP, MySQL .Net/InMotion(Vista/XP, XAMPP)
if you liked this info PAYPAL me: http://donate.consultair.eu
|
|
|
|
mentecky
Posts: 321
|
| Posted: 06/17/2008, 12:36 PM |
|
Quote ckroon:
Cheers to you both. Ricks worked.. cookies for Rick!
Thanks!
Glad I could help!
Rick
_________________
http://www.ccselite.com |
|
|
|
|
