按列文本的一部分排序查询结果

I have following query:

SELECT t.*, c.alias as calias FROM jos2_tabs t
        JOIN jos2_tabs_category c on c.id=t.category_id
        WHERE (category_id = 43
                    OR category_id in (select id from jos2_tabs_category WHERE parentid = 43)
                    ) AND state=1 
        ORDER BY ordering2 

And it's working as expected. But in "tabs" table I have got column "terms" which contains dates in text format (each in new line). Example:

18.05.2013-21.07.2013
22.05.2013-20.06.2013
01.06.2013-25.08.2013
15.06.2013-25.08.2013
06.07.2013-29.09.2013
20.07.2013-29.09.2013
17.08.2013-10.11.2013
31.08.2013-10.11.2013
03.09.2013-30.09.2013

What I have to do, is to modify query to order elements by dates in "terms" column. So I want to get rows which are closest to present time (can be only first row (first date). Any ideas how can I achieve this?

I need to split first row in cell "terms" to from->to pairs and then sort all rows by "from dates"

You can mix Order by with STR_TO_DATE() and SUBSTR()

How to use:

SELECT t.*, c.alias as calias FROM jos2_tabs t
JOIN jos2_tabs_category c on c.id=t.category_id
WHERE (category_id = 43
            OR category_id in (select id from jos2_tabs_category WHERE parentid = 43)
            ) AND state=1 
ORDER BY STR_TO_DATE(SUBSTR(terms, 0, 10),'%d.%m.%Y') ASC

Use select replace(terms,'.','-') from your_table WHERE 1 you will get 18.05.2013 to 18-05-2013.

Now, you can change it go get your desired result.

If you order your result by date you have to put this:

 ORDER BY DATE(columnname) DESC

Try with it

SELECT t.*, c.alias as calias FROM jos2_tabs t
    JOIN jos2_tabs_category c on c.id=t.category_id
    WHERE (category_id = 43
                OR category_id in (select id from jos2_tabs_category WHERE parentid = 43)
                ) AND state=1         

And you will get all the rows without ordering then

foreach($rows as $key=>row)
{
    $my_date = explode('-',$row['terms']);
    $from = $my_date[0];
    $to = $my_date[1];
    $order_arr[$key] = strtotime($from);
}
array_multisort($order_arr, SORT_ASC, $rows);
print_r($rows);

You will get deserved