Sorting Alpha but limiting??

[Trainacomin]

Hi all,

I'm trying to break out a list of songs by title and I want them broken into sections by the first letter of the song name.
I can get them sorted properly but can't figure out to limit it to just the A's, unless I add another field to create a sort order. Since my songs are all already in there I'd prefer to find a way to do with code.

Any ideas?

thanks!
Clint

[YvetteKuhns]

Are your song titles in a text file or database? You can use a php script to read the first letter in a song title.

SELECT * FROM table WHERE LEFT(songtitle,1) = 'A'

This would show all song titles in a database table that start with the letter A.

[Trainacomin]

Thanks! That worked great!

BTW I'm pulling from a DB.

[YvetteKuhns]

Glad it worked, Clint.

[satis]

Any functional difference between

WHERE LEFT(songtitle,1) = 'A'

and

WHERE songtitle like 'A%'

?

[YvetteKuhns]

No. We naturally think of starting from the left and reading left-to-right in the English language, but that is not correct for other languages or purposes. It was simply for clarification.

[satis]

That's fine, I was just curious. It tickled me to see someone use the LEFT function and I was just curious if it had some utility beyond what I was aware of. Thanks for the clarification.

[YvetteKuhns]

satis, I have allergies right now and sometimes my brain will go back in time. I even laughed after I posted that method. One of my high school computer teachers was very fussy about efficient yet understandable code.

[satis]

lol, no worries. sometimes I like using weird and little used functions in code just to confuse people. :p then again, half my job depends on me building and maintaining code, so writing weird, obscure crap is in my benefit. :-D

[Trainacomin]

Is there any way to make 26 sections with each one containing the returns from each letter of the Alphabet (Actually would need 27 one for misc characters)
from one query? Or would I need a separate query for each one?

[Doc C]

What about numeric song titles? Like 88 Lines About 44 Women, 867-5309, 99 Luftballons and similar ones?

[YvetteKuhns]

You can assign a value for each letter or number to appear in the order you desire and compare with the first (then second and so on) character in the song title. The default alphanumeric order places numbers before letters, but 10 starts with 1 and appears before 9 which may be what you want in this case but not for counting.

[Trainacomin]

What about numeric song titles? Like 88 Lines About 44 Women, 867-5309, 99 Luftballons and similar ones?


That is one of the reasons for the misc section. I have some that begin with (

[YvetteKuhns]

If a song begins with "(", then you should be looking for the character after ")" for the actual song title.

[entrecon]

if you want to do it all with one database call you would have to have the logic in your display loop to check for the initial character and do a seperation/break when the character changes.

[satis]

yea, I wouldn't do that logic in the query. I'd just return all rows and loops through it to do the logic in code. Either that or do a buttload of small queries, but that's definitely a bad idea. I'd rather do one large one.

[Trainacomin]

Can someone give me a small example? Im usually really good at finding how to do things on the net but everything I find on sorting an array I can do with my one query using SORT.
What I want to do is list my songs from a-z but put a space or maybe a letter in between each section. The only way I've found so far is just one long list.

I want
A
A song title
Another song title
B
Bsong title
Banother one that starts with B
C
etc..

[entrecon]

The code below has not been validated AT ALL and it has been a while since I have had to code anything so I am just taking a stab at it.

$currentletter = NULL
If ($songtitle[0] != $currentletter) {
$currentletter = $songtitle[0];
echo $songtitle[0] "<BR>"$songtitle"<br>";
}else{
echo $songtitle"<br>";
}


If the above code works you might want to add an additional check in for non-letter characters unless you want each special character to have its own section heading.

[YvetteKuhns]

SELECT * FROM table WHERE (songtitle not null or whatever your logic is) ORDER BY placement ASC;

I didn't test this. I am just explaining logic. My son just came home and I can't type and talk to him at the same time.

[entrecon]

Sorry, I was assuming the SELECT and ORDER part already existed. The code I provide was meant to be nested into an existing display loop so that you can seperate the list.

[Doc C]

Get out the spoon, Yvette! :D