Jump to content

Archived

This topic is now archived and is closed to further replies.

RichE

Sorting Movie Titles, but ignoring "The"

Recommended Posts

Hello, reading this forum has helped me get this far, but I think I am in need of assistance now. :-)

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, and possible point me in the right direction.

Thanks a lot!
~Rich

Share this post


Link to post
Share on other sites
[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] title,
CASE WHEN SUBSTRING(title,1,4)[color=orange]=[/color][color=red]'The '[/color] THEN SUBSTRING(title,5)
WHEN SUBSTRING(title,1,2)[color=orange]=[/color][color=red]'A '[/color] THEN SUBSTRING(title,3)
ELSE title END [color=green]as[/color] title1
[color=green]FROM[/color] [color=orange]review[/color] [color=green]WHERE[/color] CASE WHEN SUBSTRING(title,1,4)[color=orange]=[/color][color=red]'The '[/color] THEN SUBSTRING(title,5)
WHEN SUBSTRING(title,1,2)[color=orange]=[/color][color=red]'A '[/color] THEN SUBSTRING(title,3)
ELSE title END [color=orange]LIKE[/color] [color=red]'$letter%'[/color]
[color=green]ORDER BY[/color] title1 [!--sql2--][/div][!--sql3--]

Share this post


Link to post
Share on other sites
[!--quoteo(post=384011:date=Jun 14 2006, 07:01 PM:name=Barand)--][div class=\'quotetop\']QUOTE(Barand @ Jun 14 2006, 07:01 PM) [snapback]384011[/snapback][/div][div class=\'quotemain\'][!--quotec--]
[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][span style=\'color:blue;font-weight:bold\']SELECT[/span] title,
CASE WHEN SUBSTRING(title,1,4)[color=orange]=[/color][color=red]'The '[/color] THEN SUBSTRING(title,5)
WHEN SUBSTRING(title,1,2)[color=orange]=[/color][color=red]'A '[/color] THEN SUBSTRING(title,3)
ELSE title END [color=green]as[/color] title1
[color=green]FROM[/color] [color=orange]review[/color] [color=green]WHERE[/color] CASE WHEN SUBSTRING(title,1,4)[color=orange]=[/color][color=red]'The '[/color] THEN SUBSTRING(title,5)
WHEN SUBSTRING(title,1,2)[color=orange]=[/color][color=red]'A '[/color] THEN SUBSTRING(title,3)
ELSE title END [color=orange]LIKE[/color] [color=red]'$letter%'[/color]
[color=green]ORDER BY[/color] title1 [!--sql2--][/div][!--sql3--]
[/quote]


I tried that (but changed review to reviews -- my table name) and I get zero results every time. I tried figuring out what was wrong, but from what I understand about it, it looks right. [img src=\"style_emoticons/[#EMO_DIR#]/huh.gif\" style=\"vertical-align:middle\" emoid=\":huh:\" border=\"0\" alt=\"huh.gif\" /]

I've still been trying my code, and got this far:
$sreview = mysql_query ("SELECT * FROM reviews WHERE REPLACE(Title, 'The ', '') like '$id%' ORDER BY REPLACE(Title, 'The ', '') ASC")

It only works with The though, and only allows me to select 1 character. [img src=\"style_emoticons/[#EMO_DIR#]/unsure.gif\" style=\"vertical-align:middle\" emoid=\":unsure:\" border=\"0\" alt=\"unsure.gif\" /]

Share this post


Link to post
Share on other sites
Maybe it's because I used '$letter%' when I tested, you have '$id%'

Share this post


Link to post
Share on other sites
[!--quoteo(post=384084:date=Jun 15 2006, 02:34 AM:name=Barand)--][div class=\'quotetop\']QUOTE(Barand @ Jun 15 2006, 02:34 AM) [snapback]384084[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Maybe it's because I used '$letter%' when I tested, you have '$id%'
[/quote]


hmmm. I tested your code in phpmyadmin, and it works!! But when I bring it into my php document, it does not. :-( I tried letting phpmyadmin make the code for me, but still nothing.

Could it be a problem with this part of my code?
[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--][color=orange]<[/color]?
if ($row [color=orange]=[/color] mysql_fetch_row($severedreview)) {

do {
print ([color=red]" [span style=\'color:orange\']<[/color]a href[color=orange]=[/color]review.php?id[color=orange]=[/color]$row[0][color=orange]>[/color]$row[1] ($row[2])[color=orange]<[/color]/a[color=orange]>[/color][color=orange]<[/color]br /[color=orange]>[/color][color=orange]<[/color]br /[color=orange]>[/color]"[/span]);

} while($row [color=orange]=[/color] mysql_fetch_array($severedreview));
} else {print [color=red]"There are no reviews [span style=\'color:green\']in[/color] the database Starting with $severedid [color=orange]<[/color]br /[color=orange]>[/color][color=orange]<[/color]img src[color=orange]=[/color]images/spacer.gif border[color=orange]=[/color]0 width[color=orange]=[/color]1 height[color=orange]=[/color]600[color=orange]>[/color]"[/span];}
?[color=orange]>[/color]
[!--sql2--][/div][!--sql3--]
This is the code myphpadmin tells me to use:
[!--sql--][div class=\'sqltop\']SQL[/div][div class=\'sqlmain\'][!--sql1--]$sql [color=orange]=[/color] [color=red]'[span style=\'color:blue;font-weight:bold\']SELECT[/color] title,'[/span]
. [color=red]' CASE WHEN SUBSTRING(title,1,4)[span style=\'color:orange\']=[/color]\'[/span]The \[color=red]' THEN SUBSTRING(title,5)'[/color]
. [color=red]' WHEN SUBSTRING(title,1,2)[span style=\'color:orange\']=[/color]\'[/span]A \[color=red]' THEN SUBSTRING(title,3)'[/color]
. [color=red]' ELSE title END [span style=\'color:green\']as[/color] title1'[/span]
. [color=red]' [span style=\'color:green\']FROM[/color] [color=orange]reviews[/color] [color=green]WHERE[/color] CASE WHEN SUBSTRING(title,1,4)[color=orange]=[/color]\'[/span]The \[color=red]' THEN SUBSTRING(title,5)'[/color]
. [color=red]' WHEN SUBSTRING(title,1,2)[span style=\'color:orange\']=[/color]\'[/span]A \[color=red]' THEN SUBSTRING(title,3)'[/color]
. [color=red]' ELSE title END [span style=\'color:orange\']LIKE[/color] \'[/span]A%\''
. [color=red]' [span style=\'color:green\']ORDER BY[/color] title1'[/span];
[!--sql2--][/div][!--sql3--]

(I did try assigning a letter just to test. It works fine in phpmyadmin though!)

Share this post


Link to post
Share on other sites
Use it as posted

[code]$sql = "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 review 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 'A%'
         ORDER BY title1";[/code]

Share this post


Link to post
Share on other sites
I've added that part, but still nothing comes up, but it work in PHPMyAdmin! I have no idea why! So this part of the code to output the rows is right?

[code]
<?
if ($row = mysql_fetch_row($sql)) {

do {
print ("  <a href=review.php?id=$row[0]>$row[1] ($row[2])</a><br /><br />");

} while($row = mysql_fetch_array($sql));
} else {print "There are no reviews in the database Starting with $severedid <br /><img src=images/spacer.gif border=0 width=1 height=600>";}
?>
[/code]

Share this post


Link to post
Share on other sites
try changing your code to:
[code]
<?
$sql = "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 review 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 'A%'
         ORDER BY title1";
$result = mysql_query($sql) or die(mysql_error());

$row = mysql_num_rows($result);

if ($row > 0) {
   while ($info = mysql_fetch_array($result)) {
      print (" <a href=review.php?id=$info[0]>$info[1] ($info[2])</a><br /><br />");
   }
} else {
   print "There are no reviews in the database Starting with $severedid <br /><img src=images/spacer.gif border=0 width=1 height=600>";
}
?>
[/code]

Share this post


Link to post
Share on other sites
[!--quoteo(post=384161:date=Jun 15 2006, 09:17 AM:name=Crayon Violent)--][div class=\'quotetop\']QUOTE(Crayon Violent @ Jun 15 2006, 09:17 AM) [snapback]384161[/snapback][/div][div class=\'quotemain\'][!--quotec--]
try changing your code to:
[code]
<?
$sql = "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 review 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 'A%'
         ORDER BY title1";
$result = mysql_query($sql) or die(mysql_error());

$row = mysql_num_rows($result);

if ($row > 0) {
   while ($info = mysql_fetch_array($result)) {
      print (" <a href=review.php?id=$info[0]>$info[1] ($info[2])</a><br /><br />");
   }
} else {
   print "There are no reviews in the database Starting with $severedid <br /><img src=images/spacer.gif border=0 width=1 height=600>";
}
?>
[/code]
[/quote]

I did that, and I get output now :-) But a couple problems.

1) The date is not shown on the output. (Which is $info[2] in your code. ) All that is shown is Title (). (I would assume that is because we selected title, not *, I'll change that and test it.)
2) The, and A are not displayed anymore in the final output. It removed it entirely, while the code before in phpmyadmin outputed The, and A (What I would like).

Thanks for everyones help so far though! I do appreciate it!

Share this post


Link to post
Share on other sites
[!--quoteo(post=384172:date=Jun 15 2006, 09:46 AM:name=RichE)--][div class=\'quotetop\']QUOTE(RichE @ Jun 15 2006, 09:46 AM) [snapback]384172[/snapback][/div][div class=\'quotemain\'][!--quotec--]
I did that, and I get output now :-) But a couple problems.

1) The date is not shown on the output. (Which is $info[2] in your code. ) All that is shown is Title (). (I would assume that is because we selected title, not *, I'll change that and test it.)
2) The, and A are not displayed anymore in the final output. It removed it entirely, while the code before in phpmyadmin outputed The, and A (What I would like).

Thanks for everyones help so far though! I do appreciate it!
[/quote]


Alright, I changed title, to * and it fixed both problems I was having!!!!

Everything works fine NOW!!

Thanks a lot!

At the risk of overstaying my welcome, is there a way to sort by more then 1 letter. I found out through my searching that [A-I]% should work, but it doesn't for me...

Share this post


Link to post
Share on other sites
i'm sure there is. but i'm a noob when it comes to query strings. barand knows all when it comes to query strings. he'll be able to do it. you could pm him and ask him to check this thread out (don't ask for help inside the email).

Share this post


Link to post
Share on other sites
Try this. The forum wouldn't let me post it

[a href=\"http://members.aol.com/barryaandrew/response.txt\" target=\"_blank\"]My sample code[/a]

Share this post


Link to post
Share on other sites
[!--quoteo(post=384407:date=Jun 15 2006, 07:45 PM:name=Barand)--][div class=\'quotetop\']QUOTE(Barand @ Jun 15 2006, 07:45 PM) [snapback]384407[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Try this. The forum wouldn't let me post it

[a href=\"http://members.aol.com/barryaandrew/response.txt\" target=\"_blank\"]My sample code[/a]
[/quote]


I'm not too sure what this line is supposed to do:
[code]echo date('jS F Y', strtotime($date)), '<br>', $title, '<br><br>';[/code]

But it was outputing the wrong dates. Mostly June 16, 2006. But occationally had the right date. Weird. I changed it to this:

[code]echo $date, '<br>', $title, '<br><br>';[/code]

And it outputs the right date now. Is that OK, or did I just mess up something I didn't notice yet?
But wow that looks complicated. But I think I have an idea of what it is doing. It basically does exactly what I wanted!

I do need a field called primary_id in my reviews table that I am going to be using to link to my reviews.
Like this:

[code]echo '<a href=review.php?id=', $pri_id, '>',$title, ' (', $date, ')', '</a><br>';[/code]

I've tried calling it initially with the title, and release date, but when I do instead of getting the header letters. (A, B, C) I get random letters and numbers... Is there another way to get the primary_id

FYI I have an auto incrementing primary_id for every review to make it easyer to call them in review.php?id=1 - like that.


Thanks a lot for your help!

Share this post


Link to post
Share on other sites
I think I get why your date output line did not work with my database. It is probably because I do not have full dates for every review. (Older reviews only gave the year, not day, month, year. --that would explain why some worked, and others did not)

I'm still having trouble trying to get my primary_id field called from my database to use in outputting the reviews (so I can link to the actual reviews) Everything I try seems to cause the header letters (A, B, C, etc.) to output mostly numbers, but the occational letter at random). [img src=\"style_emoticons/[#EMO_DIR#]/excl.gif\" style=\"vertical-align:middle\" emoid=\":excl:\" border=\"0\" alt=\"excl.gif\" /]

Share this post


Link to post
Share on other sites
I got it! Now my php coding is all done. Thanks to everyone that helped me out. Especially Barand who basically wrote my code. :-) All the examples here really helped me out, and I can't believe everyone is willing to help people as much as they do. Thanks a lot!

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.