[PHP-DB] sql and week starting ending numbers (dates)
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
Re: [PHP-DB] sql and week starting ending numbers (dates)
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
