[SOLVED] Alphabetical Sort ... SQL query issue

[kjtocool]

I am working on an Alphabetical sort, and am wondering if there is an easier way to handle finding numerical named movies like: "88 Weeks." Currently I have to use something like:

 

$query = "SELECT * FROM myCinema_Movies WHERE (movieName like '0%' or movieName like '1%' or movieName like '2%' or movieName like '3%' or movieName like '4%' or movieName like '5%' or movieName like '6%' or movieName like '7%' or movieName like '8%' or movieName like '9%') order by movieName";

 

Is it possible to simplify it, or am i stuck using this?

[dooper3]

Forgive my ignorance, but surely "ORDER BY movieName" is an alphabetical sort...?

[rhodesa]

$query = "SELECT * FROM myCinema_Movies WHERE movieName < 'a'";

[kjtocool]

Forgive my ignorance, but surely "ORDER BY movieName" is an alphabetical sort...?

 

Of course.

 

The problem with that is, I want to get JUST movies that start with A, B, C, etc, and I want an option that is "Other" and includes just movies starting with numbers, etc.  Since I need a result set, and since I don't know exactly how many of these movies there are, a simple ORDER BY is not sufficient.

 

SELECT * FROM myCinema_Movies WHERE movieName < 'a'

 

The above, is exactly what I was looking for.

[fenway]

Why not order by LEFT(movieName, 1)?

[kjtocool]

Because I want a result that excludes anything starting with a letter.

[rhodesa]

What they are looking for is not really what the title of the post says. They know what a user clicks A, how to select all movies that start with A, but they wanted know how to find movies starting with any numeric value.

[fenway]

Because I want a result that excludes anything starting with a letter.

Well, a REGEXP can do that, too.

[kjtocool]

Heh, you're too advanced for me, I don't know what that is.  ???

[rhodesa]

Because I want a result that excludes anything starting with a letter.

Well, a REGEXP can do that, too.

 

Can regex be used in MYSQL queries? You wouldn't want to do that with PHP, as then you would have to load ALL the records, and filter them out after as you parse them. It's much better to filter out from a DB level.

[fenway]

Can regex be used in MYSQL queries?

Yes, but only to return true/false.