|
|
Posted by ZeldorBlat on 06/15/06 03:09
eklund@gmail.com wrote:
> 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. :-) )
The query should work in both cases. Consider the following:
REPLACE('The ', '', 'A Nightmare on Elm Street') gives you 'A Nightmare
on Elm Street'
REPLACE('The ', '', 'The Nightmare on Elm Street') gives you 'Nightmare
on Elm Street'
In other words, the value of REPLACE(...) for something that doesn't
begin with 'The ' will just be the same string (except in the case
where 'The ' appears somewhere else in the string). But you get my
point...
Navigation:
[Reply to this message]
|