MySQL: Select #'s

[sccr410]

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?

[astericks]

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.

[Alarion]

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
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";
}
?>

[sccr410]

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?

[r937]

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

[DR_LaRRY_PEpPeR]

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.

[sccr410]

Thanks, I got it to work perfectly