grab all items that start with x

[Onloac]

Right now I have a link list a-z and 0-9.

you click one of those links and your taken to page listing all items that start with that character.

currently I'm using:

SELECT * FROM my_table where LEFT(`my_item`,1)='$show' ORDER BY my_title ASC

all I do right now is just add a link like ?show=a and everything that starts with a is listed.

 

Now here is my problem. I want to listen all items that start with a numeric value listed all on one page instead of being spread across 0-9.

 

If another solution becomes available I'd be more then happy to hear you out. But does anyone know how I can change my query to only list items that start with numbers?

[bulrush]

SELECT * FROM mytable WHERE mytextfield like 'a%';

% is a wildcard representing zero or more characters, just like * in unix. For a single character wildcard in SQL, use underscore (_).

 

Ooops, I see your question now.

SELECT * FROM mytable WHERE (myfield like '0%') or (myfield like '1%') or
(myfield like '2%') or (myfield like '3%') or (myfield like '4%') ...

Get the idea?

 

[abdfahim]

should be something like

SELECT * FROM my_table where `my_item` LIKE '[0-9]%' ORDER BY my_title ASC

[bulrush]

I just double checked the LIKE docs and SQL does support Abdbuet's [0-9] syntax. His way is less typing.

 

OTOH, if you want to show all items that DON'T begin with letters, you can also do this:

SELECT * FROM mytable WHERE myfield like '[!a-zA-Z]%';

! is a negator.