Jump to content


Photo

Sorting Movie Titles, but ignoring "The"


  • Please log in to reply
14 replies to this topic

#1 RichE

RichE
  • Members
  • PipPip
  • Member
  • 20 posts

Posted 14 June 2006 - 06:12 PM

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

#2 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 14 June 2006 - 11:01 PM

[!--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)='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 '$letter%'
ORDER BY title1 [!--sql2--][/div][!--sql3--]
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#3 RichE

RichE
  • Members
  • PipPip
  • Member
  • 20 posts

Posted 15 June 2006 - 01:38 AM

[!--quoteo(post=384011:date=Jun 14 2006, 07:01 PM:name=Barand)--][div class=\'quotetop\']QUOTE(Barand @ Jun 14 2006, 07:01 PM) View Post[/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)='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 '$letter%'
ORDER BY 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\" /]

#4 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 15 June 2006 - 06:34 AM

Maybe it's because I used '$letter%' when I tested, you have '$id%'
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#5 RichE

RichE
  • Members
  • PipPip
  • Member
  • 20 posts

Posted 15 June 2006 - 12:02 PM

[!--quoteo(post=384084:date=Jun 15 2006, 02:34 AM:name=Barand)--][div class=\'quotetop\']QUOTE(Barand @ Jun 15 2006, 02:34 AM) View Post[/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--]<?
if ($row = mysql_fetch_row($severedreview)) {

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

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

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

#6 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 15 June 2006 - 12:35 PM

Use it as posted

$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";

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#7 RichE

RichE
  • Members
  • PipPip
  • Member
  • 20 posts

Posted 15 June 2006 - 01:08 PM

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?

<?
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>";}
?>


#8 .josh

.josh
  • Staff Alumni
  • .josh
  • 14,871 posts

Posted 15 June 2006 - 01:17 PM

try changing your code to:
<?
$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>";
}
?>

Did I help you? Feeling generous? Buy me lunch! 
Please, take the time and do some research and find out how much it would have cost you to get your help from a decent paid-for source. A "roll-of-the-dice" freelancer will charge you $5-$15/hr. A decent entry level freelancer will charge you around $15-30/hr. A professional will charge you anywhere from $50-$100/hr. An agency will charge anywhere from $100-$250/hr. Think about all this when soliciting for help here. Think about how much money you are making from the work you are asking for help on. No, we do not expect you to pay for the help given here, but donating a few bucks is a fraction of the cost of what you would have paid, shows your appreciation, helps motivate people to keep offering help without the pricetag, and helps make this a higher quality free-help community :)

#9 RichE

RichE
  • Members
  • PipPip
  • Member
  • 20 posts

Posted 15 June 2006 - 01:46 PM

[!--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) View Post[/div][div class=\'quotemain\'][!--quotec--]
try changing your code to:
<?
$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>";
}
?>
[/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!

#10 RichE

RichE
  • Members
  • PipPip
  • Member
  • 20 posts

Posted 15 June 2006 - 03:12 PM

[!--quoteo(post=384172:date=Jun 15 2006, 09:46 AM:name=RichE)--][div class=\'quotetop\']QUOTE(RichE @ Jun 15 2006, 09:46 AM) View Post[/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...

#11 .josh

.josh
  • Staff Alumni
  • .josh
  • 14,871 posts

Posted 15 June 2006 - 03:48 PM

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).
Did I help you? Feeling generous? Buy me lunch! 
Please, take the time and do some research and find out how much it would have cost you to get your help from a decent paid-for source. A "roll-of-the-dice" freelancer will charge you $5-$15/hr. A decent entry level freelancer will charge you around $15-30/hr. A professional will charge you anywhere from $50-$100/hr. An agency will charge anywhere from $100-$250/hr. Think about all this when soliciting for help here. Think about how much money you are making from the work you are asking for help on. No, we do not expect you to pay for the help given here, but donating a few bucks is a fraction of the cost of what you would have paid, shows your appreciation, helps motivate people to keep offering help without the pricetag, and helps make this a higher quality free-help community :)

#12 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 15 June 2006 - 11:45 PM

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]
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#13 RichE

RichE
  • Members
  • PipPip
  • Member
  • 20 posts

Posted 16 June 2006 - 01:39 AM

[!--quoteo(post=384407:date=Jun 15 2006, 07:45 PM:name=Barand)--][div class=\'quotetop\']QUOTE(Barand @ Jun 15 2006, 07:45 PM) View Post[/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:
echo date('jS F Y', strtotime($date)), '<br>', $title, '<br><br>';

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

echo $date, '<br>', $title, '<br><br>';

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:

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

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!

#14 RichE

RichE
  • Members
  • PipPip
  • Member
  • 20 posts

Posted 16 June 2006 - 03:26 PM

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\" /]

#15 RichE

RichE
  • Members
  • PipPip
  • Member
  • 20 posts

Posted 16 June 2006 - 11:18 PM

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!




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users