getting a date array from a mysql database?

Question

I have a database with date field is this format "2010.06.11. | 10:26 13"

What is need is a php array that would hold all the different dates, .i.e.

array[0] = "2010.06.09."

array[1] = "2010.06.10."

array[2] = "2010.06.11."

Currently I am doing it by selecting the whole table, then looping through the result and adding the date substr to an array if it is not already there.

But maybe there is a faster way? Thanks.

You should think about normalizing this field into a DATETIME field, if possible. :) — deceze
– deceze ♦, Commented Jun 11, 2010 at 10:21

Matteo Riva · Accepted Answer · 2010-06-11 11:24:34Z

2

You can directly select only the distinct dates from the database:

SELECT DISTINCT SUBSTRING(date, 1, 11) AS date_string FROM table

edited Jun 11, 2010 at 11:24

answered Jun 11, 2010 at 10:05

Matteo Riva

25.1k13 gold badges75 silver badges105 bronze badges

Sign up to request clarification or add additional context in comments.

4 Comments

Roger Travis Over a year ago

thanks for the answer. "SELECT DISTINCT (TIME) FROM..." works great by filtering out the events with the same time. Yet "SELECT DISTINCT SUBSTRING(TIME, 1, 11) FROM..." somehow seems to go into a forever loop and doesn't give any results...

Roger Travis Over a year ago

tried "SELECT DISTINCT(LEFT(TIME, 11)) FROM..." also won't give any results.

Roger Travis Over a year ago

GOT IT! THANKS MAN! I JUST HAD TO USE $row1['LEFT(TIME,11)'] WHEN GOING THROUGH RESULTS.

Matteo Riva Over a year ago

Ah now I see what you mean, just add an alias to the query, see my edit

Collectives™ on Stack Overflow

getting a date array from a mysql database?

1 Answer 1

4 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

4 Comments

Your Answer

Sign up or log in

Post as a guest

Related