Grabbing Item from Database Where Date is Within X Days Before Deadline

[CloudSex13]

Hi, thanks for reading.

 

I'm inquirying to see if there's a certain SQL function (I haven't heard of any yet - self taught) where you can select an item out of a database where a date field (i.e. 2009-04-09) is within one week before the deadline.

 

So, because today is 2009-04-06, that item would be outputted as an item almost due because that item's deadline is 2009-04-09.

 

If the deadline was 2010-05-14, then it would not be identified, because the deadline is more than an X amount of days defined (i.e. over a week.)

 

Would anyone know if this is possible?

 

Thank you, if so.

[charleshill]

You need to have a column in the table that contains the timestamp (not a formatted date)

 

 

I use this code to find the upper and lower limit timestamps of a specific date given in the URL's query string:

 

	// start with false...
$date_valid = false;

$date = explode('_', $_GET['date']);

// reindex and clean the values...
$date = array(
	'day' => isset($date[2]) ? (int) $date[2] : 0,
	'month' => isset($date[1]) ? (int) $date[1] : 0,
	'year' => isset($date[0]) ? (int) $date[0] : 0,
);

$first_month = $date['month'];
$last_month = $date['month'];
$first_day = $date['day'];
$last_day = $date['day'];

// numeric values for first and last months...	
if (empty($date['month']))
{
	$first_month = 1;
	$last_month = 12;
	$date['day'] = 0;
	$first_day = 1;
	$last_day = 31;
}
// numeric values for the first and last days...
elseif (empty($date['day']))
{
	$first_day = 1;
	// months with 31 days...
	if (in_array($date['month'], array(1,3,5,7,8,10,12)))
		$last_day = 31;
	// months with 30 days...
	elseif (in_array($date['month'], array(4,6,9,11)))
		$last_day = 30;
	// 29 days o rly?
	elseif (($date['year'] % 4) == 0)
		$last_day = 29;
	// month with 28 days...
	else
		$last_day = 28;
}

// check the date
$date_valid = checkdate($last_month, $last_day, $date['year']) && checkdate($first_month, $first_day, $date['year']);

// timestamp for last second of given date...
$date['last_timestamp'] = mktime(23, 59, 59, $last_month, $last_day, $date['year']);

// timestamp for first second of given date...
$date['start_timestamp'] = mktime(0, 0, 0, $first_month, $first_day, $date['year']);

 

note: the GET variable date should look like this in the URL .....  date=YEAR_MONTH_DAY  ... ie date=2009_4_6 for today

[CloudSex13]

Thanks for the reply.

 

But, what if I wanted to use only the date, and not the time?

 

I could make a variable as today's date in the format of 2009-04-06, and match it to the deadline date in the database of 2009-04-09, but, with the SQL code, it would be like, WHERE $Today [x amount of days of] Deadline, or something, but I don't think that would work.

 

Any other thoughts? :/

 

 

[CloudSex13]

So there's no simple way to do it in SQL, and you cannot just use dates?

[CloudSex13]

Anyone? :/

[redarrow]

http://dev.mysql.com/doc/refman/5.1/en/date-and-time-functions.html

[CloudSex13]

Yeah man, I just went there.

 

SELECT * FROM Table WHERE DATE_SUB(CURDATE(),INTERVAL 30 DAY) <= date_col

 

Could this work?

[cunoodle2]

That or you could look into the Mysql "IN" function depending on how your dates are formatted....

 

The IN function helps reduce the need to use multiple OR conditions.

 

The syntax for the IN function is:

 

    SELECT columns

    FROM tables

    WHERE column1 in (value1, value2, .... value_n);

 

 

So basically what you want to do in php is to build a basic string seperated by commas that contains the current date and then the previous 7 days that looks like this...

 

2009-04-03, 2009-04-04, 2009-04-05, 2009-04-06, 2009-04-07, 2009-04-08, 2009-04-09

 

Then do the following sql..

 

SELECT * from table WHERE dates IN (2009-04-03, 2009-04-04, 2009-04-05, 2009-04-06, 2009-04-07, 2009-04-08, 2009-04-09);

 

Make sense?

[CloudSex13]

Thanks cunoodle2 for the help.

 

Well, yes, that does make sense pretty much.

 

The only question I'd have is, how can I make a string finding the dates between today and a week from now? I obviously know how to get today's date and next week's date in two different variables, but how can I find the in-between?

 

That seems to be my problem with everything.

[cunoodle2]

Post the code that you have that shows todays date and next weeks date and I'll modify it.  I'm not going to write it from the ground up.

 

Basically what you need is to take the beginning date and keep adding 1 date until you get to the date that is at the end of your desired search string.

 

Something like this...

<?php

$start_date = "2009-04-03";
$end_date = "2009-04-10";
$in_statement = "'$start_date'";

for ($i=$start_date; $i < $end_date; $i + 1_day)
{
	$in_statement .= ", '$start_date'";
}

?>

 

The above code will NOT  work properly.  Its just a rough idea on how you should do it.  You will most likely have to use a "while" statement or something and do whatever processing is needed to change "2009-04-03" to "2009-04-04", then add it to the string, then change it to "2009-04-05" and keep doing it until your string is built.

 

Then do the SQL query.

[CloudSex13]

That makes sense.

 

I appreciate the big level of help you've given and offered me.

 

I just found out about the MySQL "between" function right now. Would I be able to save all this time and just go ahead and do something like...

 

$today = "2009-04-07";
$weeklater = "2009-04-14";

$query = mysql_query("SELECT Deadline FROM Things WHERE Deadline BETWEEN '$today' AND '$weeklater'");

 

If that does work, could the rows be outputted using a while statement?