Page 1 of 1

Sales Results

Posted: Tue Dec 30, 2014 3:23 pm
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?

Re: Sales Results

Posted: Tue Dec 30, 2014 3:46 pm
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 */

Re: Sales Results

Posted: Tue Dec 30, 2014 4:32 pm
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.

Re: Sales Results

Posted: Tue Dec 30, 2014 8:59 pm
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

Re: Sales Results

Posted: Tue Dec 30, 2014 9:31 pm
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.)

Re: Sales Results

Posted: Sat Jan 10, 2015 9:00 am
by tsalmeida
hi guys...

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