Avatar of Robert Granlund
Robert GranlundFlag for United States of America asked on


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:

	$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


Avatar of undefined
Last Comment

8/22/2022 - Mon
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.

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)
  RETURN test_date BETWEEN date_sub(ref_date, interval weekday(ref_date) day) 
     AND date_add(ref_date, interval 6-weekday(ref_date) day);

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


Log in or sign up to see answer
Become an EE member today7-DAY FREE TRIAL
Members can start a 7-Day Free trial then enjoy unlimited access to the platform
Sign up - Free for 7 days
Learn why we charge membership fees
We get it - no one likes a content blocker. Take one extra minute and find out why we block content.
See how we're fighting big data
Not exactly the question you had in mind?
Sign up for an EE membership and get your own personalized solution. With an EE membership, you can ask unlimited troubleshooting, research, or opinion questions.
ask a question
Ray Paseur

Hope you take the time to read the article!  Good luck with your project, ~Ray
This is the best money I have ever spent. I cannot not tell you how many times these folks have saved my bacon. I learn so much from the contributors.

Ray's articles are always a good read :)

Thanks for the grading. Cheers, Paul