asked on

MySQL Query WHERE WEEK of

Instead of doing it by current date is there a way to do it by the week of? NOT week(CURRENT()) I want to do as the week of:

	<?php
	$bg = '#eeeeee'; 
		
	$sql->query("SELECT doctor_doctor.doctor_id, doctor_doctor.first_name AS first_name, doctor_doctor.last_name AS last_name, doctor_schedule.doctor_id, doctor_schedule.schedule_date AS date, doctor_schedule.time AS time, doctor_schedule.location_id
	FROM doctor_doctor, doctor_schedule
	WHERE THE WEEK OF 2013--01-01 AND doctor_doctor.doctor='1' AND doctor_doctor.doctor_id=doctor_schedule.doctor_id AND doctor_schedule.location_id='1' 
	ORDER BY date DESC");
				$row = $sql->fetch_all();
				//  var_dump($row);
				foreach($row as $r){
					$bg = ($bg=='#eeeeee' ? '#ffffff' : '#eeeeee');
					echo '<tr bgcolor="' . $bg . '">';
					echo '<td height="25" width="150">'.$r['first_name'].' '.$r['last_name'].'</td><td height="25" width="150"> '.$r['date'].'</td><td height="25" width="150"> '.$r['time'].'</td>';
					echo '</tr>';
					
				}
	
		
	?>

Open in new window

Ray Paseur

Why not use PHP to compute the starting and ending dates of the week? Express these as ISO-8601 values in the query string. This will lead you to a generalized solution that can accommodate a week, a group of 5 days, a month (more or less) etc. This article teaches how you might go about doing that.
https://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_201-Handling-date-and-time-in-PHP-and-MySQL.html

Argenti

Hi rgranlund,

You can create a stored function inside your database, able to compute if a given date belongs to the week of a reference day:

CREATE FUNCTION `date_is_in_week_of`(`test_date` date, `ref_date` date) RETURNS tinyint(1)
BEGIN
  RETURN test_date BETWEEN date_sub(ref_date, interval weekday(ref_date) day) 
     AND date_add(ref_date, interval 6-weekday(ref_date) day);
END

Open in new window

Then call your function from your SQL (here we assume testing if the date value of field my_date_field is in the current week, the week the current day belongs to)

SELECT Field1, Field2 
FROM my_table 
WHERE date_is_in_week_of(my_date_field, curdate()) = 1

Open in new window

or, if you don't want to use stored function, just use the where condition

SELECT Field1, Field2 
FROM my_table 
WHERE my_date_field BETWEEN date_sub(curdate(), interval weekday(curdate()) day) 
     AND date_add(curdate(), interval 6-weekday(curdate()) day)

Open in new window

ASKER CERTIFIED SOLUTION

PortletPaul

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Ray Paseur

Hope you take the time to read the article! Good luck with your project, ~Ray

PortletPaul

Ray's articles are always a good read :)

Thanks for the grading. Cheers, Paul