1. Advertising
    y u no do it?

    Advertising (learn more)

    Advertise virtually anything here, with CPM banner ads, CPM email ads and CPC contextual links. You can target relevant areas of the site and show ads based on geographical location of the user if you wish.

    Starts at just $1 per CPM or $0.10 per CPC.

Sorting when using SELECT statement

Discussion in 'PHP' started by jarvi, Oct 30, 2004.

0
  1. #1
    I use something like the following to just return the records where my_field starts with the letter A.

    $result = mysql_query('SELECT * FROM my_table WHERE my_field LIKE "A%" ORDER BY my_field');
    PHP:
    This works fine for all the letters in the alphabet but how do I do a catch all for all the others? Surely there is a better way than doing a "not like" for each of the letters of the alphabet.

    Thanks,
     
    jarvi, Oct 30, 2004 IP
  2. mudnik

    mudnik Peon

    Messages:
    147
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    0
    #2
    Hi Jarvi,
    what is the output that you want exactly? Not sure what you mean by catch all. You can try using a sub-querry,
     
    mudnik, Oct 30, 2004 IP
  3. jarvi

    jarvi Well-Known Member

    Messages:
    127
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    103
    #3
    Sorry, by catch all I mean anything in my_field that doesn't start with a letter of the alphabet. That is anything except A-Z, so, things such as numbers and punctuation.
     
    jarvi, Oct 30, 2004 IP
  4. digitalpoint

    digitalpoint Overlord of no one Staff

    Messages:
    38,333
    Likes Received:
    2,613
    Best Answers:
    462
    Trophy Points:
    710
    Digital Goods:
    29
    #4
    If you wanted all the others, wouldn't it just be a NOT LIKE 'A%'?
     
    digitalpoint, Oct 30, 2004 IP
  5. jarvi

    jarvi Well-Known Member

    Messages:
    127
    Likes Received:
    1
    Best Answers:
    0
    Trophy Points:
    103
    #5
    What I'm trying to avoid is having to put NOT LIKE "A%" and NOT LIKE "B%" AND NOT LIKE "C%" etc for all the letters of the alphabet. I guess I'm looking hoping there is someway to specify all the letters of the alphabet without having to put them all in the SELECT statement.
     
    jarvi, Oct 31, 2004 IP
  6. draculus

    draculus Peon

    Messages:
    63
    Likes Received:
    3
    Best Answers:
    0
    Trophy Points:
    0
    #6
    You can try

    WHERE (my_field < "A") OR ((my_field > "Z") AND (my_field < "a")) OR (my_field > "z")
     
    draculus, Oct 31, 2004 IP
  7. mudnik

    mudnik Peon

    Messages:
    147
    Likes Received:
    2
    Best Answers:
    0
    Trophy Points:
    0
    #7
    hmmm..did the solution by draculus worked? It looked ok to me..

    Would something like this work? NOT LIKE ('[a-z]%')
    Can't remember already..
     
    mudnik, Nov 2, 2004 IP