display results from certain time periods

JD_

i want to have my results displayed by today,this month,3 months,6 months,12 months old

e.g the user selects from the drop down box which time period they want and the results are displayed accordingly

the default view is to view all so my select query will look like this

$results = mysql_query("SELECT * 
                        FROM bookings $agequery

and $agequery something like "WHERE bdate < 1 month old

ive looked at mktime but dont really understand how to use it , just need someone to point me in the right direction as to what i need.

thanks.

rachel2004

If you wish to do it in the SQL statement, use the MySQL function UNIX_TIMESTAMP to convert a date into a, well, Unix timestamp. Once you have both dates in timestamp format, you can subtract one from the other and do either ">" or "<" less than comparisons to determine if the values fall within an upper and lower bound.

Since you won't want to feed Unix timestamps to your audience, use the MySQL function DATE_FORMAT() in your SELECT queries to format your date/datetime/timestamp columns.

Complete sytax on either function can be found at the MySQL manual.

JD_

ok i got what your saying but im having trouble with getting a timestamp to compare it with like below the $month isnt in unix timestamp format

   // format a date for the begining of this month. 
        $month = date('Y-m-d', mktime(0,0,0,$month,1,$year)); 

$numresults = mysql_query("SELECT * FROM bookings WHERE  (bdate > '$month')"); // the query.

rachel2004

<?php 
// format a date for the begining of this month.
$month = mktime(0,0,0,$month,1,$year);
$sql = "SELECT unix_timestamp(bday) AS birthday 
        FROM bookings 
        WHERE (unix_timestamp(bday) > '$month')"; // the query.
$numresults = mysql_query($sql)
	or die("<p>Couldn't execute query: <strong>"
	       .mysql_error()."</strong></p>");
?>

JD_

sorry to be a pain, makes sense to me apart from the AS birthday?

Couldn't execute query: You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '(bdate) AS birthday FROM bookings WHERE (unix

edit: ok fixed it misspelt "timestamp" , still dont understand the AS birthday but, thanks for your help 🙂

rachel2004

I had a typo, but I fixed it now. Try it again.

As for your question: where it says "AS birthday" is what I've given as a column alias. This makes it easier for you to work with it:

while ($row = mysql_fetch_array($numresults)) 
{
    $bday = $row['birthday'];
}

instead of doing:

while ($row = mysql_fetch_array($numresults)) 
{
    $bday = $row['unix_timestamp(bday)'];
}

JD_

thankyou