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?
Sales Results
Moderator: General Moderators
Re: Sales Results
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
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 */- Christopher
- Site Administrator
- Posts: 13596
- Joined: Wed Aug 25, 2004 7:54 pm
- Location: New York, NY, US
Re: Sales Results
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.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.
(#10850)
Re: Sales Results
this is a good query to start dev my coderequinix wrote:Code: Select all
SELECT DAYOFMONTH(date), MONTH(date), SUM(amount) FROM sales GROUP BY DAYOFMONTH(date), MONTH(date) /* also sorts */
i will follow your advice, and try to put this together with the query..Christopher wrote: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.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.
i will be back with my code later...
thanks guys
Re: Sales Results
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.)
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
hi guys...
I made using a FOR every month ... I do not know if this is the correct way, but it worked
I made using a FOR every month ... I do not know if this is the correct way, but it worked