I have a list of companies in a MySql table and I'm trying to query the table and display the results alphabetically with a header for each letter of the alphabet. I also need the results displayed in a table with two columns per row.

For example
A
Alpha Company Albatrose Company
Another Company Another Company2

B
Big Company Brother Company
Ball Company Better Company

ect... all through the alphabet.

The query I have now is :

$query = "SELECT vendor_id, name FROM shop_vendor ORDER by name";

Thank you for your help.

    <?php
$alpha = range('a','z');
foreach($alpha as $letter) {
    $query = "SELECT vendor_id, name FROM shop_vendor WHERE name LIKE \"$letter%\" ORDER by name";
    //code to actually execute, retrieve and print the data here
} //end foreach
?>

      drawmack

      Thank you for your reply. It's starting to make sense. I'm a little new at PHP though so I need some more help concerning the code to execute, retrieve and print the data. Do I use the query for each letter of the alphabet, or is the query done once and the results are looped through? If you could give some sample code that would be very helpful.

      Thank you!!

        another approach which saves you from quite a few database queries: keep with your query, and keep track of the first letter. if it changes, display it, else don't.

        hope that makes some sense to you. I'll happily explain it a bit more detailed if necessary, but won't be back on phpbuilder until tomorrow.

        bye,
        xblue

          xblue - please explain....

          what I have is a list of vendors. Not every letter of the alphabet has a vender associated with it. My query works pretty good for me, the main problem I have is displaying the results. I need to need to check the Query to see if a vendor name begins with a certain letter (i.e. 'A'), print those vendors in a table with two columns, and then move onto the next letter and do the same thing. If a letter doesn't have a vendor name associated with it, the script needs to skip that letter and move on to the next.

          Thank you!!

            okay, some simple example.

            $query = "SELECT vendor_id, name FROM shop_vendor ORDER by name";
// assuming mysql as database
$result = mysql_query($query) or die(mysql_error());

// variable to store first letter
$letter = '';

// fetch rows one by one
while ($row=mysql_fetch_assoc($result)){
    // only if first letter is different from stored letter
    if (strtoupper(substr($row['name'],0,1)) != $letter) {
        // store new first letter
        $letter = strtoupper(substr($row['name'],0,1));
        // and print it
        echo '<b>'.$letter.'</b><br />';
    }
    // then print the name
    echo $row['name'].'<br />';
}
unset($letter);

            good luck, and watch out for typos!

              10 days later

              xblue

              Thats it!! It works. You can see the page at http://www.woodturns.com/shop/company/

              I added a little bit extra to the code. First I put a another while loop in front of yours to capture all of the letters in the alphabet used.

              //RETRIEVE ALL OF THE LETTERS OF THE ALPHABET AND STORE THEM
	//IN VARIABLE TO BE USED AS A LINK TO AN ANCHOR WITH THAT LETTER

//VARIABLE TO STORE FIRST LETTER
$letter_first = '';
while ($row=mysql_fetch_assoc($result)){ 
// ONLY IF FIRST LETTER IS DIFFRENT FROM STORED LETTER
if (strtoupper(substr($row['name'],0,1)) != $letter_first) { 
     // STORE NEW FIRST LETTER 
    $letter_first = strtoupper(substr($row['name'],0,1)); 
	$letter_all .= '<a href="#'.$letter_first.'">'.$letter_first.'</a> | ';
	}
 }
mysql_data_seek($result, 0);

              I then put in a simple echo statement: echo $letter_all; and a little bit of HTML formating in front of echo '<b>'.$letter.'</b><br />';

              Thank you!!

                Write a Reply...