Page 1 of 1

Custom sort for 2-dimensional array

Posted: Mon May 24, 2010 9:55 pm
by jraede
I'm doing two db queries and combining the results of both into an array of the rows. Specifically, there is a table for todo-list items and their duedates, and a table for reminders and their dates/repeat schedules, and I want to display data from both tables on a calendar-type thing.

I can order the rows within each query as I want, but I want to order them across both queries, so I'm going to need a custom sort function (I think) for the array. Basically I want to order each row within the main array by the strtotime value of its 'datetime' value, so the row with $row['datetime'] as the lowest would appear first, and the highest would appear last.

What's the best way of doing this? Or, how can I query both tables within the same mysql_query function and order by `li_datetime` in the todo list table, and also `rm_datetime` in the reminders table?

Thanks.

Re: Custom sort for 2-dimensional array

Posted: Mon May 24, 2010 10:42 pm
by requinix
If you ask me, the best way would be to sort it in the SQL. "But I can't sort the two together!" Oh yes you can.

Code: Select all

SELECT * FROM
    (SELECT "todo" AS type, li_title AS title, li_duedate AS duedate FROM todo
     UNION
     SELECT "reminder" AS type, rm_title AS title, rm_duedate AS duedate FROM reminder) items
ORDER BY duedate ASC
- The column titles from the first SELECT are used
- The two queries need the same number of columns and same data types

Re: Custom sort for 2-dimensional array

Posted: Mon May 24, 2010 10:53 pm
by jraede
Cool, didn't know about UNION. Can you do that for more than 2 tables by just adding another UNION statement?