SitePoint Sponsor

User Tag List

Results 1 to 8 of 8

Hybrid View

  1. #1
    SitePoint Wizard TheRedDevil's Avatar
    Join Date
    Sep 2004
    Location
    Norway
    Posts
    1,198
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)

    Can mysql sort the query by serveral values?

    What Im wondering on, is there any way to sort a mysql query after serveral values, which might "interfer" with eachother (i.e. that one might overrule the others when beeing sorted) like you can with the php command array_multisort?

    I have included the code I used to manage this with array_multisort and a little explanation of what I want to archive. If anyone knows if this is possible to do in only mysql it would be really nice to know how.

    Thanks.

    I.e. you have a bunch of articles that is submitted on the same day, some of those also have paid to be displayed above the other articles. The rest of the articles should be displayed after the time they was submitted.

    Hence you got these values to sort by:
    timestamp = exact time it got submitted.
    date2 = 0000.00.00 i.e. the day it got submitted.
    dist_plan_type = payment plan, goes from 1 (best) to 4 (free).

    Iv tried several ways to solve this in a pure mysql query, but the timestamp ends up deciding the way they get picked up, and hence the articles is not displayed the right way.

    PHP Code:
    $query "select id, headline, summary, dist_plan_type, date2, timestamp from  ".$t_release." where enabled=1 order by date2 DESC, dist_plan_type ASC limit $s$num_rows_on_page";

    $result MYSQL_QUERY($query);

    $sort_array = array();

    while(
    $buffer mysql_fetch_assoc($result))
        {
        
    $sort_array[] = array('date' => $buffer['date2'], 'type' => $buffer['dist_plan_type'], 'timestamp' => $buffer['timestamp'], 'id' => $buffer['id'], 'headline' => stripslashes($buffer['headline']), 'summary' => stripslashes($buffer['summary']));
        }

    foreach (
    $sort_array as $key => $value
        {
      
    $date[$key] = $value['date'];
      
    $timestamp[$key] = $value['timestamp'];
        
    $type[$key] = $value['type'];
        }

    array_multisort($dateSORT_DESC$typeSORT_ASC$timestampSORT_DESC$sort_array);

    $total count($sort_array) - 1;

    for (
    $nr=0;$nr <= $total;++$nr)
        {
    //echoes the result 

  2. #2
    SitePoint Guru
    Join Date
    Nov 2004
    Location
    Parry Sound, ON
    Posts
    725
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    PHP Code:
     $query "select id, headline, summary, dist_plan_type, date2, timestamp from  ".$t_release." where enabled=1 order by date2 DESC, dist_plan_type ASC limit $s$num_rows_on_page"
    You almost had it here. Just reverse the sort order:
    PHP Code:
     $query "select id, headline, summary, dist_plan_type, date2, timestamp from  ".$t_release." where enabled=1 order by dist_plan_type ASC, date2 DESC limit $s$num_rows_on_page"

  3. #3
    SitePoint Wizard TheRedDevil's Avatar
    Join Date
    Sep 2004
    Location
    Norway
    Posts
    1,198
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)
    Thanks for your answer HardCoded.

    The current query works, Im wondering if its possible to add a third "sort" by value into the query.

    Since if I do like this, the query fails.
    PHP Code:
    $query "select id, headline, summary, dist_plan_type, date2, timestamp from  ".$t_release." where enabled=1 order by dist_plan_type ASC, date2 DESC, timestamp DESC  limit $s$num_rows_on_page"
    And is it forexample possible to sort by several different values where one of them might "overrule" the others even if the other should be posted before it.

    For Example:
    Article | date | type | timestamp (just using a nr so the difference is easier spotted)
    Test1 | 2005.02.06 | 4 | 1
    Test2 | 2005.02.06 | 1 | 2
    Test3 | 2005.02.06 | 2 | 3
    Test4 | 2005.02.06 | 4 | 4
    Test5 | 2005.02.06 | 1 | 5

    Now these articles is displayed like this:

    Test5
    Test2
    Test3
    Test4
    Test1

    This is what Im wondering is possible with a pure mysql query, I already got it working with my above php code. But if there is anyway to do this with mysql I would really like to learn it.

    Hopefully I managed to explain it better now. Thanks

  4. #4
    SitePoint Wizard Lats's Avatar
    Join Date
    Jun 2003
    Location
    Melbourne, AU
    Posts
    1,142
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I may be missing something, but to get that result, this works for me...
    Code:
    SELECT 
    	article, 
    	thedate, 
    	thetype, 
    	thetime
    FROM 
    	sorting
    ORDER BY 
    	thedate DESC, 
    	thetype ASC,
    	thetime DESC
    Lats...

  5. #5
    SitePoint Wizard TheRedDevil's Avatar
    Join Date
    Sep 2004
    Location
    Norway
    Posts
    1,198
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)
    Oh, strange. When I try to add the third order by value the query fails. But it dont give any error message at all, just dont fetch any values.

    Can it have something with mysql versions to do?

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,347
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    it returns rows, but when you add something to the ORDER BY, it doesn't? weird

    can you show this query?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Wizard Lats's Avatar
    Join Date
    Jun 2003
    Location
    Melbourne, AU
    Posts
    1,142
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I've just noticed you're using timestamp as a fieldname - as it's a reserved word, you can either rename it, or surround the fieldname in backticks like `timestamp`
    Lats...

  8. #8
    SitePoint Wizard TheRedDevil's Avatar
    Join Date
    Sep 2004
    Location
    Norway
    Posts
    1,198
    Mentioned
    4 Post(s)
    Tagged
    1 Thread(s)
    Sorry for not replying before now.

    The query in question is the one displayed earlier in the thread.

    However I wrote a small test script today on my own server, and then it worked perfectly adding three order by values. And it got sorted correctly.

    It still dont work at the other webpage, but I figure the issue might lie in some of the info in the database since it contain a few thousend articles. And since its not really a problem anymore as its working using array_multisort to do the sorting I wont put any more time into it.

    Thanks alot for the help guys!


Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •