PHP MySQL Hard Question Please Help

Hi Experts,

This is completely out of my scope. so im looking for some help, i dont think this is possible but you might think otherwise.

I have got a table with Months in it, Jan to December, a company is given a "kit fund level" between jan and decemeber however this may be renewed in the middle of the month in July for example. Every company will be different when it is renewed.

Is their anything i can use in the company table to define when the kit fund was issued and then someone use PHP to determin the total they spent AFTER the month it was issued.

So for example if it was issed in July. I would only need the total from july to december for that year rather than jan to dec.

Like i said this will change for every company so im guessing some sort of "flag" will be needed.

All advice and comments are appreciated
LVL 6
NeoAshuraAsked:
Who is Participating?
 
OnALearningCurveCommented:
OK,

I think I should have ended the CASE statement with END CASE rather than just END (SQL verses MYSQL) and I think the month filed names used in the case statement might need to be encapsulated by quotes.

Try the version of the SQL statement below
SELECT customer_name, SUM(Jan09) AS 'Jan', SUM(Feb09) AS 'FEB' , SUM(Mar09) AS 'Mar',  SUM(Apr09) AS 'Apr',  SUM(May09) AS 'May',  SUM(Jun09) AS 'Jun',  SUM(Jul09) AS 'Jul',  SUM(Aug09) AS 'Aug',  SUM(Sep09) AS 'Sep',  SUM(Aug09) AS 'Aug',  SUM(Sep09) AS 'Sep',  SUM(Oct09) AS 'Oct',  SUM(Nov09) AS 'Nov',  SUM(Dec09) AS 'Dec'), SUM(CASE renweal_month WHEN 1 THEN (`Jan` + `Feb` + `Mar` + `Apr` + `May` + `Jun` + `Jul` + `Aug` + `Sep` + `Oct` + `Nov` + `Dec`) WHEN 2 THEN (`Feb` + `Mar` + `Apr` + `May` + `Jun` + `Jul` + `Aug` + `Sep` + `Oct` + `Nov` + `Dec`) WHEN 3 THEN (`Mar` + `Apr` + `May` + `Jun` + `Jul` + `Aug` + `Sep` + `Oct` + `Nov` + `Dec`) WHEN 4 THEN (`Apr` + `May` + `Jun` + `Jul` + `Aug` + `Sep` + `Oct` + `Nov` + `Dec`) WHEN 5 THEN (`May` + `Jun` + `Jul` + `Aug` + `Sep` + `Oct` + `Nov` + `Dec`) WHEN 6 THEN (`Jun` + `Jul` + `Aug` + `Sep` + `Oct` + `Nov` + `Dec`) WHEN 7 THEN (`Jul` + `Aug` + `Sep` + `Oct` + `Nov` + `Dec`) WHEN 8 THEN (`Aug` + `Sep` + `Oct` + `Nov` + `Dec`) WHEN 9 THEN (`Sep` + `Oct` + `Nov` + `Dec`) WHEN 10 THEN (`Oct` + `Nov` + `Dec`) WHEN 11 THEN (`Nov` + `Dec`) WHEN 12 THEN (`Dec`) END CASE) AS totalSpend FROM customer_table GROUP BY company

Open in new window

0
 
OnALearningCurveCommented:
Hi NeoAshura,

I may be looking at this from a more simplistic point of view than it need but I would start with the following.

Have a column in your MySQL company table called renewal_month and simply store the integer value of the month (1 through 12)

Then in your Query you would use a case statement to sum up the spend from that month onward

something like

SELECT company,..... SUM(CASE WHEN (month(spend_date) <= renwal_month) THEN send_value ELSE 0 END) AS totalSpend, .... FROM .... WHERE ... GROUP BY company

OR if it needs to be based on a specific date have the renewal_date column and have a similar case statement as the one above but pulling values from dates greater than the renewal date.

I hope this makes sense,

Mark.
0
 
NeoAshuraAuthor Commented:
SELECT company,..... SUM(CASE WHEN (month(spend_date) <= renwal_month) THEN send_value ELSE 0 END) AS totalSpend, .... FROM .... WHERE ... GROUP BY company

Let me break this down....


"month(spend_date)" what is this bit? is this a coulmn in the database too sorry just a bit confused. I know renwal_month is the 1-12 also what is "THEN sned_value ELSE 0 END)"

Sorry for being a pain.

My table looks similar to this incase it helps.

