Posted by ZeldorBlat on 06/14/06 23:02

eklund@gmail.com wrote:
> Hello, I hope I can get some help on this. I really really really need
> to get this working!
>
> Here is an overview of what I am trying to do. I have a database of
> movie reviews, and I am trying to sort them alphabetically. (So I can
> select all reviews starting with $someletter) The catch is I don't want
> titles starting with The to be included with T. For example. "The
> Exorcism of Emily Rose" will be under E, not T. Preferably ignoring "A"
> as well, as in "A Nightmare on Elm Street" but I don't want to get
> greedy. :-)
>
> Here is what I got so far.
> $sreview = mysql_query ("SELECT * FROM reviews ORDER BY REPLACE(Title,
> 'The ', '') ASC");
>
> That will arrange it alphabetically, and it ignores "The" when sorting,
> but outputs it later on when I ask for the title. So far, so good.
>
>
> Next, I want to sort them for every review starting with $id (A letter,
> number, or group of letters, or a group of numbers.
>
> I figured out this:
> WHERE Title LIKE '$id%' //$id being a letter of the alphabet, or a
> number
>
> But I can't get that to work in my script. If I put it in front of the
> ORDER BY REPLACE it does not include the entries that have "The" in
> front of them. If I put it after the ORDER BY REPLACE it does not work
> at all.
>
>
>
> What I would like to have is a script that I can sort by a single
> letter/number, or a group of them. (0-9, A-I, etc.) And ignoring The,
> and hopefully A.
>
> Can someone help me out, or at least point me in the right direction.
>
> Thanks a lot!
> ~Rich

Why not use the same technique in the where clause as you did in the
order by?

SELECT *
FROM reviews
WHERE REPLACE(Title, 'The ', '') like 'F%'
ORDER BY REPLACE(Title, 'The ', '') ASC

[Back to original message]