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

LVL 7
rgranlundAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ray PaseurCommented:
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.
http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/A_201-Handling-date-and-time-in-PHP-and-MySQL.html
0
ArgentiCommented:
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

0
PortletPaulfreelancerCommented:
I would caution anyone to avoid using 'between ... and' for date range selections. The reliability of this is dependent on the underlying data type precision (date, datetime, timestamp) and unless this is correctly accommodated 'between ... and' can lead to gaps or overlaps.

I attempt to explain this in an article "Beware of Between"

A robust and effective alternative to 'between ... and' is the use of >= with < with the later date being "the next day". I suggest this:

$requestDate = '2013-10-29';
           
$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 INNER JOIN doctor_schedule ON doctor_doctor.doctor_id = doctor_schedule.doctor_id WHERE doctor_doctor.doctor = '1' AND doctor_schedule.location_id = '1' AND ( doctor_schedule.schedule_date >= date_sub('$requestDate', interval weekday('$requestDate') DAY) AND doctor_schedule.schedule_date < date_add('$requestDate', interval 7 - weekday('$requestDate') DAY) )");

Comments about this (formatted) 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
INNER JOIN doctor_schedule
        ON doctor_doctor.doctor_id = doctor_schedule.doctor_id
WHERE doctor_doctor.doctor = '1'
AND doctor_schedule.location_id = '1'       
AND (
         doctor_schedule.schedule_date >= date_sub('$requestDate', interval weekday('$requestDate') DAY)
    AND doctor_schedule.schedule_date < date_add('$requestDate', interval 7 - weekday('$requestDate') DAY)
    )
;

Open in new window

both date and time are "reserved words" and should really be backticked, ideally you wouldn't use `time` as a field name or either as an alias. Using ANSI join syntax (e.g. the inner join) is most definitely preferred to "old fashioned" joining through the where clause.

nb: use of YEARWEEK may achieve the desired results; but that approach is applying a function to every row of data and it adversely affects the efficient use of indexing in the query
see: http://en.wikipedia.org/wiki/Sargable
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Ray PaseurCommented:
Hope you take the time to read the article!  Good luck with your project, ~Ray
0
PortletPaulfreelancerCommented:
Ray's articles are always a good read :)

Thanks for the grading. Cheers, Paul
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.