customer_name         Jan      Feb      Mar     Apr    May     Jun   Jul    Etc.... renewal_month  Mobile
Mrs Anna               10.00      12.00   11.00    11.00                                              1              07...
0
Cloud Class® Course: CompTIA Healthcare IT Tech

This course will help prep you to earn the CompTIA Healthcare IT Technician certification showing that you have the knowledge and skills needed to succeed in installing, managing, and troubleshooting IT systems in medical and clinical settings.

 
OnALearningCurveCommented:
Ah right,

I guessed at the layout of the table and got it wrong.

My idea was based on the table holding transactions per company which are each dated for example:

Company      Date               Comment      Value   ....

Company1    01/03/2011     Printer          £150.00

the ELSE sned_value should have read ELSE spend_value ( he idea being it would add up the value of each entry if within the correct date range).

So you have one line per customer with a field for each month and a total value in each month (is this correct?)

Let me have a think and come back to you
0
 
NeoAshuraAuthor Commented:
Yes a value for each month is correct, for each customer, however each customer has more than one mobile, so they could have multiple rows in the database. for example

Anna    12.00  10.00 12.00        077847458745  
Anna    11.11   9.00   12.00       074587458744  

does that make sense? the mobile number is the unequie identifer but i "GROUP" them for the table output. i then use the columns of jan-dec to create a dynamic total as well.

see screenshot

as you can see from the screenshot. the kit fund level takes it off the month average. when the flag is in place it will add the months together from the month where the flag is to "dec" of that year.

Is that more clear?
1.png
0
 
OnALearningCurveCommented:
Hi  ,

sorry for the delay,

I think I have a "Very Cludgy" option that would give you the results you are looking for.

based on your example above of:

customer_name         Jan      Feb      Mar     Apr    May     Jun   Jul    Etc.... renewal_month  Mobile
Mrs Anna               10.00      12.00   11.00    11.00                                              1              07...

I have created the SQL snippet attached, please take a look and let me know if it makes sense.

Obviously you will need to replace my field and table names with the correct ones and add in any missing columns in the select but I think it will make sense

Hope this helps.

Mark.
SELECT company, SUM(Jan) AS 'Jan', SUM(Feb) AS 'FEB), SUM(CASE renweal_month WHEN 1 THEN (Jan + Feb + Mar + Apr + May + Jun + Jul + Aug + Sep + Oct + Nov + Dec) WHEN 2 THEN (Feb + Mar + Apr + May + Jun + Jul + Aug + Sep + Oct + Nov + Dec) WHEN 3 THEN (Mar + Apr + May + Jun + Jul + Aug + Sep + Oct + Nov + Dec) WHEN 4 THEN (Apr + May + Jun + Jul + Aug + Sep + Oct + Nov + Dec) WHEN 5 THEN (May + Jun + Jul + Aug + Sep + Oct + Nov + Dec) WHEN 6 THEN (Jun + Jul + Aug + Sep + Oct + Nov + Dec) WHEN 7 THEN (Jul + Aug + Sep + Oct + Nov + Dec) WHEN 8 THEN (Aug + Sep + Oct + Nov + Dec) WHEN 9 THEN (Sep + Oct + Nov + Dec) WHEN 10 THEN (Oct + Nov + Dec) WHEN 11 THEN (Nov + Dec) WHEN 12 THEN (Dec) END) AS totalSpend FROM customer_table GROUP BY company

Open in new window

0
 
NeoAshuraAuthor Commented:
Hi mark thanks for the reply, my phone can't see code snippets so ill take a look when I get to a PC thanks again
0
 
OnALearningCurveCommented:
No problem,

It should work but its lengthy and quite a cludge. If I can come up with a better solution in the mean time I'll post it.

Cheers
0
 
NeoAshuraAuthor Commented:
I just looked at your code and WOW thats all i can say.. I would never of got that ever. 110% clear what it does too. Do you do this as a job? I only ask because ive just got my first job (still at uni) and im doing web development as a junior php developer.

I wont be at uni until Tuesday. so i cant test this until then is it ok if i leave this open until i test it? im sure it will work but just to make sure. And i will award you the points.

Many thanks would'nt of got it without you.

0
 
OnALearningCurveCommented:
No Problem,

Hope it works when you test it :)

I pretend to be a sysadmin for my day job but the role is quite varied so involves a little PHP and ASP work on company intranet pages and a fair amount of SQL writing to produce management reports (hence the ability to come up with random cludges like the one above)

