Hello and welcome to our community! Is this your first visit?
Register
Enjoy an ad free experience by logging in. Not a member yet? Register.
Results 1 to 3 of 3
  1. #1
    New Coder
    Join Date
    Jul 2011
    Location
    Sunshine State
    Posts
    80
    Thanks
    18
    Thanked 0 Times in 0 Posts

    A-Z links based on query

    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 '<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?

  • #2
    Senior Coder kbluhm's Avatar
    Join Date
    Apr 2007
    Location
    Philadelphia, PA, USA
    Posts
    1,509
    Thanks
    3
    Thanked 258 Times in 254 Posts
    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.
    Last edited by kbluhm; 02-01-2013 at 09:33 PM.

  • Users who have thanked kbluhm for this post:

    dnnhater (02-01-2013)

  • #3
    New Coder
    Join Date
    Jul 2011
    Location
    Sunshine State
    Posts
    80
    Thanks
    18
    Thanked 0 Times in 0 Posts
    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!


  •  

    Posting Permissions

    • You may not post new threads
    • You may not post replies
    • You may not post attachments
    • You may not edit your posts
    •