[SOLVED] Calendar script selecting records

[bschultz]

I'm trying to select records from the database that fall between the start date and the end date AND also (if they are recurring events) fall on the correct day of the week or the date of the month.

 

Here's what I have:

 

$newday = $_GET['day'];

$sql = "SELECT * FROM dayplanner WHERE $newday BETWEEN startdate AND enddate AND '$newday = 'date('l')' OR '$newday = 'date('d')''";

 

The format of $_GET['day'] is the mysql date format (2009-05-07).  Obviously, my code won't work, because I haven't figured out what day of the week, or date of the month, the url is passing.

 

How can I determine from 2009-05-07 what day of the week and date of the month it is?

 

Thanks.

[xtopolis]

Uh, probably using a few of the Mysql functions found here:

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

 

If you have trouble, first be sure you can correctly select dates between startdate and enddate.  Then add your AND clause, and probably use parentheses to enforce the logic you are trying to achieve.

[bschultz]

Thanks for the help...the first part of the query works.  The only part that doesn't is the "recurring" part.

 

Here's the new code:

 

$sql = "SELECT * FROM dayplanner WHERE $newday BETWEEN startdate AND enddate AND (recurring = 'DAYOFMONTH('$newday')' OR recurring = 'DAYOFWEEK('$newday')')";
          
$rs = mysql_query($sql,$dbc);  
$matches = 0; 
while ($row = mysql_fetch_assoc($rs))  {
$matches++; 
echo "$row[event]<br />";
}  
if (! $matches) { 
echo "no matches here"; }  

 

It throws an error on this line...

 

while ($row = mysql_fetch_assoc($rs))  {

 

...so it's not pulling the correct date or day from the db.

 

Could this have something to do with the fact that using BETWEEN removes the dashes from the date field?  So instead of 2009-05-07, it's 20090507.

 

[bschultz]

To help in troubleshooting, I've added a field in the database.

 

I now have a field for dayofweek, and a field for dayofmonth (these are for the recurring events).

 

Unfortunately, the code still fails:

 

$sql = "SELECT * FROM dayplanner WHERE $newday BETWEEN startdate AND enddate AND (dayofmonth = 'DAYOFMONTH('$newday')' OR dayofweek = 'DAYOFWEEK('$newday')')";

[xtopolis]

if I had to guess, I'd say:

dayofmonth = 'DAYOFMONTH('$newday')'

 

does not need single quotes around DAYOFMONTH('$newday')

and the same for the other.  Did you try pasting that query directly into mysql?

[bschultz]

God, I hate quotes and brackets!

 

That did it.  Thanks.