Avatar of Robert Granlund
Robert GranlundFlag for United States of America 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

PHPMySQL ServerSQL

Avatar of undefined
Last Comment
PortletPaul

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.
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

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
or
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.
rwheeler23
PortletPaul

Ray's articles are always a good read :)

Thanks for the grading. Cheers, Paul