SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Wizard
    Join Date
    Mar 2002
    Location
    Bristol, UK
    Posts
    2,240
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Reordering MySQL Resultset

    Hi guys,

    I'm developing a site for a client who wants to have the flexibility of changing the order of the navigation items on her site.

    Now as far as I'm aware it's not possible using an SQL query alone to specify a set of row IDs to set the order of the results (without executing one query for each item).

    Anyway, so I've got a numerical array which contains the IDs for the pages, in the order they need to appear on the navigation:

    PHP Code:
    $navorder = array(34,10,13,14,21,27); 
    So the page with the ID 34 should come first, followed by 10, etc (you get the picture).

    What I want to do is build this into the site so that I can reorder the results of the MySQL query which generates the navigation system.

    Here is my current query:

    PHP Code:
    SELECT id,title,path FROM page WHERE parent AND exclude 'N' 
    I'm aware that I need to use some kind of loop based on the $navorder array but I'm at a loss as to how to get this working.

    If anyone could point me in the right direction I'd very much appreciate it.

    Thanks in advance

    Sam
    Sam Hastings

  2. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by SJH
    Now as far as I'm aware it's not possible using an SQL query alone to specify a set of row IDs to set the order of the results
    but it is
    Code:
    SELECT id
         , title
         , path 
      FROM page 
     WHERE parent = 0 
       AND exclude = 'N' 
    order
        by field(id,34,10,13,14,21,27)
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard
    Join Date
    Mar 2002
    Location
    Bristol, UK
    Posts
    2,240
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Wow, I never knew that!

    That's giving me a syntax error though unfortunately. I'm running MySQL 3.23.58.

    Here's the exact error:

    Code:
    #1064 - You have an error in your SQL syntax near '(id,34,10,13,14,21,27) LIMIT 0, 30' at line 1
    Does that mean the field() function isn't available on this version of MySQL?

    Sam
    Sam Hastings

  4. #4
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    3.23? whoa, that's the version before the version before the version before the current version

    are you still running windows 95 too?
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  5. #5
    SitePoint Wizard
    Join Date
    Mar 2002
    Location
    Bristol, UK
    Posts
    2,240
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Shared hosting account I think unfortunately.

    I'll tell my client to get onto her host about this

    Thanks though. And if anyone has any other ideas I'd appreciate it.

    Sam
    Sam Hastings

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,350
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    other ideas? well, sure
    Code:
    order
        by case id
           when 34 then 1
           when 10 then 2
           when 13 then 3 
           when 14 then 4 
           when 21 then 5 
           when 27 then 6
           else 9999
           end
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Wizard
    Join Date
    Mar 2002
    Location
    Bristol, UK
    Posts
    2,240
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    That seems to work for me. Cheers

    Sam
    Sam Hastings


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
  •