I have been working pretty heavily with Coldfusion for the past year and a half, but haven't spent a lot of time with PHP. I am working on a php site right now and would like to create an alphabetical index (IE A | B | C
D | ... Z at the top of the page) so that the user can click on a letter and be shown all of the records that start with that letter for a certain field. IE, If I clicked on "A", I would get a listing of all of the records where the company name started with the letter "A". I know I could do this by running a query each time the user clicks a letter, but is there a better/more efficient way? In Coldfusion, I might be able to store the query results for all records in a session variable and sort through or group those somehow depending on the letter clicked, but have no idea how to go about that with PHP. Im not even sure if that would be more efficient than running multiple queries or not.

Also, say I don't have any records where "company_name" starts with "D". In that case, I would not want to link the letter "D". I know how to create a loop that will put a link around a letter or not depending on a certain condition, but how can I determine if each letter has records?

Does any of this make sense? I am really bad at describing things.
I appreciate any help!

1. Choose multiple queries if records are too many for a single page ( say more than 500) all letters included.
2. Use anchor elements <A name="a"> and <A name="backToTop"> combinations to print all records in a single page and then allow the user to jump to index and specific records.
3. An example to create a letter loop

        echo "<A name="backToTop">";
    echo "| ";
    for ($letterCounter=65;$letterCounter<91;$letterCounter++)   
    {
        $l=chr($letterCounter);
    if (getRecordCount($letter))
    {
            echo '<A href="#'.$l.'"> |';
    }
    else
    {
        echo $l." |";
    }

    }
    
  
    for ($letterCounter=65;$letterCounter<91;$letterCounter++)   
    {
        $l=chr($letterCounter);
    if (getRecordCount($letter))
    {
            echo '<A name="'.$l.'"><br>';echo '<A href="#backToTop"> top</a>';

        //dummy records printer...Change this with your records.
            for ($i=0;$i<30;$i++)
             {
                     echo "This is a dummy record.<br>";
              }
    }


    }



function getRecordCount($letter)
{
    //change SQL to your tblName and fieldName.
    $sql="select count(*) as rowCount from `tblName` where `fieldName` like '$letter%'";
    $result=mysql_query($sql);
    if($row=mysql_fetch_assoc($result))
    {
        return $row['rowCount'];
    }
    else
    {
        return 0;
    }
}

Thanks for your help Amit.

There will be too many to list on a single page. I would like to display only the records that have a company name beginning with the letter the user clicks on. I guess I will just set up a query with a "where" statement that changes based on a variable passed thorugh the URL. I just didn't know if there was a better way to do that so I don't run a query every time a user picks a different letter.

Also, I still am not sure how to check to make sure there is a company name beginning with each letter. I don't want to link letters that will not show any results. I could just set it up to say something like "no records found" if the user clicked on a letter with no matches, but I would rather just not make that letter a hot link in the first place. KWIM?

Hi,

Actually, that wont happen, if you notice the code I posted earlier. Those letters that do not have any records wont appears as links, so they wont be clickable ! KWIM

 else
    {
        echo $l." |";
    }

delete the above lines from the original code to not print them(letters with 0 records) at all.

I recently did a similar function, and heres what I used to pull the letters.

SELECT COUNT(title) as count, LEFT(title,1) as letter FROM entries GROUP BY letter ORDER BY title ASC

(Where in you case, title would be company_name and entries companies or similar.)

With this, all entries will be ordered correctly (A-Z) and you'll get the first letter and the number of times it occured, and you won't get the letters which no entry begins with.

Then I chose to return an array with all the letters as keys and the count as value, as in

PHP Code:

array(
   'A' => 3,
   'B' => 1,
   'F' => 7,
   // and so on
); 


So in your loop, you can easly check if there are any entries for a certain letter by checking if that letter is in the array with the key_array_exists() function. Plus, you can get the number of entries for that letter by using the letter as index for the array, as in $array[$letter].

In my case I also specially handled entries starting with a digit, and added them all to a "letter" called '0-9', giving me

PHP Code:

array(
   '0-9' => 3,
   'A' => 3,
   'B' => 1,
   'F' => 7,
   // and so on
); 


Hope this helps!

@amitsoni - I looked at your code again and it makes more sense to me today. Maybe my mind just needed a weekend away from code before it could comprehend any thing else new! lol! Thanks!

@lizciz - Thank you for your answer. I wasn't aware of the key_array_exists() function or the COUNT and LEFT functions in the query. This will definitely help!!