Posted by eklund on 06/15/06 01:19

ZeldorBlat wrote:
> 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

I did not know I could do that. (Newbie here!)
Is there a way to do the same for Titles starting in A? Also, selecting
a group of characters. Such as starting in A-I?

[Back to original message]