View Full Version : checking for a week range

08-22-2006, 05:22 PM
Hi everyone,

I have a query to check the data for a week starting from the date entered by the user, but it's not working. I would be very grateful if you can have a look.

function GetDateSelectString($IsPosted = true)
return $_POST['sd_year'] . '-' . $_POST['sd_month'] . '-' . $_POST['sd_day'];

$PostedDate = GetDateSelectString();

$query1 = ("SELECT m_room_details.room_code, m_room_details.room_number, room_name, m_session.session_id, session_date, session_start_time, session_end_time, room_cost_per_session, booking_indicator FROM m_room, m_room_details, m_room_session, m_session WHERE m_room_details.room_code = m_room_session.room_code AND m_room_details.room_number = m_room_session.room_number AND m_room.room_code = m_room_details.room_code AND booking_indicator = 'YES' AND m_room_session.session_id = m_session.session_id AND session_date BETWEEN ('".$PostedDate."' AND ('".$PostedDate."' + INTERVAL 7 DAY )) ");

$result1 = mysql_query($query1) or die('Query failed miserably: ' . mysql_error());
$numfields = mysql_num_fields($result1);

the query works fine if i remove the condition
AND session_date BETWEEN ('".$PostedDate."' AND ('".$PostedDate."' + INTERVAL 7 DAY )) :

08-22-2006, 05:25 PM
The "INTERVAL 7 DAY" needs to be part of the date_add() function. You can also use "1 WEEK".

AND session_date BETWEEN ('".$PostedDate."' AND date_add('".$PostedDate."', INTERVAL 1 WEEK ))

08-22-2006, 05:47 PM
Thanks but it still doesn't work. the query works only when I remove the date part. By the way I am echoing the $PostedDate and it's showing the right selected date in the right format

08-22-2006, 05:57 PM
"It doesn't work" isn't helpful. "I got a sql syntax error and here it is" or "No rows are returned" is helpful.

The code I posted needed a tweak, the parenthesis were askew.

AND (session_date BETWEEN '".$PostedDate."' AND date_add('".$PostedDate."', INTERVAL 1 WEEK ))

I just ran this on a table of mine and it works fine.

08-22-2006, 06:00 PM
that works thanks :thumbsup:

sorry i didn't post the error message, it was just a normal sql error "you have an error in your syntax....."

Many thanks.