Organizing database list into columns using PHP

Question

My sql db has many rows. I use a php while loop to echo out the database on a page, into a html list

 <ul>
<?php
     while ($data = mysqli_fetch_assoc($result)):
        $address = $data['address'];
        $ad_link = $data['ad_link'];
      if(is_null($ad_link)){$ad_link = "#";}
?>
     <li><?php echo $address; ?></li>
        <?php endwhile; mysqli_close($con); ?>
  </ul>

I would like the list to run into an adjacent column after a certain number of lines have been spit out (rather than just run a mile down the page).

Then going into a third and forth column.

How does one even begin to do this? Doing it in a table would be preferable.

@lance solution below!

enter image description here

based on your description, what would the markup look like in the end? — Kevin
– Kevin, Commented Oct 7, 2014 at 23:53
Why not just dump all the values into an array, then count to a certain value inside the array equal to the number of columns you want and stop there. Resume from that point in the next column and so on... — Andrei P.
– Andrei P., Commented Oct 7, 2014 at 23:58
@ghost I added to question. main goal here is to keep it automated so as sql table gets bigger the list will roll over to the next column after a certain number of lines — rhill45
– rhill45, Commented Oct 8, 2014 at 0:01
@Andrew P. that could work, or could use SQL COUNT but this just seems like something thats more embedded — rhill45
– rhill45, Commented Oct 8, 2014 at 0:02

Lance · Accepted Answer · 2014-10-08 00:35:20Z

2

Try separating your MySQL code from your PHP/HTML code as it can get confusing. If you want to echo out multiple lists, then define how many list items that you want in each list and then create two loops; one to echo out the actual opening and closing tags for the lists and one for the list items. The list items loop will need to be nested inside of the other loop.

<?php
// Connect to the DB
$mysqli = mysqli_connect('localhost', 'username', 'password', 'db_name');

if(mysqli_connect_errno($mysqli)) {
    echo mysqli_connect_error($mysqli);
    die;
} 

$sql = "SELECT * FROM table";
$query = mysqli_query($mysqli, $sql)or die(mysqli_error($mysqli));
$num = mysqli_num_rows($query);
$i = 0;

while($row = mysqli_fetch_array($query)) {
    $address[$i] = $row['address'];
    $ad_link[$i] = $row['ad_link'];

    $i++;
}

mysqli_close($mysqli); 

// Define how many list items will be placed in each list
$per_list = 10;
// Determine how many lists there will need to be
$lists = ceil($num/$per_list);

// Loop thru each list
for($i=0;$i<$lists;$i++) {
     echo "<ul style='float:left;'>";

     // Determine the starting and ending points for the second loop that will echo out the list items
     $start = $i*$per_list;

     // Since the final list may not be a complete list containing 10 items,
     // only extend the loop to the full amount
     if($i == ($lists-1)) {
         // Find the modulus 
         $mod = $num%$per_list;

         if($mod > 0) { 
              $end = ($start+$per_list)-$mod;
         } else {
              $end = $start+$per_list;
         }
     } else {
         $end = $start+$per_list;
     }

     // Echo out the list items that are inside of the list
     for($x=$start;$x<$end;$x++) {
         if(is_null($ad_link[$x])) {
              $ad_link = "#";
         }

         echo "<li>".$address[$x]."</li>";
     }

     echo "</ul>";
}

echo "<div style='clear:both;'></div>";
?>

edited Oct 8, 2014 at 0:35

answered Oct 8, 2014 at 0:04

Lance

4,83016 gold badges56 silver badges91 bronze badges

Sign up to request clarification or add additional context in comments.

6 Comments

rhill45 Over a year ago

I'm pretty impressed @Lance. I'm working through it, just pasting it in under my db credentials didn't seem to work. Should mysqli_close by in the middle of the script there?

Lance Over a year ago

And yes, once you've queried the DB and saved all of your info from the DB as variables, you can close connection to the DB.

rhill45 Over a year ago

i think i have it working, it's definitely separating it into groups of 10. Those groups then go straight down the page. So let me know if i'm correct: css could then direct this into columns: echo "<li>".$address[$x]."</li>"; ? if you wrote this on the fly man I'm pretty impressed

Lance Over a year ago

Yes, if you set the style of each <ul> to float:left;, they'll appear alongside of one another. Just remember to clear the float at the end. I'll update my answer

Lance Over a year ago

Glad I was able to help!

|

Collectives™ on Stack Overflow

Organizing database list into columns using PHP

1 Answer 1

6 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

6 Comments

Your Answer

Sign up or log in

Post as a guest

Related