MySQL/PHP Paging by Alphabetical Letter Help Please!

[xjulzkx]

Ok.. I'm having a paging issue with displaying my data, maybe someone can help me..

 

First of all, I have two PHP Queries, one script will display my results via a specified letter, but then I'd have to setup a .php file for every letter of the alphabet to display them. A, B, C...Z etc... This script is:

 

<?php

include 'dbconfig.php';

include 'dbopen.php';

 

$data = mysql_query("

 

SELECT *,

CASE WHEN SUBSTRING_INDEX(artist, ' ', 1)

IN ('a', 'an', 'the')

THEN CONCAT(

SUBSTRING(artist, INSTR(artist, ' ') + 1),

', ',

SUBSTRING_INDEX(artist, ' ', 1)

)

ELSE artist

END AS TitleSort

FROM data

WHERE

CASE WHEN SUBSTRING_INDEX(artist, ' ', 1)

IN ('a', 'an', 'the')

THEN CONCAT(

SUBSTRING(artist, INSTR(artist, ' ') + 1),

', ',

SUBSTRING_INDEX(artist, ' ', 1)

)

ELSE artist

END LIKE 'b%'

ORDER BY TitleSort")

 

or die(mysql_error());

 

while($info = mysql_fetch_array( $data ))

{

Print " ".$info['type'] ." -";

Print " ".$info['cat'] ." -";

Print " ".$info['artist'] ." -";

Print " ".$info['title'] ." -";

Print " ".$info['label'] ." -";

Print " ".$info['year'] ." -";

Print " ".$info['country'] ." -";

Print " ".$info['qr'] ." -";

Print " ".$info['qc'] ." -";

Print " ".$info['qb'] ." -";

Print " ".$info['comments'] ." -";

Print " ".$info['price'] . " <br>";

}

Print "</table>";

 

include 'dbclose.php';

?>

 

 

Next, I have another script that makes it so that I have to use it like www.blah.com/script?letter=B

 

That's all good, because it will let me specify a letter from the one script and load all the data, problem is, my first script specifies my data so that all the artists starting with the word "THE" are ignored. For example the word "The Beatles" would come up until the letter T in the second script I posted below. Where as the first script above ignores the "A ", "AN ", and "THE " words within the artist field. Can someone help please? I am trying to combine the features of the second script so I can display only results via script.php?letter=B function but with the words A, AN and THE words but out of the artist name, so for example, "The Beatles" would come in under the letter B and not T.

 

 

<?php

include 'dbconfig.php';

include 'dbopen.php';

 

$starting_letter = $_GET['letter'];

 

$data = mysql_query("

 

SELECT *,

CASE WHEN SUBSTRING_INDEX(artist, ' ', 1)

IN ('a', 'an', 'the')

THEN CONCAT(

SUBSTRING(artist, INSTR(artist, ' ') + 1),

', ',

SUBSTRING_INDEX(artist, ' ', 1)

)

ELSE artist

END AS TitleSort

FROM data

WHERE LEFT(artist, 1) = '$starting_letter'

ORDER BY TitleSort")

 

or die(mysql_error());

 

while($info = mysql_fetch_array( $data ))

{

Print " ".$info['type'] ." -";

Print " ".$info['cat'] ." -";

Print " ".$info['artist'] ." -";

Print " ".$info['title'] ." -";

Print " ".$info['label'] ." -";

Print " ".$info['year'] ." -";

Print " ".$info['country'] ." -";

Print " ".$info['qr'] ." -";

Print " ".$info['qc'] ." -";

Print " ".$info['qb'] ." -";

Print " ".$info['comments'] ." -";

Print " ".$info['price'] . " <br>";

}

 

include 'dbclose.php';

 

?>