SitePoint Sponsor

User Tag List

Results 1 to 4 of 4
  1. #1
    SitePoint Enthusiast
    Join Date
    Jul 2005
    Posts
    55
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Split database results into 3 equal columns

    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 Code:
    <?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. #2
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,329
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    i can't comment on your php code, but your first query is unnecessary, just use mysql_num_rows() on the second
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"

  3. #3
    SitePoint Wizard wonshikee's Avatar
    Join Date
    Jan 2007
    Posts
    1,223
    Mentioned
    3 Post(s)
    Tagged
    0 Thread(s)
    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. #4
    SitePoint Enthusiast
    Join Date
    Jul 2005
    Posts
    55
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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!


Bookmarks

Posting Permissions

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