SitePoint Sponsor

User Tag List

Results 1 to 8 of 8

Thread: Order By

  1. #1
    SitePoint Zealot
    Join Date
    Apr 2008
    Location
    Plano, TX
    Posts
    115
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Order By

    I really just have a simple question about this. i have the search engine i'm working on and when people search for a zipcoded say 75075 ( plano tx ) it still pulls up 75073, 75074, 75075, 75076 all plano zipcodes. i'm wanting to do some kind of order by where it shows 75075 first then the rest after that. is this possible or am I chasing pandoras box. I've been looking everywhere I just can't find it. thnx.

    basic php sql for the code:

    PHP Code:
    if ($zipcode == "yes") {
    $ordrby "Zip";
    } else {
    $ordrby "name"// this is what it orders by when they search by city/state
    }

    // Removed code //

    mysql_query("SELECT DISTINCT * FROM $tabledad WHERE City = '$city' AND (Name = '$name' or $q) GROUP BY Address ORDER BY $odrby"); 
    this is just basicly what it says. I don't feel it's necessary to post a bunch of code that's not really part of what i'm asking.

  2. #2
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,707
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    all you need in the order by clause:
    Code:
    ORDER BY FIELD(zipcodefield, '75075', '75074', '75073', '75076')
    if you knew the exact zip codes that would come up. you could also use this standard sql:
    Code:
    ORDER BY 
    CASE
      WHEN zipcodefield='75075' THEN 0 ELSE 1 END,
       zipcodefield
    which puts all other zipcodes returned in the result set after 75075. the difference is you don't need to know what those other zipcodes are. the final zipcodefield just puts the rest of them in alpha/numeric order

  3. #3
    SitePoint Zealot
    Join Date
    Apr 2008
    Location
    Plano, TX
    Posts
    115
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    thanx for the post and that'd work but we need something a little more dynamic. we have zipcodes all over the usa.

  4. #4
    SitePoint Wizard guelphdad's Avatar
    Join Date
    Oct 2003
    Location
    St. Catharines, ON Canada
    Posts
    1,707
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    well yes you'd make it dynamic by substituting a variable for 75075 which would hold the zip code they would be searching for. you did suggest that they could search on 75075 (or 90210 or whatever) and pull up all the related zip codes for Plano and you wanted the 75057 at the top which is what the order by clause i've shown will do in the second example.

    the topmost of them would only be if you knew what exact codes you were sorting and is less dynamic.

  5. #5
    SitePoint Zealot
    Join Date
    Apr 2008
    Location
    Plano, TX
    Posts
    115
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    ok, i'm not sure exactly what you mean. sorry. do you think you could give an example

  6. #6
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,220
    Mentioned
    58 Post(s)
    Tagged
    3 Thread(s)
    the combination of SELECT DISTINCT * and GROUP BY address just absolutely boggles my mind

    why are you grouping? what happens in the non-grouped columns? and why DISTINCT?

    but the part that really floors me is that you can search on a specific 5-digit string and have it find other 5-digit strings that aren't equal to the one you searched for
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  7. #7
    SitePoint Mentor bronze trophy
    John_Betong's Avatar
    Join Date
    Aug 2005
    Location
    City of Angels
    Posts
    1,806
    Mentioned
    73 Post(s)
    Tagged
    6 Thread(s)
    Hi machutta,

    If your code has been copied and pasted then there is an error in the spelling of $ordrby and $odrby.

    PHP Code:
    mysql_query("SELECT DISTINCT * FROM $tabledad WHERE City = '$city' AND (Name = '$name' or $q) GROUP BY Address ORDER BY $odrby"); 




    .

  8. #8
    SitePoint Zealot
    Join Date
    Apr 2008
    Location
    Plano, TX
    Posts
    115
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Yea, the code was copied and pasted. but $odrby is spelled right. I have another variable as $orderby. either way it doesn't matter I used the code you said this morning and it worked. I just had to play with it for a little bit. thank you very much.


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
  •