Skip to main content ↑ ↓
Topic: bar chart - how to sort and order correctly date by day? (Read 1681 times) previous topic - next topic

bar chart - how to sort and order correctly date by day?

Hello all,
I need to display registered users by day on a bar chart.
I can get the chart done, but the sorting is wrong.
I need to display the last 10 days in descendend order but the Mysql LIMIT function displays only the FIRST 10 records See image:

https://imgur.com/a/RwNZuuK

But how can I display the chart in reverse order (most recent on the right?)? Like this pic:

https://imgur.com/a/tUhXtzL

This is my MySQL code:
Code: [Select]
"SELECT  COUNT(r.privacy_reg) AS registrati, DATE_FORMAT(r.data_registrazione_reg, '%d/%m/%Y') AS data FROM registrazioni AS r GROUP BY data ORDER BY r.data_registrazione_reg DESC limit 10"

CAn someone help me?
TIA
tony

Re: bar chart - how to sort and order correctly date by day?

Reply #1 –
Assuming your query is correct, this would display it in descending order
Code: [Select]
SELECT  COUNT(r.privacy_reg) AS registrati, DATE_FORMAT(r.data_registrazione_reg, '%d/%m/%Y') AS data FROM registrazioni AS r GROUP BY data ORDER BY data DESC limit 10

Re: bar chart - how to sort and order correctly date by day?

Reply #2 –
Hi willvin, your query display the same results. After some other researches I found another way (subquery) to display the correct data. Here is my updated query for anyone having my problem:
Code: [Select]
SELECT *
FROM (
	SELECT 
		COUNT(privacy_reg) AS registrati,
		DATE_FORMAT(data_registrazione_reg,	'%Y/%m/%d') AS datareg,
		DATE_FORMAT(data_registrazione_reg,	'%d/%m/%Y') AS datavis

FROM
	registrazioni
GROUP BY
	datareg
ORDER BY
datareg DESC
LIMIT 15
) as reg
ORDER BY
	datareg ASC
I use the datavis column for my chart.

HTH
Tony