Posted by eklund on 06/15/06 03:00

ZeldorBlat wrote:
> eklund@gmail.com wrote:
> > 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!)
>
> Welcome. Functions are expressions, and, in general, can be used just
> like constants and column names in the select list, where clause, group
> by, and order by. Keep in mind that functions can also be nested (i.e.
> the return value of some function can be used as a parameter to
> another).
>
> > Is there a way to do the same for Titles starting in A?
>
> C'mon...even a newbie can figure that out with a little bit of thinking
> :) Change the 'F%' to 'A%'. The % is a wildcard that matches 0 or
> more characters.
>
> > Also, selecting a group of characters. Such as starting in A-I?
>
> Instead of using the pattern 'F%' (an 'F' followed by zero or more
> characters) use '[A-I]%' (a single character in the range A-I followed
> by zero or more characters).

Thanks. I did know that I could change 'F%' to 'A%', (I was actually
using a $id so I can change it through my reviewsort.php?id=D I ment
movies starting with A, such as "A Nightmare on Elm Street" Sorry for
the misunderstanding. I don't think I could have found a way onto the
internet if I couldn't figure that out! lol (Ignoring 'The ', and 'A '
- just like in your example, which I still can't figure out whats wrong
with it. Looks nice though. :-) )

[Back to original message]