Display Data in Multiple Columns from Database using PHP

In some scenario we need to display data in multiple columns in a HTML table where each row from database keeps data for a single column of HTML table. The data can be text, image or mixture of both. This article will guide you display data in multiple columns from database using PHP.

Example of this kind of arrangement is product catalog which you can see in picture above. The products are displayed in columns along with image and product name.

We have used a while loop to achieve that. The only thing to keep in mind is to print blank columns to maintain equal number of column in each row after displaying last record from database. For that we have used for loop after end of while.

The code along with comments will let you assist to understand the code and to display data in multiple columns:

<?Php
	$connection = new mysqli('HOST_NAME', 'USER_NAME', 'PASSWORD', 'DATABASE_NAME');
	
	if ($connection->connect_errno > 0) {
		die ('Unable to connect to database [' . $connection->connect_error . ']');
	}	
	$sql = "SELECT * FROM products";
	if (!$result = $connection->query($sql)) {
    	die ('There was an error running query[' . $connection->error . ']');
	}	
?>
...
<?php
	$rows = $result->num_rows;    // Find total rows returned by database
	if($rows > 0) {
		$cols = 3;    // Define number of columns
		$counter = 1;     // Counter used to identify if we need to start or end a row
		$nbsp = $cols - ($rows % $cols);    // Calculate the number of blank columns

                echo '<table width="100%" align="center" cellpadding="4" cellspacing="1">';
		while ($row = $result->fetch_array()) {
			if(($counter % $cols) == 1) {    // Check if it's new row
				echo '<tr>';	
			}
                   $img = "http://yourdomain.com/assets/".$row['image_name'];
			echo '<td><img src="'.$img.'" alt="'.$row['name'].'" /><h5>'.$row['name'].'</h5></td>';
			if(($counter % $cols) == 0) { // If it's last column in each row then counter remainder will be zero
				echo '</tr>';	
			}
			$counter++;    // Increase the counter
		}
		$result->free();
		if($nbsp > 0) { // Add unused column in last row
			for ($i = 0; $i < $nbsp; $i++)	{ 
				echo '<td>&nbsp;</td>';		
			}
			echo '</tr>';
		}
                echo '</table>';
	}
?>

<?Php

$connection = new mysqli('HOST_NAME', 'USER_NAME', 'PASSWORD', 'DATABASE_NAME');

if ($connection->connect_errno > 0) {

die ('Unable to connect to database [' . $connection->connect_error . ']');

}

$sql = "SELECT * FROM products";

if (!$result = $connection->query($sql)) {

die ('There was an error running query[' . $connection->error . ']');

}

...

<?php

$rows = $result->num_rows; // Find total rows returned by database

if($rows > 0) {

$cols = 3; // Define number of columns

$counter = 1; // Counter used to identify if we need to start or end a row

$nbsp = $cols - ($rows % $cols); // Calculate the number of blank columns

echo '<table width="100%" align="center" cellpadding="4" cellspacing="1">';

while ($row = $result->fetch_array()) {

if(($counter % $cols) == 1) { // Check if it's new row

echo '<tr>';

}

$img = "http://yourdomain.com/assets/".$row['image_name'];

echo '<td><img src="'.$img.'" alt="'.$row['name'].'" /><h5>'.$row['name'].'</h5></td>';

if(($counter % $cols) == 0) { // If it's last column in each row then counter remainder will be zero

echo '</tr>';

}

$counter++; // Increase the counter

}

$result->free();

if($nbsp > 0) { // Add unused column in last row

for ($i = 0; $i < $nbsp; $i++) {

echo '<td> </td>';

}

echo '</tr>';

}

echo '</table>';

}

The above result can easily be achieved with div structure or HTML lists also. But to understand the technique, i have used tables. Hope it will help you to display data in multiple columns from database using PHP.

9 COMMENTS

focused says:
March 14, 2022 at 8:11 pm
Hi,I am starting coding..
And this thread was really helpfull to me…
I could adjust it to my needs, but I am soting as I am sorting my data by alphabetical orders, I would like a different layout:
actaully with the code you wrote, I habve:
aa | ab | ac
ad | ae | af
I would like to display it like this:
aa | ad
ab | ae
ac | af
does somebody can help me with that ?
GodFear17 says:
January 10, 2018 at 1:26 pm
If you take XRP or ETH in donations, send me your address. This helped and is worthy of a donation…
1. Amit Sonkhiya says:
  January 10, 2018 at 11:30 pm
  Hello @godfear17:disqus
  Glad to know that the helped you. We prefer to accept Payoneer and PayPal transactions.
GodFear17 says:
January 10, 2018 at 1:25 pm
Perfect! Just Perfect!
Hanamichi Sakuragi says:
August 14, 2017 at 11:58 pm
sir can you please explain this line of codes “$img = “http://yourdomain.com/assets/”.$row[‘image_name’];” cause i cant show the images and what data should i enter in the database. this is the location of my images, src=”imgreyvibal.jpg”.
Larry Wishon says:
June 23, 2015 at 5:40 am
This has been very helpful. I would like to be able to have two responsive columns. Any quick advice on how this would be achieved? I realize I would probably use divs instead of tables. But not real clear how to integrate that into the above code.
1. Amit Sonkhiya says:
  June 23, 2015 at 10:28 am
  Hi,
  I will write a post in day one or two to use the same with div.However you can replace tags as the following:
  table with parent container
  tr with row in container
  td with columns in each row
  Also you would need to apply some CSS for responsive columns.
  1. Larry Wishon says:
    June 23, 2015 at 2:16 pm
    Thanks for your quick reply, Amit. I am working on a project for a client where this will be a necessity. I will start playing around with it and see what I can do. I think there are many out there that can benefit from these. Thanks again,
    1. Amit Sonkhiya says:
      June 25, 2015 at 4:36 pm
      Hi Larry,
      I have just posted a post http://fellowtuts.com/php/display-data-in-responsive-columns-from-database-using-php/ and tried my best to explain.
      I have also dropped you an email from amit@fellowtuts.com.
      Thanks

9 COMMENTS

Leave a Reply Cancel reply