SELECT
FROM_DAYS(TO_DAYS(purchaseDate)-DAYOFYEAR(purchaseDate)+WEEK(purchaseDate)*7)
will hopefully return the date you need. You'll have to enclose all that
in a DATE_FORMAT to get a printable date tho. Last minute note: you
might have to subtract 1 from the last term because I guess MySQL starts
counting with week 1.
HTH
Bogdan
Adam Royle wrote:
> Hello all,
>
> I am creating a finance manager in php and mysql for my own accounting
> purposes (and to broaden my skills in php and sql) and I have a couple
> of pages where I view statistics on how much I spend, etc, and what
> patterns are formed from my spending.
>
> I have made a couple of pages with different statistics, and on one
> specific page, I show how much I spend total each week... The
> following query does that, but I want to display the start date and
> end date of the week.
>
> $sql = "SELECT YEARWEEK(purchaseDate,1) AS week,
> CONCAT_WS(' ',MONTHNAME(purchaseDate),
> YEAR(purchaseDate),'- Wk', WEEK(purchaseDate,1)) AS month,
> SUM(itemPrice) AS total
> FROM tblCashflow
> WHERE userID_FK = ${user['userID']}
> AND Type = 'Expenditure'
> GROUP BY week";
>
> OK, so the sql works, and the output on the page looks like something
> below...
>
> February 2002 - Wk 5 $49.70
> February 2002 - Wk 7 $12.95
> February 2002 - Wk 8 $53.29
> February 2002 - Wk 9 $275.99
> March 2002 - Wk 10 $78.95
> March 2002 - Wk 11 $171.15
> March 2002 - Wk 12 $82.85
> March 2002 - Wk 13 $20.50
> April 2002 - Wk 14 $55.15
> April 2002 - Wk 15 $30.55
> April 2002 - Wk 16 $104.20
> April 2002 - Wk 17 $461.95
> May 2002 - Wk 18 $152.75
> May 2002 - Wk 19 $219.80
> May 2002 - Wk 20 $133.55
> May 2002 - Wk 21 $98.65
>
> Now thats all well and good, but I want to make it display something
> like the following:
>
> February 1 - 7 2002 $49.70
> February 8 - 15 2002 $12.95
> February 16 - 23 2002 $53.29
>
> etc etc etc
>
> Does anyone have any idea on how this could be achieved? I looked at
> the mySQL time and Date functions, but I couldn't find out how to do
> it with that, and I think doing the work in PHP might be the
> answer.... Also, currently the SQL statement only shows the weeks
> where I have spent money, so if I don't spend anything in that week,
> it won't show up at all, although I would like it to say "January 12 -
> 19 2002 $0" or something like that.
>
> Below is my table schema for the above query
>
> Adam
>
> CREATE TABLE tblCashflow (
> ID bigint(20) NOT NULL auto_increment,
> purchaseDate date default NULL,
> categoryID_FK tinyint(4) default NULL,
> itemName text,
> itemPrice float default '0',
> isEssential tinyint(4) default '0',
> paymentTypeID_FK tinyint(4) default '1',
> receiverID_FK tinyint(4) default NULL,
> itemDesc text,
> Type text,
> userID_FK int(11) default NULL,
> PRIMARY KEY (ID)
> );
>
>
--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php
