tkv
12-29-2007, 08:07 PM
Intro
OK, so you know how to get your data out of a database and display it. But, it looks rather dull in a single column down the screen doesn't it? Today you will learn how to put that data into multiple columns. We will do this two different ways. The first we will display the data moving horizontally across the screen. Like so:
1 2
3 4
5 6
7 8
The second method we will display the data going vertically, like so:
1 5
2 6
3 7
4 8
I am going to make a couple assumptions during this tutorial. First, that you already understand how to query your data out of the database. Second, that you understand the basics of PHP.
OK, so on to the first method!
Horizontal Display
OK, first off let's take a look at how we would fetch some data from a database and display it in a table without multiple columns.
<?php
mysql_connect('localhost','','');
mysql_select_db('test');
$query = "SELECT stuff FROM mystuff ORDER BY stuff";
$result = mysql_query($query);
echo "<TABLE BORDER=\"0\">\n";
while($row = mysql_fetch_array($result)) {
echo "<TR><TD>" . $row['stuff'] . "</TD></TR>\n";
}
echo "</TABLE>\n";
?>
The preceeding code will connect to a database and display all the data in a single column table. With the data that I am using, it comes out looking like :
apple
baby
belt
brother
doctor
father
grape
grapefruit
mother
orange
pear
shirt
shoe
sister
tie
So, what we want to do is break that into two columns so that apple and baby are on one line, belt and brother on the next, and so on.
The key to making this happen is the modulus operator. The modulus operator returns the remainder of a division problem. With the modulus operator, we will be able to tell how many columns we have output and know when to start a new row. Let's look at the code.
<?php
//set the number of columns
$columns = 2;
mysql_connect('localhost','','');
mysql_select_db('test');
$query = "SELECT stuff FROM mystuff ORDER BY stuff";
$result = mysql_query($query);
//we add this line because we need to know the number of rows
$num_rows = mysql_num_rows($result);
echo "<TABLE BORDER=\"0\">\n";
//changed this to a for loop so we can use the number of rows
for($i = 0; $i < $num_rows; $i++) {
$row = mysql_fetch_array($result);
if($i % $columns == 0) {
//if there is no remainder, we want to start a new row
echo "<TR>\n";
}
echo "<TD>" . $row['stuff'] . "</TD>\n";
if(($i % $columns) == ($columns - 1) || ($i + 1) == $num_rows) {
//if there is a remainder of 1, end the row
//or if there is nothing left in our result set, end the row
echo "</TR>\n";
}
}
echo "</TABLE>\n";
?>
That looks a bit more complicated, but let's break it down and look at the new additions.
$columns = 2;
Here we are just setting how many columns we want.
$num_rows = mysql_num_rows($result);
Just grabbing the number of rows in the result set.
for($i = 0; $i < $num_rows; $i++) {
$row = mysql_fetch_array($result);
Here we switched to a for loop so that we can keep track of which record we are on. We also moved the row retrieval to the inside of the for loop.
if($i % $columns == 0) {
//if there is no remainder, we want to start a new row
echo "<TR>\n";
}
This is the code that starts a row.
echo "<TD>" . $row['stuff'] . "</TD>\n";
All we did here is remove the starting and ending of rows from the HTML
if(($i % $columns) == ($columns - 1) || ($i + 1) == $num_rows) {
//if there is a remainder of 1, end the row
//or if there is nothing left in our result set, end the row
echo "</TR>\n";
}
And this is the code that ends a row.
With the same data set, here is the output I get from the above script with two columns set:
apple baby
belt brother
doctor father
grape grapefruit
mother orange
pear shirt
shoe sister
tie
As you can see, this horizontal multi-column output is very simple. Now on to vertical display.
Vertical Display
OK, now let's do a vertical display. Let's take a look at our new code.
<?php
$columns = 4;
mysql_connect('localhost','','');
mysql_select_db('test');
$query = "SELECT stuff FROM mystuff ORDER BY stuff";
$result = mysql_query($query);
$num_rows = mysql_num_rows($result);
//we are going to set a new variables called $rows
$rows = ceil($num_rows / $columns);
//to do this display, we will need to run another loop
//this loop will populate an array with all our values
while($row = mysql_fetch_array($result)) {
$data[] = $row['stuff'];
}
echo "<TABLE BORDER=\"0\">\n";
//here we changed the condition to $i < $rows
for($i = 0; $i < $rows; $i++) {
echo "<TR>\n";
//here will run another loop for the amount of columns
for($j = 0; $j < $columns; $j++) {
if(isset($data[$i + ($j * $rows)])) {
echo "<TD>" . $data[$i + ($j * $rows)] . "</TD>\n";
}
}
echo "</TR>\n";
}
echo "</TABLE>\n";
?>
OK, let's take a look at the modifications one by one.
$rows = ceil($num_rows / $columns);
We have set a new variable called $rows. We get this value from the number of rows in our data set divided by the number of columns we want. The ceil function rounds that number up to the next whole number.
while($row = mysql_fetch_array($result)) {
$data[] = $row['stuff'];
}
Next we take all the data from the result set and put it in an array.
for($i = 0; $i < $rows; $i++) {
Or for loop condition changed to only loop for the number of rows we will need.
for($j = 0; $j < $columns; $j++) {
if(isset($data[$i + ($j * $rows)])) {
echo "<TD>" . $data[$i + ($j * $rows)] . "</TD>\n";
}
}
Here is what does the magic. What we are doing here is running a for loop for the amount of columns we want. Then we take which column we are on (0, 1, or 2) and multiply that times the amount of rows in our table. Then we add that the the row we are on to get which piece of data to display.
As you can see, we no longer need to check whether or not to display the start and end of a row, we always do as the outer for loop is on a per row basis.
Something to think about
Shortly after posting this tutorial, I was asked the question "What if I want to print out more than one piece of data in these multiple columns?" Well, here's the same code from the last page but modified to print more than one piece of data.
<?php
$columns = 4;
mysql_connect('localhost','root','LRMSys2k');
mysql_select_db('test');
//change the query to get another field from the database
$query = "SELECT stuff, morestuff FROM mystuff ORDER BY stuff";
$result = mysql_query($query);
$num_rows = mysql_num_rows($result);
$rows = ceil($num_rows / $columns);
while($row = mysql_fetch_array($result)) {
$data[] = $row['stuff'];
//store the other field into an array
$data2[] = $row['morestuff'];
}
echo "<TABLE BORDER=\"0\">\n";
for($i = 0; $i < $rows; $i++) {
echo "<TR>\n";
for($j = 0; $j < $columns; $j++) {
if(isset($data[$i + ($j * $rows)])) {
echo "<TD>" . $data[$i + ($j * $rows)] . "</TD>\n";
//echo out the field
echo "<TD>" . $data2[$i + ($j * $rows)] . "</TD>\n";
}
}
echo "</TR>\n";
}
echo "</TABLE>\n";
?>
As you can see, there was very little modification. Just need to store the field in an array also, and then echo it out.
Well, that's it. I hoped you learned something and can put it to good use!
OK, so you know how to get your data out of a database and display it. But, it looks rather dull in a single column down the screen doesn't it? Today you will learn how to put that data into multiple columns. We will do this two different ways. The first we will display the data moving horizontally across the screen. Like so:
1 2
3 4
5 6
7 8
The second method we will display the data going vertically, like so:
1 5
2 6
3 7
4 8
I am going to make a couple assumptions during this tutorial. First, that you already understand how to query your data out of the database. Second, that you understand the basics of PHP.
OK, so on to the first method!
Horizontal Display
OK, first off let's take a look at how we would fetch some data from a database and display it in a table without multiple columns.
<?php
mysql_connect('localhost','','');
mysql_select_db('test');
$query = "SELECT stuff FROM mystuff ORDER BY stuff";
$result = mysql_query($query);
echo "<TABLE BORDER=\"0\">\n";
while($row = mysql_fetch_array($result)) {
echo "<TR><TD>" . $row['stuff'] . "</TD></TR>\n";
}
echo "</TABLE>\n";
?>
The preceeding code will connect to a database and display all the data in a single column table. With the data that I am using, it comes out looking like :
apple
baby
belt
brother
doctor
father
grape
grapefruit
mother
orange
pear
shirt
shoe
sister
tie
So, what we want to do is break that into two columns so that apple and baby are on one line, belt and brother on the next, and so on.
The key to making this happen is the modulus operator. The modulus operator returns the remainder of a division problem. With the modulus operator, we will be able to tell how many columns we have output and know when to start a new row. Let's look at the code.
<?php
//set the number of columns
$columns = 2;
mysql_connect('localhost','','');
mysql_select_db('test');
$query = "SELECT stuff FROM mystuff ORDER BY stuff";
$result = mysql_query($query);
//we add this line because we need to know the number of rows
$num_rows = mysql_num_rows($result);
echo "<TABLE BORDER=\"0\">\n";
//changed this to a for loop so we can use the number of rows
for($i = 0; $i < $num_rows; $i++) {
$row = mysql_fetch_array($result);
if($i % $columns == 0) {
//if there is no remainder, we want to start a new row
echo "<TR>\n";
}
echo "<TD>" . $row['stuff'] . "</TD>\n";
if(($i % $columns) == ($columns - 1) || ($i + 1) == $num_rows) {
//if there is a remainder of 1, end the row
//or if there is nothing left in our result set, end the row
echo "</TR>\n";
}
}
echo "</TABLE>\n";
?>
That looks a bit more complicated, but let's break it down and look at the new additions.
$columns = 2;
Here we are just setting how many columns we want.
$num_rows = mysql_num_rows($result);
Just grabbing the number of rows in the result set.
for($i = 0; $i < $num_rows; $i++) {
$row = mysql_fetch_array($result);
Here we switched to a for loop so that we can keep track of which record we are on. We also moved the row retrieval to the inside of the for loop.
if($i % $columns == 0) {
//if there is no remainder, we want to start a new row
echo "<TR>\n";
}
This is the code that starts a row.
echo "<TD>" . $row['stuff'] . "</TD>\n";
All we did here is remove the starting and ending of rows from the HTML
if(($i % $columns) == ($columns - 1) || ($i + 1) == $num_rows) {
//if there is a remainder of 1, end the row
//or if there is nothing left in our result set, end the row
echo "</TR>\n";
}
And this is the code that ends a row.
With the same data set, here is the output I get from the above script with two columns set:
apple baby
belt brother
doctor father
grape grapefruit
mother orange
pear shirt
shoe sister
tie
As you can see, this horizontal multi-column output is very simple. Now on to vertical display.
Vertical Display
OK, now let's do a vertical display. Let's take a look at our new code.
<?php
$columns = 4;
mysql_connect('localhost','','');
mysql_select_db('test');
$query = "SELECT stuff FROM mystuff ORDER BY stuff";
$result = mysql_query($query);
$num_rows = mysql_num_rows($result);
//we are going to set a new variables called $rows
$rows = ceil($num_rows / $columns);
//to do this display, we will need to run another loop
//this loop will populate an array with all our values
while($row = mysql_fetch_array($result)) {
$data[] = $row['stuff'];
}
echo "<TABLE BORDER=\"0\">\n";
//here we changed the condition to $i < $rows
for($i = 0; $i < $rows; $i++) {
echo "<TR>\n";
//here will run another loop for the amount of columns
for($j = 0; $j < $columns; $j++) {
if(isset($data[$i + ($j * $rows)])) {
echo "<TD>" . $data[$i + ($j * $rows)] . "</TD>\n";
}
}
echo "</TR>\n";
}
echo "</TABLE>\n";
?>
OK, let's take a look at the modifications one by one.
$rows = ceil($num_rows / $columns);
We have set a new variable called $rows. We get this value from the number of rows in our data set divided by the number of columns we want. The ceil function rounds that number up to the next whole number.
while($row = mysql_fetch_array($result)) {
$data[] = $row['stuff'];
}
Next we take all the data from the result set and put it in an array.
for($i = 0; $i < $rows; $i++) {
Or for loop condition changed to only loop for the number of rows we will need.
for($j = 0; $j < $columns; $j++) {
if(isset($data[$i + ($j * $rows)])) {
echo "<TD>" . $data[$i + ($j * $rows)] . "</TD>\n";
}
}
Here is what does the magic. What we are doing here is running a for loop for the amount of columns we want. Then we take which column we are on (0, 1, or 2) and multiply that times the amount of rows in our table. Then we add that the the row we are on to get which piece of data to display.
As you can see, we no longer need to check whether or not to display the start and end of a row, we always do as the outer for loop is on a per row basis.
Something to think about
Shortly after posting this tutorial, I was asked the question "What if I want to print out more than one piece of data in these multiple columns?" Well, here's the same code from the last page but modified to print more than one piece of data.
<?php
$columns = 4;
mysql_connect('localhost','root','LRMSys2k');
mysql_select_db('test');
//change the query to get another field from the database
$query = "SELECT stuff, morestuff FROM mystuff ORDER BY stuff";
$result = mysql_query($query);
$num_rows = mysql_num_rows($result);
$rows = ceil($num_rows / $columns);
while($row = mysql_fetch_array($result)) {
$data[] = $row['stuff'];
//store the other field into an array
$data2[] = $row['morestuff'];
}
echo "<TABLE BORDER=\"0\">\n";
for($i = 0; $i < $rows; $i++) {
echo "<TR>\n";
for($j = 0; $j < $columns; $j++) {
if(isset($data[$i + ($j * $rows)])) {
echo "<TD>" . $data[$i + ($j * $rows)] . "</TD>\n";
//echo out the field
echo "<TD>" . $data2[$i + ($j * $rows)] . "</TD>\n";
}
}
echo "</TR>\n";
}
echo "</TABLE>\n";
?>
As you can see, there was very little modification. Just need to store the field in an array also, and then echo it out.
Well, that's it. I hoped you learned something and can put it to good use!