Selecting data within certain dates problem

I am trying to make a report generation page that will display data between certain dates. I first need to select all data that is in the current month. I want to be able to select all data in any given month by the user entering in the month.

The second thing I need is to be able to select the data on a weekly basis...like week 1 2006 = bla bla, week 2 2006 = bla bla, week 3 2006 = bla bla, etc. I would like to be able to have it go through week 52 of the year, but be able to show like Month = June, but Week 1 of June 2006 = bla bla, Week 2 of June 2006 = bla bla, etc.

The third thing I need is to be able to generate Y-T-D reports for the current year only. I have used the links below as help, but I get errors. I will post my errors & code below. I can't even get this to work to get the data from the last 7 days.

http://codingforums.com/showthread.php?t=80896

My Error: Parse error: syntax error, unexpected T_STRING in C:\XAMPP\xampp\htdocs\My Sites\Members - MGE\cp\reports\monthly.php on line 18 $sql2 = "SELECT SUM(profit) AS sum_profit2
FROM $order_table
WHERE order_date <= Date_Format(Now(), "%Y-%m-%d") //This is line 18
AND order_date >= Date_Format(Sub_Date(Now(), INTERVAL 7 DAY), "%Y-%m-%d")";
$result2 = mysql_query($sql2) or die(mysql_error());
$i2 = mysql_fetch_array($result2);

echo "<br><hr>";
echo $i2['sum_profit2'];


Thanks in advance for your help!

change to '%Y-%m-%d' on line 18 and 19..

change to '%Y-%m-%d' on line 18 and 19..I did that I got this error 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 '), '%Y-%m-%d')' at line 4

I think it is talking about line 4 of my query since there is nothing on line 4 of my php page.

This is the entire page<?PHP
$sql = "SELECT SUM(profit) AS sum_profit FROM $order_table WHERE order_status = 'Filled' ";
$result = mysql_query($sql) or die(mysql_error());
$i = mysql_fetch_array($result);

echo "<br><strong>";
echo $i['sum_profit'];
echo "</strong><hr>";

$sql2 = "SELECT SUM(profit) AS sum_profit2
FROM $order_table
WHERE order_date <= Date_Format(Now(), '%Y-%m-%d')
AND order_date >= Date_Format(Sub_Date(Now(), INTERVAL 7 DAY), '%Y-%m-%d')";
$result2 = mysql_query($sql2) or die(mysql_error());
$i2 = mysql_fetch_array($result2);

echo "<br><hr>";
echo $i2['sum_profit2'];
?>

I think you want to use the "Date_Sub" function rather than the "Sub_Date" function (which doesn't exist).

I am trying to make a report generation page that will display data between certain dates. I first need to select all data that is in the current month. I want to be able to select all data in any given month by the user entering in the month.
in the where clause you could do: DATE_FORMAT(your_date, '%m-%Y') = '12-2006'

The second thing I need is to be able to select the data on a weekly basis...like week 1 2006 = bla bla, week 2 2006 = bla bla, week 3 2006 = bla bla, etc. I would like to be able to have it go through week 52 of the year, but be able to show like Month = June, but Week 1 of June 2006 = bla bla, Week 2 of June 2006 = bla bla, etc.
in a group by you could do: DATE_FORMAT(your_date, '%U-%Y')

refer to http://dev.mysql.com/doc/refman/5.0/en/date-and-time-functions.html for details

I think you want to use the "Date_Sub" function rather than the "Sub_Date" function (which doesn't exist).
subdate() exists, and is an alias for date_sub and will require fewer keystrokes to change :)