Welcome to WebmasterWorld Guest from 54.197.94.141

Forum Moderators: coopster & jatar k

Message Too Old, No Replies

mysql alphabetical pagination

letters work but what about number and symbols

   
9:31 pm on Dec 28, 2007 (gmt 0)

5+ Year Member



Hi,
I've got a database full of songs for people to request on my site. I've got alphabetical pagination working fine but I also want a page for artists that start with a number or a symbol instead of a letter.

Here's the code...


if (!isset($_GET['letter'])) {$letter = "A";} else {$letter = $_GET['letter'];}
echo '<div align="center"><b>';
for ($i=65; $i<90; $i++) {
if ($letter!= chr($i)) {echo '<a href="index.php?letter='.chr($i).'">';}
echo chr($i)."&nbsp;";
if ($letter!= chr($i)) {echo '</a>';}
}
echo "</b></div><p>";

$query="SELECT * FROM songs WHERE artist LIKE '".$letter."%'";

So I just want to add one link before A B C.... for everything that isn't a letter

Thanks in advance :)

8:50 pm on Dec 29, 2007 (gmt 0)

5+ Year Member



Welcome to WebmasterWorld antriver.

You can use either the MySQL function ASCII or SUBSTRING to do this. So to fetch all artists whose name doesn't start with an alpha character:

$query="SELECT * FROM songs WHERE ASCII(UPPER(artist)) NOT BETWEEN ASCII('A') and ASCII('Z')";

or

$query="SELECT * FROM songs WHERE UPPER(SUBSTRING(artist,1,1)) NOT BETWEEN 'A' and 'Z'";

Hope this helps.

7:55 pm on Dec 31, 2007 (gmt 0)

5+ Year Member



thanks, that works great :)