Good luck with Uni and the PHP work.

Let me know if your test works,  if not I'll have another shot at it.
0
 
NeoAshuraAuthor Commented:
Hi just testing and got an error any chance you could help with this please?

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 '), SUM(CASE renweal_month WHEN 1 THEN (Jan + Feb + Mar + Apr + May + Jun + Jul +' at line 1 IN SELECT c.customer_name, c.network, c.kit_fund_level, COUNT(mobile_number), SUM(Jan09), SUM(Feb09), SUM(Mar09), SUM(Apr09), SUM(May09), SUM(Jun09), SUM(Jul09), SUM(Aug09), SUM(Sep09), SUM(Oct09), SUM(Nov09), SUM(Dec09) FROM customer c LEFT JOIN bill_detail2009 d ON c.customer_name = d.customer_name WHERE c.network = 'O2' GROUP BY customer_name ORDER BY SUM(Jan09)+ SUM(Feb09)+SUM(Mar09)+SUM(Apr09)+SUM(May09)+SUM(Jun09)+SUM(Jul09)+ SUM(Aug09)+ SUM(Sep09)+ SUM(Oct09) +SUM(Nov09) +SUM(Dec09) DESC
$query4 = "SELECT customer_name, SUM(Jan09) AS 'Jan', SUM(Feb09) AS 'FEB' , SUM(Mar09) AS 'Mar',  SUM(Apr09) AS 'Apr',  SUM(May09) AS 'May',  SUM(Jun09) AS 'Jun',  SUM(Jul09) AS 'Jul',  SUM(Aug09) AS 'Aug',  SUM(Sep09) AS 'Sep',  SUM(Aug09) AS 'Aug',  SUM(Sep09) AS 'Sep',  SUM(Oct09) AS 'Oct',  SUM(Nov09) AS 'Nov',  SUM(Dec09) AS 'Dec'), SUM(CASE renweal_month WHEN 1 THEN (Jan + Feb + Mar + Apr + May + Jun + Jul + Aug + Sep + Oct + Nov + Dec) WHEN 2 THEN (Feb + Mar + Apr + May + Jun + Jul + Aug + Sep + Oct + Nov + Dec) WHEN 3 THEN (Mar + Apr + May + Jun + Jul + Aug + Sep + Oct + Nov + Dec) WHEN 4 THEN (Apr + May + Jun + Jul + Aug + Sep + Oct + Nov + Dec) WHEN 5 THEN (May + Jun + Jul + Aug + Sep + Oct + Nov + Dec) WHEN 6 THEN (Jun + Jul + Aug + Sep + Oct + Nov + Dec) WHEN 7 THEN (Jul + Aug + Sep + Oct + Nov + Dec) WHEN 8 THEN (Aug + Sep + Oct + Nov + Dec) WHEN 9 THEN (Sep + Oct + Nov + Dec) WHEN 10 THEN (Oct + Nov + Dec) WHEN 11 THEN (Nov + Dec) WHEN 12 THEN (Dec) END) AS totalSpend FROM customer_table GROUP BY company";
$result4 = mysql_query($query4) or die(mysql_error() . " IN $query4");

Open in new window

0
 
NeoAshuraAuthor Commented:
sorry this is the correct error code, Ignore one above.

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 '), SUM(CASE renweal_month WHEN 1 THEN (Jan + Feb + Mar + Apr + May + Jun + Jul +' at line 1 IN SELECT customer_name, SUM(Jan09) AS 'Jan', SUM(Feb09) AS 'FEB' , SUM(Mar09) AS 'Mar', SUM(Apr09) AS 'Apr', SUM(May09) AS 'May', SUM(Jun09) AS 'Jun', SUM(Jul09) AS 'Jul', SUM(Aug09) AS 'Aug', SUM(Sep09) AS 'Sep', SUM(Aug09) AS 'Aug', SUM(Sep09) AS 'Sep', SUM(Oct09) AS 'Oct', SUM(Nov09) AS 'Nov', SUM(Dec09) AS 'Dec'), SUM(CASE renweal_month WHEN 1 THEN (Jan + Feb + Mar + Apr + May + Jun + Jul + Aug + Sep + Oct + Nov + Dec) WHEN 2 THEN (Feb + Mar + Apr + May + Jun + Jul + Aug + Sep + Oct + Nov + Dec) WHEN 3 THEN (Mar + Apr + May + Jun + Jul + Aug + Sep + Oct + Nov + Dec) WHEN 4 THEN (Apr + May + Jun + Jul + Aug + Sep + Oct + Nov + Dec) WHEN 5 THEN (May + Jun + Jul + Aug + Sep + Oct + Nov + Dec) WHEN 6 THEN (Jun + Jul + Aug + Sep + Oct + Nov + Dec) WHEN 7 THEN (Jul + Aug + Sep + Oct + Nov + Dec) WHEN 8 THEN (Aug + Sep + Oct + Nov + Dec) WHEN 9 THEN (Sep + Oct + Nov + Dec) WHEN 10 THEN (Oct + Nov + Dec) WHEN 11 THEN (Nov + Dec) WHEN 12 THEN (Dec) END) AS totalSpend FROM customer_table GROUP BY company
0
 
