CODEWITHSUNDEEP
phpmysqldatabase
Dylan Cross
Dylan Cross

Reputation: 5986

MySQL explode array of values to use to ORDER the result by

I have to store a different number of values in a column called, date_of, for the date of an event. There can be multiple dates that it takes place, for instance Oct 1st, 2nd, and 6th. And I need to store that in that column as three different dates. Now I do this like this: 

1350619200, 1350705600, 1350792000

So what I want to be able to do is order the mysql result by the date(s). The first date should be the one that it gets ordered by, however if there are multiple dates as shown above, if the date is pass then it should go to the second date, then third date etc.

I'm not sure how to get started with this, my main priority is to just order the results by the first date in the array, the other part of my question isn't as important.

Upvotes: 0

Views: 883

Answers (2)

LSerni
LSerni

Reputation: 57463

If you order by the first field, which is always set, then you can try extracting the field:

ORDER BY SUBSTRING_INDEX(date_of, ',', 1);

Actually, you could order by date_of, since lexicographic order is maintained. You'd be in trouble if you wanted to order by the last subfield, but since you want the first...

Now "if the first element of the date is past" is a bit tricky.

ORDER BY CASE WHEN SUBSTRING_INDEX(date_of, ',', 1) > UNIX_TIMESTAMP()
    THEN SUBSTRING_INDEX(date_of, ',', 1)
    ELSE ... END

could be a start, but then if there is no second subfield? If we iterate SUBSTRING_INDEX, we get again the first field.

You would have to iterate the CASE itself. Shudder

ORDER BY CASE WHEN SUBSTRING_INDEX(date_of, ',', 1) > UNIX_TIMESTAMP()
    THEN SUBSTRING_INDEX(date_of, ',', 1)
    ELSE
        CASE WHEN SUBSTRING_INDEX(SUBSTRING_INDEX(date_of, ',', 2),',',-1) > UNIX_TIMESTAMP()
        THEN SUBSTRING_INDEX(SUBSTRING_INDEX(date_of, ',', 2),',',-1)
        ELSE
            CASE WHEN SUBSTRING_INDEX(SUBSTRING_INDEX(date_of, ',', 3),',',-1) > UNIX_TIMESTAMP()
            THEN SUBSTRING_INDEX(SUBSTRING_INDEX(date_of, ',', 3),',',-1)
            ELSE 999999999
            END
        END
    END

I can see there's no great performances to be had from this structure. I would strongly advise for having the three dates in three different columns, or the model reengineered to have dates in a separate table.

Upvotes: 3

Aatch
Aatch

Reputation: 1856

If you store it as a string (VARCHAR) then you can just do a ORDER BY on the query. MySQL will do a lexographic sort on the string. If you are storing unix timestamps (which you appear to be), then this will work until 20th November 2286 5:46:40 PM UTC.

It works because all the timestamps are likely to be the same length, 10 digits. You just have to make sure that keep the format of the list consistent. I'd go for <number>,<number,... no spaces.

Upvotes: 1

Related Questions