Split database results into 3 equal columns

PHP
1

Hi everyone, I’ve got a very minor problem but it’s one I’d like to work out, so I’m looking for suggestions.

Basically, I have a set of database records that I want to split into 3 equal columns. I want to output the data from top to bottom, however, not left to right. So instead of:

Result 1 Result 2 Result 3
Result 4 Result 5 Result 6 etc

I want to have:

Result 1 Result 3 Result 5
Result 2 Result 4 Result 6

Usually I’d just use the % operator to sort out however many columns I need, but in this case it won’t work. I would also, for preference, like to have a <ul> in each column, containing the results.

This is how I’m currently doing it, which more or less works.

<?php
	
	  if ($result = mysqli_query($dbcnx, "SELECT id FROM services")) {

		  $totalRows = mysqli_num_rows($result);
		  mysqli_free_result($result);
	  }


	
	  $items = $totalRows/3;
	  $numItems = ceil($items);
	

	  if ($menuSQL = mysqli_query($dbcnx, "SELECT service, servicePrintName FROM services ORDER BY service ASC"))
	  {
		  $menuList  = array();
		
		  while ($menuRow = mysqli_fetch_assoc($menuSQL)) {
		  $menuList[] = $menuRow;
		  }

	  }
	
	  mysqli_free_result($menuSQL);
	
  ?>

  <div id="service-list">

	<div class="col-01">
	  <ul>
	  <?php
			
	   for ($i = 0; $i < $numItems; $i++)
	   {
	  ?>
		 <li><a href="service.php?id=<?php echo $menuList[$i]['service'];?>"><?php echo $menuList[$i]['servicePrintName'];?></a></li>
	  <?php
	   }
	  ?>
	  </ul>
	</div><!--end col-01-->


	<div class="col-02">
	  <ul>
	  <?php
		
		 $k = $i;
		
		 for ($j = 0; $j < $numItems; $j++)
		 {
			
	   ?>
		   <li><a href="service.php?id=<?php echo $menuList[$k]['service'];?>"><?php echo $menuList[$k]['servicePrintName'];?></a></li>
	   <?php
			$k++;
		 }
	   ?>
	  </ul>
	</div><!--end col-02-->
	

	<div class="col-03">
	  <ul>
	  <?php
		
		 $m = $k;
		
		 for ($l = 0; $l < $numItems; $l++)
		 {
			
	   ?>
		   <li><a href="service.php?id=<?php echo $menuList[$m]['service'];?>"><?php echo $menuList[$m]['servicePrintName'];?></a></li>
	   <?php
			$m++;
		 }
	   ?>
	  </ul>
	</div><!--end col-03-->

My minor niggle is this - depending on the number of results, the data is not spread out evenly amongst the columns. E.g. at the moment I have 28 results. If I was hardcoding this, I’d lay that out with column one having 10 items, and columns two and three having 9 each. But as I’m using ceil(), the actual result I get with my code is that columns one and two get 10 items each, while column three gets just 8.

I feel like there must be a way of working this out (after all, that’s how my brain works out the optimal layout), but I can’t think how to translate “divide the results evenly, and then chuck the remainder into as many columns as required” into a version that can be used to display the results from top to bottom. I’ve tried searching, but all I’ve been able to find on this topic covers left-to-right columns.

Does anyone have any suggestions as to how I can do this, even keywords I might use to search on? I’ve tried everything I can think of, but I’ve been caught in the past not being able to find a single thing on common topics just because I haven’t known what to ask for . . .

Please feel free to comment generally on my code - I am by no means an expert and I wouldn’t mind some opinions on whether this is unusually inefficient code or anything like that.

Cheers for any input.

2

i can’t comment on your php code, but your first query is unnecessary, just use mysql_num_rows() on the second

3

I would query and build an array with it. (You can build the HTML during the loop, but this makes updates harder, so separating it is better)

Then split the array into 3 with this:

$count = mysql_num_rows($rs);
$first_col = ceil($count/ 3);
$count -= $first_col;
$second_col = ceil($count / 2);
$third_col = $count - $second_col;

(This could be done with a loop so you could easily change the number of columns, just takes more effort than I’d like to offer at the moment ;))

Then it’s just a matter of calling array_splice() to split the array into 3 parts, now that you know the size of each column.

Using the logic above, given 6 rows, you’d get, 2, 2, 2. Given 7, you’d get 3, 2, 2, and with 8, you’d get 3, 3, 2. Which would be the proper split.

4

Ah yes, I like it, this is the logic I am looking for. I will give that implementation a go.

Thanks also for the comment on the efficiency of my queries, r937, that’s the sort of thing that I often have trouble with, because my brain is not well suited for programming . . .

Cheers for the replies!