Thread: please help getting content from db to display in vertical 'columns' with in a div!

Hi All,

im really stuck, i have a menu which when you hover over the menu items a drop down box appears, which is great, and im in the process of adding data to these drop downs, 'discount codes' to be specific, you will notice when you hover over discount codes a list of stores appear grouped by letter.

now the trouble im having is that i want the stores to appear as they are 'one under another' but instead of continuing outside of the div create a new 'column' inside and carry on so it woul look something like this

a f k p u
b g l q v
c h m r w
d i n s x
e j o t y
z

and not
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

any ideas how this can be acheived pref just using css

many thanks

ok i've kinda solved it but it means doing a lot more work than necessary, i have copied the same piece of coe several imes to show just a certain number of stores like so

PHP Code:

    <li class="menu_right"><a href="#" class="drop">Discount Codes</a><!-- Begin 3 columns Item -->
    
    <div class="dropdown_3columns align_right"><!-- Begin 3 columns container -->
    <?php
    mysql_query("SET NAMES 'utf8'");

    $query = mysql_query("SELECT UPPER(SUBSTRING(stores.storeName,1,1)) 
    AS letter, storeID, storeName, rating 
    FROM stores ORDER BY storename ASC")or die(mysql_error());

    while ($records = @mysql_fetch_array ($query))
    {
        $alpha[$records['letter']] = !isset($alpha[$records['letter']]) ? 1 : $alpha[$records['letter']] + 1;
        ${$records['letter']}[$records['storeID']] = $records['storeName'];
    }
        
        
    echo "<ul>";
    
    // Create Alpha link Listing;
    foreach(range('A','E') as $i)
    {
        if (array_key_exists ("$i", $alpha))
        {
            $count = 1;
            echo "<li>";
            echo "<b>".$i."</b><br />";
            foreach ($$i as $key=>$value)
            {
                $stores = "SELECT *
                FROM stores 
                WHERE storeID = '$key'";
                $query  = mysql_query($stores);?>
                <?php
                while ($row = mysql_fetch_array($query))
                {
                    $name = $row['name'];
                    $storename = trim(htmlspecialchars($row['storeName']));
                    $ids = $row['storeID'];
                    $link = htmlspecialchars($row['storeLink']);?>
                    <a href='<?php echo $link?>'><?php echo $storename.", ";?></a><?php
                    $count++;
                }
            }
                echo "</li>";
        }
        #echo "</ul>";
    }
    echo "</ul>";
    
    echo "<ul>";
    
    // Create Alpha link Listing;
    foreach(range('F','L') as $i)
    {
        if (array_key_exists ("$i", $alpha))
        {
            $count = 1;
            echo "<li>";
            echo "<b>".$i."</b><br />";
            foreach ($$i as $key=>$value)
            {
                $stores = "SELECT *
                FROM stores 
                WHERE storeID = '$key'";
                $query  = mysql_query($stores);?>
                <?php
                while ($row = mysql_fetch_array($query))
                {
                    $name = $row['name'];
                    $storename = trim(htmlspecialchars($row['storeName']));
                    $ids = $row['storeID'];
                    $link = htmlspecialchars($row['storeLink']);?>
                    <a href='<?php echo $link?>'><?php echo $storename.", ";?></a><?php
                    $count++;
                }
            }
                echo "</li>";
        }
        #echo "</ul>";
    }
    echo "</ul>";

    echo "<ul>";
    
    // Create Alpha link Listing;
    foreach(range('M','R') as $i)
    {
        if (array_key_exists ("$i", $alpha))
        {
            $count = 1;
            echo "<li>";
            echo "<b>".$i."</b><br />";
            foreach ($$i as $key=>$value)
            {
                $stores = "SELECT *
                FROM stores 
                WHERE storeID = '$key'";
                $query  = mysql_query($stores);?>
                <?php
                while ($row = mysql_fetch_array($query))
                {
                    $name = $row['name'];
                    $storename = trim(htmlspecialchars($row['storeName']));
                    $ids = $row['storeID'];
                    $link = htmlspecialchars($row['storeLink']);?>
                    <a href='<?php echo $link?>'><?php echo $storename.", ";?></a><?php
                    $count++;
                }
            }
                echo "</li>";
        }
        #echo "</ul>";
    }
    echo "</ul>";

    echo "<ul>";
    
    // Create Alpha link Listing;
    foreach(range('S','Z') as $i)
    {
        if (array_key_exists ("$i", $alpha))
        {
            $count = 1;
            echo "<li>";
            echo "<b>".$i."</b><br />";
            foreach ($$i as $key=>$value)
            {
                $stores = "SELECT *
                FROM stores 
                WHERE storeID = '$key'";
                $query  = mysql_query($stores);?>
                <?php
                while ($row = mysql_fetch_array($query))
                {
                    $name = $row['name'];
                    $storename = trim(htmlspecialchars($row['storeName']));
                    $ids = $row['storeID'];
                    $link = htmlspecialchars($row['storeLink']);?>
                    <a href='<?php echo $link?>'><?php echo $storename.", ";?></a><?php
                    $count++;
                }
            }
                echo "</li>";
        }
        #echo "</ul>";
    }
    echo "</ul>";
    ?>
    </div><!-- End 3 columns container -->
    
    </li><!-- End 3 columns Item -->

but i would like to condense this if at all possible, or if there is a better way to do this please share
thanks

From a UI perspective, people read left to right (in the western world), therefore it would be better usability to have it output like the second example.

Confusing people will result in a lower conversion / CTR.

[edit]
Ignore me, I have just seen what you are trying to achieve under the retail stores tab and it does work better like you want it to above.

well, for starters, you could slap your repeated foreach loops in a function with the range as parameters:

PHP Code:


output_stores('A','E',$alpha);
...
output_stores('F','L',$alpha);

function output_stores($from,$to,$alpha)
{
  foreach(range($from,$to) as $i)
  {
      if (array_key_exists ("$i", $alpha))
      {
          $count = 1;
  .....
} 


@welshstew - i would be easier from a coding point of view as well because floats float left or right not up and down which is a real pain as ive had to hard code it as i cannot get them to automatically flow down and then across, which looks a little untidy because i dont know which stores have codes and how many - the joys of codeing :D

@janvt
thanks mate didnt even think of that
ive condensed it like so

PHP Code:

        output_stores('A','C',$alpha);
        output_stores('D','J',$alpha);
        output_stores('K','Q',$alpha);        
        output_stores('R','Z',$alpha);
        
        function output_stores($from,$to,$alpha)
        {
            $getcodes = mysql_query("SELECT  UPPER(SUBSTRING(stores.storeName,1,1)) AS letter, storeID, storeName,  rating FROM stores ORDER BY storename ASC")
            or die(mysql_error());
            while ($records = @mysql_fetch_array ($getcodes))
            {
                $alpha[$records['letter']] = !isset($alpha[$records['letter']]) ? 1 : $alpha[$records['letter']] + 1;
                ${$records['letter']}[$records['storeID']] = $records['storeName'];
            }        
            echo "<ul>";
            // Create Data Listing
            foreach(range($from,$to) as $i)
            {
                if (array_key_exists ("$i", $alpha))
                {
                    foreach ($$i as $key=>$value)
                    {
                        $checkcodes = mysql_query("SELECT codeID,code,codeLink,storeID,details,expireDate
                        FROM tbl_codes  
                        WHERE storeID = '$key'
                        ORDER BY expireDate ASC")or die(mysql_error);
                        $rows = mysql_num_rows($checkcodes);
                        if($rows >=1)
                        {
                            echo "<li>";
                            $code_count = 1;
                            //GET STORE LINK
                            $sql = mysql_query("SELECT * FROM stores WHERE name = '$value'")or die(mysql_error());
                            $row = mysql_fetch_array($sql);
                            $link = htmlentities($row['storeLink']);
                        
                            echo "<b>".htmlspecialchars($value)?></b><br /><?php
                            while ($row = mysql_fetch_array($checkcodes))
                            {
                                $codeLink = htmlspecialchars($row['codeLink']);?>                            
                                <a href="<?php echo  $codeLink?>" title="<?php echo $row['details']?>"><?php  echo $row['code']?></a><?php
                                if($code_count <> $rows)
                                {
                                    echo ", ";
                                }
                                $code_count++;
                            }
                            echo "</li>";
                        }
                    }
                }
            }
            echo "</ul>";
        }?>

which is alot shorter than before
cheers

for performance reasons I wouldn't select all the stores everytime you call the function. You have a couple options:
1. move the code out of the function.
2. cache the results and only fetch them during the first execution.
3. modify your SQL statement to only select the stores you need (means you have 4 smaller statements, I would run with one of the other solution):

Code:

"SELECT  UPPER(SUBSTRING(stores.storeName,1,1)) AS letter, storeID, storeName,  rating FROM stores WHERE letter BETWEEN('$from','$to') ORDER BY storename ASC"

i have tried the 3rd method but the code is throwing up an error

Code:

You have an error in your SQL syntax; check the manual that corresponds  to your MySQL server version for the right syntax to use near 'ORDER BY  storename ASC' at line 1

any ideas?
cheers mate

It's

Code:

storeName

in one place and

Code:

storename

in the other. Correct one of them.

It's storeName in one place and storename in the other. Correct one of them.

Originally Posted by ian

Field names aren't case sensitive (default value) in MySQL, that probably isn't the error. Could you post the whole query?

Oh yeah, so they're not. I forgot.

Ignore me.