help with html table code, displaying values from database

Question

Hi, i want to display values from a database (a list of categories) into a table that has 2 columns and x number of rows.

I want my web page to display like this:

Apes Cats
Apples Cherries
Bats Tigers
Berries Zebras

Instead of

Apes Apples
Bats Bears
Cats Cherries
Tigers Zebras

Here is my code so far:

  <table border="0" bordercolor="#FFCC00" style="background-color:#FFFFCC" width="400" cellpadding="3" cellspacing="3">
                        <?php
                        $query = "SELECT * FROM category ORDER BY cat_name";
                        $data = mysqli_query($dbc, $query);

                        while ($category = mysqli_fetch_array($data)) {
                        ?>
                            <tr>    
                                <td><?php echo $category['cat_name'] ?></td>
                            </tr>    
                        <?php } ?>
                    </table>

Answer 1

You can add an another variable say, $i, to the loop and just increment this through as follows:

<table border="0" bordercolor="#FFCC00" style="background-color:#FFFFCC" width="400" cellpadding="3" cellspacing="3">
        <?php
        $query = "SELECT * FROM category ORDER BY cat_name";
        $data = mysqli_query($dbc, $query);

        $i=1;

        while ($category = $data->fetch_row()) {
        ?>
            <tr>
                <td><?php echo $i; ?></td>   
                <td><?php echo $category[1] ?></td>
            </tr>   
        <?php
        $i++;
        } ?>
    </table>

EDIT: Updated to fetch row data for each result and assume that cat_name is the second item in the array i.e. $category[1].

Answer 2

If it doesn't have to be a table and your fine with browsers that support CSS 3, then you can use CSS columns:

http://jsfiddle.net/QKuDL/

Otherwise you'll need to sort the results first (untested - PHP is not my strong suit):

<?php
$query = "SELECT * FROM category ORDER BY cat_name";
$data = mysqli_query($dbc, $query);

$col_count = 2;
$max_items_per_col = ceil( mysqli_num_rows ( $data ) / $col_count );
$cols = array(array());

$col = 0;

while ($category = mysqli_fetch_array($data)) {
   if (count($cols[$col]) >= $max_items_per_col) {
     $col++;
   }
   $cols[$col][] = $category['cat_name'];
}
?>
<table> <!-- all of those attributes should be CSS instead -->
 <?php for ($i = 0; $i < $max_items_per_col; $i++) { ?>
    <tr>
       <?php foreach ($cols as $col) { ?>
         <td><?php if(isset($col[$i])) echo $col[$i]; ?></td>
       <?php } ?>       
    </tr>
 <?php } ?>
 </table>

Answer 3

Here's the basic idea:

You get the count of the data via num_rows Divide by two. Now, the result of your division will be the number of rows.

Output a loop echoing value for row x and x+ num rows. For example the output of line 1 would be :

<tr><td>$row[val1][data]</td><td>$row[val5][data]</td></tr>

So, your loop would ultimately output:

val 1  |  val 5
val 2  |  val 6
val 3  |  val 7
val 4  |  val 8

The loop should end when your incrementing variable = num_rows. Should be pretty straightforward from there. Good luck.

Answer 4

    <?php
    $query = "SELECT * FROM category ORDER BY cat_name";
    $data = mysqli_query($dbc, $query);
    $midpoint = ceil($data->num_rows / 2);
    $i=0;

    while ($category = $data->fetch_array()) {
      if ($i < $midpoint)
        $left[$i] = "<td>" . $category['cat_name'] . "</td>";
      else 
         $right[$i - $midpoint] = "<td>" . $category['cat_name'] . "</td>";
      $i++;
    }

    print "<table>";
    for ($j=0; $j < $i; $j++)
    {
        print "<tr>" . $left[$j];
        if (array_key_exists($j, $right)) print $right[$j];
        print "</tr>";
    }
    print "</table>";

Answer 5

Try this (haven't tested it though):

<table border="0" bordercolor="#FFCC00" style="background-color:#FFFFCC" width="400" cellpadding="3" cellspacing="3">
    <?php
    $query = "SELECT * FROM category ORDER BY cat_name";
    $data = mysqli_query($dbc, $query);

    # Calculate total rows and half rows, rounded up
    $full_row_count = mysqli_num_rows($data);
    $half_row_count = ceil($full_row_count / 2);

    $i = 0;
    while ($i <= $half_row_count) {
        # Set the result pointer for first column ...
        mysqli_data_seek($data, $i);
        $category_1 = mysqli_fetch_array($data);

        # ... then calculate the offset for the second column ...
        $col_2_offset = $i + $half_row_count;

        # .. and make sure it's not larger than total rows - 1
        if ($col_2_offset <= $full_row_count - 1) {
            mysqli_data_seek($data, $col_2_offset);
            $category_2 = mysqli_fetch_array($data);
        } else {
            $category_2 = array('cat_name' => '');
        }
    ?>
    <tr>    
        <td><?php echo $category_1['cat_name'] ?></td>
        <td><?php echo $category_2['cat_name'] ?></td>
    </tr>
    <?php
        $i++;
    }
    ?>
</table>

Hope this helps !