A-Z links based on query

[dnnhater]

Hi all -
I would like to generate a list of links from a-z based on the records returned from a query (for example, records are returned for A, B and F so they have links, C, D and E do not have a record and are not linked: <a href="#">A</a> <a href="#">B</a> C D E <a href="#">F</a>)

I have tried using:

PHP Code:

    public function getCarrierByAlphabet() {
        $alphabet = range('A', 'Z');
        foreach ($alphabet AS $alpha) {
            $getCarrierByAlphabetQuery = mysql_query("SELECT carrierName FROM carriers WHERE carrierName LIKE '$alpha%' ORDER BY carrierName") or die ('Get Carrier By Alphabet Query Error --- MySQL Error No: '.mysql_errno().' --- '.mysql_error());
            $alphaCount = mysql_num_rows($getCarrierByAlphabetQuery);
            $alphaLinks = ($alphaCount > 0 ? '<a href="'.WEBSITE_URL.'/viewCarriers.php?alpha='.$alpha.'">'.$alpha.'</a>' : $alpha);
            echo $alphaLinks;
        }
        return $alphaLinks;
    } 


this list that echos displays and works, but the values returned displays only Z

am I supposed to have a foreach on the page this is displaying on?

[kbluhm]

You can do that with one query as opposed to a query within the foreach loop:

PHP Code:

    public function getCarrierByAlphabet()
    {

        // only run once per request
        static $chars;
    
        if ( ! isset( $chars ) )
        {
    
            // All characters we want displayed, A-Z
            $chars = range( 'A', 'Z' );
    
            // Query for existing carrier names
            // - Grab only the first character
            // - Uppercase it
            // - Select distinct so we have at most 26 results (assuming all letters)
            //   regardless of the number of carriers in the database
            $res = mysql_query( '
                SELECT DISTINCT UPPER( LEFT( `carrierName`, 1 ) ) AS `alpha`
                FROM `carriers`
            ' );
    
            // Build an array of all *existing* characters
            $alphas = array();
        
            while ( $row = mysql_fetch_assoc( $res ) )
            {
                $alphas[ $row['alpha'] ] = $row['alpha'];
            }
    
            // Insert links to existing characters
            foreach ( $chars as & $char )
            {
    
                if ( isset( $alphas[ $char ] ) )
                {
                    $char = '<a href="' . WEBSITE_URL . '/viewCarriers.php?alpha=' . $char . '">' . $char . '</a>';
                }
                
            }

            // Convert the $chars array to a string using new lines
            $chars = implode( "\n", $chars );

        }

        return $chars;

    } 


Usage:

PHP Code:

echo $obj->getCarrierByAlphabet(); 


You'll notice I'm returning the HTML and echoing it, as opposed to echoing from within the method itself. Small standard preference that will allow you to be more flexible with your output.

[dnnhater]

that worked quite loverly - wound up tweaking it just a wee bit more because I decided there was no point to the links pointing a different page (been a couple of months since I've worked on this and have no idea what I was thinking about)

thanks for the help!