|
|
Posted by eklund on 06/15/06 04:06
ZeldorBlat wrote:
> 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...
Is there a way to have both The, and A in a replace statement?
Everything I've read indicated that it's only for one string of
characters, so I've tried using 2 replaces in a row, but that didn't
work either.
I just realized I made reference to a piece of script in my last post
that was actually from someone else that was trying to help me out.
Oops. :-) This part " (Ignoring 'The ', and 'A ' - just like in your
example, which I still can't figure out whats wrong with it. Looks nice
though. :-) )"
They wanted me to try this, but it doesn't work, and I can't figure out
why. (I get zero results every time)
SELECT title,
CASE WHEN SUBSTRING(title,1,4)='The ' THEN SUBSTRING(title,5)
WHEN SUBSTRING(title,1,2)='A ' THEN SUBSTRING(title,3)
ELSE title END as title1
FROM reviews WHERE CASE WHEN SUBSTRING(title,1,4)='The ' THEN
SUBSTRING(title,5)
WHEN SUBSTRING(title,1,2)='A ' THEN SUBSTRING(title,3)
ELSE title END LIKE '$letter%'
ORDER BY title1
Looks really complicated, but I get what it's trying to do, and seems
ok to me, but doesn't work!
Navigation:
[Reply to this message]
|