[RESOLVED] Mysql date range problem

[webevelopersFTW]

i have this query and i want to look back 3 months in the database but that is not what i exactly want.

i want to be able to see a 5 day range back in the 3 months

for example today is 11-11-09 i want to be able to see all days from 8-07-9 through 8-11-09


SELECT table_student.s_id, table_lessons.lessons_date, table_student.s_last, table_student.s_first
FROM table_lessons, table_student
WHERE table_lessons.s_id = table_student.s_id
AND lessons_date = date_add(curdate(), interval -3 month);

[ssystems]

Are you calling this via any script? If so what's keeping you to just passing the dates?

[webevelopersFTW]

no this is just a simple record set

this code here get me the data i want, although a little bit more than i want. I figure i will just limit the results to 15 or so and that should be fine.

i was just wondering if i could exclude a value.

for example, if in a field there is only going to be either value 1, 2 or 3. I would like to exclude 3 from showing up in the results.


SELECT table_student.s_id, table_lessons.lessons_date, table_student.s_last, table_student.s_first
FROM table_lessons, table_student
WHERE table_lessons.s_id = table_student.s_id
AND lessons_date >= date_add(curdate(), interval -3 month)
order by table_lessons.lessons_date asc LIMIT 15

[svidgen]

i want to be able to see a 5 day range back in the 3 months
Something like ...
select [fields] from [tables] where [date-field] >= (now() - interval 3 month) - interval 5 day and [date-field] <= now() - interval 3 month;
Or ...
select [fields] from [tables] where [date-field] between (now() - interval 3 month) - interval 5 day and now() - interval 3 month;

[webevelopersFTW]

the bottom one worked perfectly! Thank you.

Is there anyway i can exclude a value.

as i said above

" if in a field there is only going to be either value 1, 2 or 3. I would like to exclude 3 from showing up in the results."

[svidgen]

In your where clause add something like ...
and [field-name] != 3

[webevelopersFTW]

Perfect! Thank You.

[webevelopersFTW]

ok it works great in myphpadmin but in dreamweaver i get a parse error on the last and statement




$query_Recordset1 = "SELECT table_student.s_id, table_student.s_homephone, table_lessons.lessons_desc, table_student.s_address, table_student.s_cellphone, table_student.s_city, table_lessons.lessons_date, table_student.s_last, table_student.s_first
FROM table_lessons, table_student
WHERE table_lessons.s_id = table_student.s_id
AND lessons_date between (now() - interval 3 month) - interval 5 day
AND now() - interval 3 month and lessons_desc !="Third"";

[webevelopersFTW]

never-mind single quotes fixed it