Displaying results from MySQL database in columns

[alexsmith2709]

Hi,

This has been baffling me for a couple hours now and i cant seem to figure it out.

I have some code which creates an array and gets info from a mysql database and then displays in a list. This works great but after adding more and more rows to my database the list is now becoming quite large and doesnt look great on my site.

Is it possible to split the list into multiple columns of about 25 and if possible once 3 or 4 columns have been created start another column underneath.

To help explain i would be looking at a layout as follows:

line 1      line 1      line 1
line 2      line 2      line 2
...      ...      ...
line 25      line 25      line 25

line 1      line 1      line 1
line 2      line 2      line 2
...      ...      ...
line 25      line 25      line 25

Im guessing there should be some sort of if statement to check how many items are being displayed and to create a new column if necessary. Is this correct?

 

Thanks,

Alex

[AyKay47]

Hi,

This has been baffling me for a couple hours now and i cant seem to figure it out.

I have some code which creates an array and gets info from a mysql database and then displays in a list. This works great but after adding more and more rows to my database the list is now becoming quite large and doesnt look great on my site.

Is it possible to split the list into multiple columns of about 25 and if possible once 3 or 4 columns have been created start another column underneath.

To help explain i would be looking at a layout as follows:

line 1      line 1      line 1
line 2      line 2      line 2
...      ...      ...
line 25      line 25      line 25

line 1      line 1      line 1
line 2      line 2      line 2
...      ...      ...
line 25      line 25      line 25

Im guessing there should be some sort of if statement to check how many items are being displayed and to create a new column if necessary. Is this correct?

 

Thanks,

Alex

Yes is can be done,mans yes you will be counting the iterations performed and create a new column with an if statement. You will want to create a variable outside of the loop, then inside of the loop, increment it each time, this value will be the number of iterations. Posting the relevant code will surely help.

[Pikachu2000]

Set an incrementing integer, and check its modulus against 25. If the result is 0, echo a blank row. If you're using a table, you could do it this way, for example.

 

$i = 1;
while( $array = mysql_fetch_assoc($result) ) {
     // echo your table data as normal here
     echo $i % 25 === 0 ? "<tr><td colspan=\"3\"> </td></tr>\n" : ''; // will insert a blank row every 25th iteration
     $i++;
}

[alexsmith2709]

here is my code so far:

<?php
include_once("includes.php");
doConnect();

//gather the motherboards
$get_mobo_sql = "SELECT * FROM mobo WHERE mobo_brand='Asus' ORDER BY mobo_model ASC";
$get_mobo_res = mysqli_query($mysqli, $get_mobo_sql)
			or die(mysqli_error($mysqli));

if (mysqli_num_rows($get_mobo_res) < 1) {
//if there are no motherboards
$asus = "<p><em>Sorry, there are currently no motherboards.</em></p>";
} else {
//create the display string
$asus = "<b><u>Asus</u></b><br/>";

while ($mobo_info = mysqli_fetch_array($get_mobo_res)) {
	$mobo_id = $mobo_info['mobo_id'];
	$mobo_brand = stripslashes($mobo_info['mobo_brand']);
	$mobo_model = stripslashes($mobo_info['mobo_model']);
	$mobo_formfactor = stripslashes($mobo_info['mobo_formfactor']);
	$mobo_cpu = stripslashes($mobo_info['mobo_cpu']);
	$mobo_chipset = stripslashes($mobo_info['mobo_chipset']);
	$mobo_sysbus = stripslashes($mobo_info['mobo_sysbus']);
	$mobo_memory = stripslashes($mobo_info['mobo_memory']);
	$mobo_vga = stripslashes($mobo_info['mobo_vga']);
	$mobo_exp = stripslashes($mobo_info['mobo_exp']);
	$mobo_storage = stripslashes($mobo_info['mobo_storage']);
	$mobo_lan = stripslashes($mobo_info['mobo_lan']);
	$mobo_audio = stripslashes($mobo_info['mobo_audio']);
	$mobo_firewire = stripslashes($mobo_info['mobo_firewire']);
	$mobo_backpanel = stripslashes($mobo_info['mobo_backpanel']);
	$mobo_internal = stripslashes($mobo_info['mobo_internal']);
	$mobo_bios = stripslashes($mobo_info['mobo_bios']);
	$mobo_accessories = stripslashes($mobo_info['mobo_accessories']);
	$mobo_other = stripslashes($mobo_info['mobo_other']);

	//add to display
	$asus .= "
	<a href=\"motherboards.php?mobo_id=".stripslashes($mobo_id)."\"><strong>".$mobo_model."</strong></a><br/>";
}
//free results
mysqli_free_result($get_mobo_res);
}
echo $asus;
?>

 

This code is placed inside a div. As you can see i just have a <br/> tag to put the next item on a new line.

Im still confused where to put the code to count the number of rows etc.

 

 

Thanks,

Alex

[Pikachu2000]

Why are you calling stripslashes() on everything that comes out of the database? If you have escaping slashes stored with the data, something is wrong with the way it's being inserted to begin with.

 

On to the current question, what have you tried so far?

[litebearer]

Also, given...

the list is now becoming quite large and doesn't look great on my site.

You might start looking into pagination. (http://www.nstoia.com/sat/disp_pag/index.php)

[alexsmith2709]

Why are you calling stripslashes() on everything that comes out of the database? If you have escaping slashes stored with the data, something is wrong with the way it's being inserted to begin with.

 

On to the current question, what have you tried so far?

I put stripslashes in a while back when trying something out and never removed them. I have changed my site since writing this code.

I havent tried much to be honest, i've been staring at my code wondering where and how to do the if statement and wanted to make sure if i was on the right lines. Now i know on im the right lines i can concentrate a bit more.

 

Thanks for your first example, i can now have a go.

 

@litebearer: Thanks, i already have this list in a popup div using jquery, but when it gets even bigger i will look at pagination.

[laffin]

some sample code for you

<?php
  $MaxCols=5;
  $MaxRows=20;
  $PerTable=($MaxRows*$MaxCols);
  
  $items=range(1,$PerTable+rand(1,$PerTable));
  $count=count($items);
  
  $tables=intval($count/$PerTable)+1;
  $table=0;
  while($table<$tables)
  {
    echo "<table>".PHP_EOL;
    for($i=0;$i<$MaxRows;$i++)
    {
      echo "  <tr>".PHP_EOL;
      for($j=0;$j<$MaxCols;$j++)
      {
        $idx=($PerTable*$table)+($j*$MaxRows)+$i;
        $contents=(isset($items[$idx]))?$items[$idx]:' ';
        echo "    <td>$contents</td>".PHP_EOL;
      }
      echo "  </tr>".PHP_EOL;
    }
    echo "</table>".PHP_EOL;
    $table++;
  }
  
?>