NeoAshuraAuthor Commented:
now i get this..

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 '), SUM(CASE renweal_month WHEN 1 THEN (`Jan` + `Feb` + `Mar` + `Apr` + `May` + `' at line 1 IN SELECT c.customer_name, c.network, c.renewal_month, SUM(Jan09) AS 'Jan', SUM(Feb09) AS 'FEB' , SUM(Mar09) AS 'Mar', SUM(Apr09) AS 'Apr', SUM(May09) AS 'May', SUM(Jun09) AS 'Jun', SUM(Jul09) AS 'Jul', SUM(Aug09) AS 'Aug', SUM(Sep09) AS 'Sep', SUM(Aug09) AS 'Aug', SUM(Sep09) AS 'Sep', SUM(Oct09) AS 'Oct', SUM(Nov09) AS 'Nov', SUM(Dec09) AS 'Dec'), SUM(CASE renweal_month WHEN 1 THEN (`Jan` + `Feb` + `Mar` + `Apr` + `May` + `Jun` + `Jul` + `Aug` + `Sep` + `Oct` + `Nov` + `Dec`) WHEN 2 THEN (`Feb` + `Mar` + `Apr` + `May` + `Jun` + `Jul` + `Aug` + `Sep` + `Oct` + `Nov` + `Dec`) WHEN 3 THEN (`Mar` + `Apr` + `May` + `Jun` + `Jul` + `Aug` + `Sep` + `Oct` + `Nov` + `Dec`) WHEN 4 THEN (`Apr` + `May` + `Jun` + `Jul` + `Aug` + `Sep` + `Oct` + `Nov` + `Dec`) WHEN 5 THEN (`May` + `Jun` + `Jul` + `Aug` + `Sep` + `Oct` + `Nov` + `Dec`) WHEN 6 THEN (`Jun` + `Jul` + `Aug` + `Sep` + `Oct` + `Nov` + `Dec`) WHEN 7 THEN (`Jul` + `Aug` + `Sep` + `Oct` + `Nov` + `Dec`) WHEN 8 THEN (`Aug` + `Sep` + `Oct` + `Nov` + `Dec`) WHEN 9 THEN (`Sep` + `Oct` + `Nov` + `Dec`) WHEN 10 THEN (`Oct` + `Nov` + `Dec`) WHEN 11 THEN (`Nov` + `Dec`) WHEN 12 THEN (`Dec`) END CASE) AS totalSpend FROM customer c LEFT JOIN bill_detail2009 d ON c.customer_name = d.customer_name WHERE c.network = 'O2' GROUP BY customer_name
0
 
NeoAshuraAuthor Commented:
A query i tried was this but i just get resource ID all the time...

http://www.experts-exchange.com/Web_Development/Web_Languages-Standards/PHP/Q_26949546.html
0
 
OnALearningCurveCommented:
Hi NeoAshura,

Did you try changing END to END CASE at the end of the case statement?

Cheers,

Mark,.
0
 
OnALearningCurveCommented:
You might also need to change the start of the SQL statement

FROM:

SELECT c.customer_name, c.network, c.renewal_month,

TO:

SELECT c.customer_name, MAX(c.network) AS network, MAX(c.renewal_month) as renewal_month,

As the query is grouped by customer_name and network and renewal_month would need to be aggregated in some way if they are not part of the Group By clause.

Hope this makes sense,

Mark
0
 
NeoAshuraAuthor Commented:
fix now works nicely thanks
0
 
OnALearningCurveCommented:
Glad I could help!

:)

Mark.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.