Extract events that occur between two dates from a MYSQL Database Using PHP

meHere

Can anyone tell me how I extract events from a database that occur between two certain dates, The problem is that I have stored the date of the events all seperate as startday, startmonth and startyear all integers?
I can get it to work between two dates in the one month but when one of the dates is in another month it won't work.

I would be very grateful if anyone can help me

The_Chancer

Get the individual dates, timestamp them then use the timestamp to get the details.

substr(date("M J Y",strtotime($day $month $year))0,2);

Might help you in the right direction...

Can't remember about the order in the strtotime though, if it takes DDMMYY or MMDDYY....

cahva

You can easily extract events from mysql without using PHP.

example:

SELECT  * 
FROM  `table` 
WHERE 
TO_DAYS(concat( startyear,'-',startmonth,'-', startday ))  > TO_DAYS('2004-01-01') 
AND
TO_DAYS(concat( startyear,'-',startmonth,'-', startday ))  < TO_DAYS('2004-02-01')

That would extract this months events..

meHere

Thanks for the help

But I have to Write this as a PHP program cahva should your suggestion still work in PHP?

cahva

Yes offcourse! You put that to your mysql query:

$from='2004-01-01';
$to='2004-02-01';
$query="SELECT  * 
FROM  `table` 
WHERE 
TO_DAYS(concat( startyear,'-',startmonth,'-', startday ))  > TO_DAYS('".$from."') 
AND
TO_DAYS(concat( startyear,'-',startmonth,'-', startday ))  < TO_DAYS('".$to."')";
$result=mysql_query($query);

// print results(this prints the date fields)
while($row=mysql_fetch_array($result)) {
echo $row['startyear'].'-'.$row['startmonth'].'-'.$row['startday'].'<br>';
}

meHere

Sorry to keep bothering you but It won't work cahva,

for my start and end dates they are in the format 20040220 without the '-' that you have should it still work if i simply take them out and just leave the ',' in?

And in here TO_DAYS('".$from."') what are the '.' for?

Thanks

cahva

Yes they will work without - characters.

I myself usually want to use those quotes around variables(to see them more clearly) but you dont have to if you dont want to.

TO_DAYS('$from') is just as good as the other way.

The_Chancer

I find using the ".$foo." construction easier - especially when you start usign ".$_GET['foo']." etc within the query..

meHere

I'm sorry but I don't know what you mean by the ".$foo." construction ??

My knowledge of PHP and MYSQL is very basic I have only been using it a few weeks.

After using the TO_DAYS method I keep getting the following error message

Warning: mysql_fetch_array(): supplied argument is not a valid MySQL result resource

and the line which it points me to is the while statement which gets data from the array. Do you have any idea what the problem is Thanks.