Sorting Movie Titles from a Database of Reviews

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

Re: Sorting Movie Titles from a Database of Reviews

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

Re: Sorting Movie Titles from a Database of Reviews

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?

Re: Sorting Movie Titles from a Database of Reviews

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).

Re: Sorting Movie Titles from a Database of Reviews

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. :-) )

Re: Sorting Movie Titles from a Database of Reviews

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...

Re: Sorting Movie Titles from a Database of Reviews

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!

Re: Sorting Movie Titles from a Database of Reviews

eklund wrote:

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.

Re-read the answer above


eklund@gmail.com wrote:
> 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!

Re: Sorting Movie Titles from a Database of Reviews

strawberry wrote:
> eklund wrote:
>
> 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.
>
> Re-read the answer above
>
>
> eklund@gmail.com wrote:
> > 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!


I've tried putting 2 replaces for the 2 words I want to ignore, but it
doesn't work! But I actually got the last code I posted to work.... in
phpmyadmin. Now I just have to find out why it doesn't work when I
bring it into my php document.

Re: Sorting Movie Titles from a Database of Reviews

eklund@gmail.com wrote:
> strawberry wrote:
> > eklund wrote:
> >
> > 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.
> >
> > Re-read the answer above
> >
> >
> > eklund@gmail.com wrote:
> > > 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!
>
>
> I've tried putting 2 replaces for the 2 words I want to ignore, but it
> doesn't work! But I actually got the last code I posted to work.... in
> phpmyadmin. Now I just have to find out why it doesn't work when I
> bring it into my php document.

I got it working now. Well, I shouldn't say I, maybe We. :-) Thanks for
the help everyone!