Sales Results

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
tsalmeida
Forum Commoner
Posts: 38
Joined: Sat Jul 06, 2013 2:23 pm

Sales Results

Post by tsalmeida »

hello,
I would like to make a table in HTML to show the values of the sales results per day in each month.

example.

       January | February
1 | 100.00 | 132.00
2 | 50.00 | 200.00
3 | 0.00 | 0.00
4 | 500.00 | 450.00

it is like an excel spreadsheet.

but I do not know how the cods to show that way, because if you do not have anything recorded, as shown on the 3rd, the sql skips this line and shows.

I have no code yet, wanted an idea to get started.
Can anyone help?
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Sales Results

Post by requinix »

Can you write a SQL query that returns:
1. The day number
2. The month number
3. The total sales for that day
Make sure it's sorted by day first, month second.

It could look something like

Code: Select all

SELECT DAYOFMONTH(date), MONTH(date), SUM(amount)
FROM sales
GROUP BY DAYOFMONTH(date), MONTH(date) /* also sorts */
User avatar
Christopher
Site Administrator
Posts: 13596
Joined: Wed Aug 25, 2004 7:54 pm
Location: New York, NY, US

Re: Sales Results

Post by Christopher »

tsalmeida wrote:but I do not know how the cods to show that way, because if you do not have anything recorded, as shown on the 3rd, the sql skips this line and shows.
It sounds like you do not have data for some days. So you need to loop through the months (1..12) and then the days (1..31) as you build your HTML table. Perhaps it would make sense to loop through the data from the database and build a two-dimensional array. Then use that array to build the HTML table.
(#10850)
tsalmeida
Forum Commoner
Posts: 38
Joined: Sat Jul 06, 2013 2:23 pm

Re: Sales Results

Post by tsalmeida »

requinix wrote:

Code: Select all

SELECT DAYOFMONTH(date), MONTH(date), SUM(amount)
FROM sales
GROUP BY DAYOFMONTH(date), MONTH(date) /* also sorts */
this is a good query to start dev my code
Christopher wrote:
tsalmeida wrote:but I do not know how the cods to show that way, because if you do not have anything recorded, as shown on the 3rd, the sql skips this line and shows.
It sounds like you do not have data for some days. So you need to loop through the months (1..12) and then the days (1..31) as you build your HTML table. Perhaps it would make sense to loop through the data from the database and build a two-dimensional array. Then use that array to build the HTML table.
i will follow your advice, and try to put this together with the query..

i will be back with my code later...

thanks guys
User avatar
requinix
Spammer :|
Posts: 6617
Joined: Wed Oct 15, 2008 2:35 am
Location: WA, USA

Re: Sales Results

Post by requinix »

To explain:

My method would have to craft a query that returns data in the order you need to be reading it to build the HTML table. It lets you skip the processing step of arranging the data into an array, but it can be a bit more difficult to wrap one's head around.
Christopher's method is more obvious, so to speak, where you get all the data (in whatever order) and then use a couple foreach loops to extract what you need. The benefit to this is that it's more obvious how the code works.
Either one forces you to deal with the holes in the data the same way though, so neither will help you particularly with that part. (Which isn't difficult anyway.)
tsalmeida
Forum Commoner
Posts: 38
Joined: Sat Jul 06, 2013 2:23 pm

Re: Sales Results

Post by tsalmeida »

hi guys...

I made using a FOR every month ... I do not know if this is the correct way, but it worked
Post Reply