Filtering results by DATE value in database

[martincrumlish]

Hi,

I have this code in my page for displaying some data from a database, sorted by date. i had posted a messed up version of this a while ago but I couldnt get it working even after a few suggestions. Therefore, i decided to post this code and see if one of you good people can help me 🙂

Heres the code:

<?

include ("dbconnect.php");

if($date == "Today")
{ 
//what should go here?
}
if($date == "yesterday")
{
//what should go here?
}
if($date == "last7")
{
//what should go here?
}
if($date == "last30")
{
//what should go here?
}
if($date == "all")
{
	$sql = "Select * from special where eventCategory = '$eventcat'";
	$result = mysql_query($sql);
	while($row = mysql_fetch_array($result)){

echo $row[description]."<p>";
}
}

else{
?>

  <form name="Search_Special_Form" method="post" action="<? $PHP_SELF ?>">
      <table align="center">
	  <tr> 
        <td width="146" class=specialsGreenTextBold>&nbsp;Date: 
          <input type="hidden" name="eventcat" value="Soccer">
        </td>
        <td width="233"> 
          <select name="date">
            <option value="today" selected>Today</option>
            <option value="yesterday">Yesterday</option>
            <option value="last7">Last 7 days</option>
            <option value="last30">Last 30 days</option>
            <option value="all">All specials</option>
          </select>
          <br>
        </td>
        <td width="171"> 
          <input type="submit" value="submit" name="submit">
        </td>
      </tr>
    </form>

<?
}

The SQL queries are querying a table that has a column called startDate of type DATE. I have left the query for Today, yesterday, last 7 days and last 30 days blank as they are the ones i dont knw. I looked at mysql.com but I still couldnt get it right.

Thanks in advance,
//Martin

[floR]

From the MySQL reference (6.3.4):

SELECT something FROM tbl_name WHERE TO_DAYS(NOW()) - TO_DAYS(date_col) <= 30;

I believe this would help you out 🙂

[martincrumlish]

Thanks,

I've got it working ok now 🙂