Alphabetical Pagination Using Arrays

[Sp4rkyM4rk]

I'm having a little trouble making my pagination script user friendly. I have an array of characters like so:

$character_set = array('#','A','B','C','D','E','F','G','H','I','J','K','L','M','N','O','P','Q','R','S','T','U','V','W','X','Y','Z');

Basically, I want to query the database using the array to check which strings in a field begin with which letters. If a string does not begin with a letter, I still want to display the letter on the page but remove the hyperlink. I also want to remove the hyperlink and make the text bold, whenever the user is on a letter which does have records beginning with it. I have tried using a foreach() loop in combination with LIKE in SQL, to get all array values, but no luck. This is my loop and query:

foreach ($character_set as $letters)
{
$query = mysql_query('SELECT film_title FROM ' . REVIEW_ARTICLE_TABLE . ' WHERE film_title LIKE "' . $letters . '%"');
$rows = mysql_fetch_assoc($query);

$film_title = $rows['film_title'];

// I have a feeling this is wrong

if (in_array($film_title['0'],$character_set))
{

echo '<span class="link"><a href="SITEURLHERE/select.php?category=0&amp;letter=' . strtolower($letters) . '">' . $letters . '</a></span>';

}
else
{

echo '<span class="plaintext">' . $letters . '</span>';

}
}

I can see where the issue is - the entire array is being echo'd out rather than separate values, but I'd rather be simple as possible and not have to edit the script at all, whenever I add a record which begins with a new letter.

Thanks in advance. :)

[httpwebwitch]

first, do the numbers. you'll need a REGEXP query for that.

SELECT count(*) > 0 as num FROM TABLE WHERE COLUMN REGEXP '^[0-9]';

if the result is 1, then wrap a link around the "#" symbol. if not, then show no link.

then inside a loop from A to Z, construct this query:

select count(*) > 0 as a from TABLE where COLUMN like 'a%';

if there are more than 0 films in your database that start with "A", then the query will return a result named "a" with the value "1". If there are no films, then the query returns "0". Loop through the alphabet, do this query with each letter, and use the result to show or not show the hyperlink.

If a letter is to be linked, you wrap it in a <a>. If not, then wrap it in a <span>.

now as you're looping through the alphabet, check if the current letter is the same as the first letter of the film being shown on the page. if it is, style the label with a class, like "current". Use CSS to style this in a way that makes it apparent which letter is the current one.

What you should end up with is markup that looks like this:

<div id='alpha'>
<a href="url?l=#">#</a>
<a href="url?l=a">A</a>
<span>B</span>
<a href="url?l=c">C</a>
<a href="url?l=d" class="current">D</a>
<a href="url?l=e">E</a>
...
</div>

^^ where there are no films starting with "B", and the current film starts with "D".

This is heavy work for a SQL database on each page load; if your dataset is small and traffic is light, it'll be OK. You should consider caching the results of all those queries, but that's a different topic

g'luck