DaniWeb Forum Index > Web Development > PHP

date range php/mysql

Please support our PHP advertiser: PostgreSQL or MySQL? Compare and contrast the two most popular open source databases

•

Join Date: Jan 2008

Posts: 1

Reputation: thecraigmcrae is an unknown quantity at this point

Solved Threads: 0

thecraigmcrae thecraigmcrae is offline

Offline

Newbie Poster

date range php/mysql

Jan 19th, 2008

I'd been trying to do date range comparisons all day, and found that using sql was the only way to find out if a date (inside a table) was between a range (assigned by me)
I have 4 of the statements below, one for each season.

 Help with Code Tags 
 PHP Syntax (Toggle Plain Text) 
$strSQL = 'SELECT * '
        . ' FROM `calendar` '
        . ' WHERE `eventdate` '
        . ' BETWEEN "2008-04-30" AND "2008-06-23"'
        . ' ORDER BY `eventdate` ASC';
$strSQL = 'SELECT * '
        . ' FROM `calendar` '
        . ' WHERE `eventdate` '
        . ' BETWEEN "2008-04-30" AND "2008-06-23"'
        . ' ORDER BY `eventdate` ASC';

The problem i'm having now, is that i need to assign a season to each date range to use in my document as a variable ($quarter). What i figured i'd need to do was see if the current date was within that range, so i'd need to get the system date. Not sure how to achieve this in sql and pass the variable through to use in the document.

 Help with Code Tags 
 PHP Syntax (Toggle Plain Text) 
<a href="" onclick="return showPanel(this, 'fall');" <?php if($quarter == "fall"){ echo("class='active'"); }?> >FALL</a>
<a href="" onclick="return showPanel(this, 'fall');" <?php if($quarter == "fall"){ echo("class='active'"); }?> >FALL</a>

Any help would be appreciated, i'm tearing my hair out over this.

Last edited by thecraigmcrae; Jan 19th, 2008 at 8:55 pm.

•

Join Date: Jan 2008

Posts: 57

Reputation: Walkere is an unknown quantity at this point

Solved Threads: 5

Walkere

Offline

Junior Poster in Training

Re: date range php/mysql

Jan 19th, 2008

•

Originally Posted by thecraigmcrae

I'd been trying to do date range comparisons all day, and found that using sql was the only way to find out if a date (inside a table) was between a range (assigned by me)

You should be able to do this with some php.

You can use the strotime() function to turn the date from the table into a timestamp. Then use strotime to turn your start and end dates into a timestamp. Then do a simple if statement comparison - if the start date is less than the table date and the table date is less than the end date... you're good.

You could create an array, with four elements. Each one of those elements would be its own array - with a start date, end date, and quarter name.

Then loop through the entire array, do the if comparison, and when you have a match save the quarter name.

Here's a working script to get you started. You'd just need to punch in your data from the db table and add the correct date ranges in.

 Help with Code Tags 
 PHP Syntax (Toggle Plain Text) 
<?php
$test_date = strtotime("21 June 2008");
 
$quarters = array();
 
$season['start'] = strtotime("1 January 2008");
$season['end'] = strtotime("1 April 2008");
$season['name'] = 'First';
 
$quarters[] = $season;
 
$season['start'] = strtotime("1 April 2008");
$season['end'] = strtotime("1 July 2008");
$season['name'] = 'Second';
 
$quarters[] = $season;
 
$active_quarter = '';
foreach ($quarters as $one)
{
	if (($one['start'] < $test_date) && ($test_date < $one['end']))
		$active_quarter = $one['name'];
}
 
if ($active_quarter != '')
	echo "Match: $active_quarter";
else
	echo "No match!";
?>
<?php
$test_date = strtotime("21 June 2008");

$quarters = array();

$season['start'] = strtotime("1 January 2008");
$season['end'] = strtotime("1 April 2008");
$season['name'] = 'First';

$quarters[] = $season;

$season['start'] = strtotime("1 April 2008");
$season['end'] = strtotime("1 July 2008");
$season['name'] = 'Second';

$quarters[] = $season;

$active_quarter = '';
foreach ($quarters as $one)
{
	if (($one['start'] < $test_date) && ($test_date < $one['end']))
		$active_quarter = $one['name'];
}

if ($active_quarter != '')
	echo "Match: $active_quarter";
else
	echo "No match!";
?>