SitePoint Sponsor

User Tag List

Results 1 to 7 of 7
  1. #1
    SitePoint Guru
    Join Date
    Dec 2001
    Location
    San Diego, CA
    Posts
    617
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    MySQL: Select #'s

    I have a database of bands. Users can click on a letter and it will pull all the bands that start with that letter using the following:

    SELECT * FROM bands WHERE ( band_name like \'' . $_GET['letter']. '%\' ) ORDER BY band_name

    However, there are some bands where the name starts with a number. How can I select these bands?

  2. #2
    My precious!!! astericks's Avatar
    Join Date
    Mar 2002
    Location
    Vancouver, BC
    Posts
    1,971
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Just a suggestion...I dont know if it'll help or not.

    You could give the bands a unique id, and then identify them by that id.

  3. #3
    SitePoint Zealot Alarion's Avatar
    Join Date
    May 2001
    Location
    Virginia
    Posts
    126
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    I am supposing the user is clicking on a letter of the alphabet first, which is passed in the $_GET[letter] variable?

    For #'s you could use "num" as the "letter". Instead of passing that to the query, you could run a new query if $_GET[letter] === "num":

    PHP Code:
    <?php
    if ($_GET[letter] === 'num') {
      
    $sql "SELECT * FROM bands WHERE ( substring(bandname, 1, 1) in ('0', '1', '2', '3', '4', '5', '6', '7', '8', '9', '0') ) ORDER BY band_name";
    } else {
      
    $sql "SELECT * FROM bands WHERE ( band_name like \'' . $_GET['letter']. '%\' ) ORDER BY band_name";
    }
    ?>
    -=Alarion=-
    Protollix - Linux hosting from $3.95/m

  4. #4
    SitePoint Guru
    Join Date
    Dec 2001
    Location
    San Diego, CA
    Posts
    617
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Actually, I need to get everything that isnt an actual letter. I have a database of record labels too, and some (actually, just one) is called '+/- Records'. Can you select everything that doesn't start with a letter?

  5. #5
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,015
    Mentioned
    53 Post(s)
    Tagged
    2 Thread(s)
    select foo, bar
    from yourtable
    where bandname REGEXP [^[:alpha:]]

    caution: i got this from the docs, i haven't tested it

    http://www.mysql.com/doc/en/Regexp.html

    http://evolt.org/article/Regular_Exp...700/index.html


    rudy

  6. #6
    Making a better wheel silver trophy DR_LaRRY_PEpPeR's Avatar
    Join Date
    Jul 2001
    Location
    Missouri
    Posts
    3,428
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    neither Alarion's nor r937's queries will use an index on band_name if there is one (and it seems like it would be good to have). this will

    ... WHERE (band_name < 'a' OR band_name > 'zzz') AND band_name NOT LIKE 'z%'

    you can replace zzz with as many z's as you want, but 3 is better than 1, if band_name is indexed at least, as it will let the optimizer eliminate names that are between z and zzz.
    - Matt ** Ignore old signature for now... **
    Dr.BB - Highly optimized to be 2-3x faster than the "Big 3."
    "Do not enclose numeric values in quotes -- that is very non-standard and will only work on MySQL." - MattR

  7. #7
    SitePoint Guru
    Join Date
    Dec 2001
    Location
    San Diego, CA
    Posts
    617
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    Thanks, I got it to work perfectly


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
  •