Query last saturday to this friday

[leszer]

Hi,

First, thanks to all of the freaks on this forum who've helped me off and on during my coding efforts.  I tend to do well until I over-think my problem.

Now, what I'm trying to do right now is query a table for the current week saturday to Friday, no matter the day.

The code I've come up with doesn't give me any errors but..  also doesn't work so I'm sure I've not thought something through.

[code

<?php
$username="root";
$password="";
$database="flash";
$lsat=strtotime( "last saturday" );



if (date(strtotime( "friday" )))
$fri=date(strtotime( "friday" ));
else
$fri=date(strtotime( "next friday"));

mysql_connect("localhost",$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT * FROM events WHERE Date>=$lsat and Date<=$fri";
$result=mysql_query($query);

$num=mysql_num_rows($result);

mysql_close();
?>

[kickstart]

Hi

 

Firstly, avoid using a column name like "Date". Anything that is or may be a reserved word in the future is just a pain.

 

The MySQL week function only supports changing the start of the week between Sunday and Monday I think, which is a bit of a pain.

 

Using SQL to determine the week (and changing the column name to aDate)

 

<?php
mysql_connect("localhost",$username,$password);
@mysql_select_db($database) or die( "Unable to select database");
$query="SELECT *
FROM events
INNER JOIN (
SELECT DATE(DATE_ADD(NOW(), INTERVAL(CASE DAYOFWEEK(NOW()) WHEN 1 THEN -1 WHEN 2 THEN -2 WHEN 3 THEN -3 WHEN 4 THEN -4 WHEN 5 THEN -5 WHEN 6 THEN -6 ELSE 0 END) DAY)) AS PrevSaturday,
DATE(DATE_ADD(NOW(), INTERVAL(CASE DAYOFWEEK(NOW()) WHEN 1 THEN 5 WHEN 2 THEN 4 WHEN 3 THEN 3 WHEN 4 THEN 2 WHEN 5 THEN 1 WHEN 6 THEN 0 ELSE 6 END) DAY)) AS NextFriday) AS DateRange
WHERE events.aDate BETWEEN DateRange.PrevSaturday AND DateRange.NextFriday";
$result=mysql_query($query);

$num=mysql_num_rows($result);

mysql_close();
?>

 

All the best

 

Keith

[leszer]

Fails on:

 

mysql_num_rows() expects parameter 1 to be resource

[kickstart]

Hi

 

Try the SQL directly against the db.

 

And have you changed your column name and the SQL to match with the name of that date column?

 

All the best

 

Keith