Too many mysql_queries in php script

... page loading takes way too long ...

[foy]

So I have an array which has our alphabet:

$array_letters = 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");

I have a mysql database with articles in it and I need an "alphabet index" which outputs me the results according to its starting letter.

Something like this:

<?php
$i = 0;
while($i < 26) {
$letter = $array_letters[$i];
if(mysql_num_rows(mysql_query("SELECT description FROM articles WHERE description LIKE '$letter%'"))!= 0) {?>
 <a href="letter.php?letter=<?= $letter;?>"><?= $letter?></a>    
<? } $i++;
} 
?>

It works fine and only gives me the links to alphabet letters who really have results in the database. However it takes far too long for each array index to be queried, I suppose that's because there are 26 mysql queries to be made before the output starts.

Is there a faster way to do this? Since I have around 100.000 articles in the database the mysql_select checks all of them....

[coopster]

There are a number of ways to do this, but I usually use the DISTINCT keyword when building lists such as this. One option would be SUBSTRING, or you could use a regular expression. Here is an example using SUBSTRING.

$array_letters = 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" 
); 
$comma_separated = "'" . implode("','", $array_letters) . "'"; 
$query = "SELECT 
 DISTINCT(SUBSTRING(description , 1, 1)) AS alpha 
 FROM articles 
 WHERE SUBSTRING(description , 1, 1) IN($comma_separated) 
 ORDER BY alpha" 
;

[httpwebwitch]

tangent: did you know that as of PHP4.3.2, you can populate an array of letters with range()?

$array_letters = range("a","z");

it produces the same array as
$array_letters = 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");

[coopster]

Good point, httpwebwitch, thanks for mentioning that.

[charlier]

Do you have an index on your description field, a one column index should make your queries much faster.

From section 6.5.7 CREATE INDEX Syntax

CREATE INDEX part_of_name ON customer (name(10));