Collected Solutions.... Displaying Three or Four Items per Row in a Table

Weedpacket

v. 0.β.3

Instead of displaying records one per row, you want to display them three per row, or four per row or something like that?

Let's say that there are three columns. The amount of padding needed to finish off the last row for various numbers of items is worked out in the following table. The first two columns are straightforward. The other three illustrate my working in getting from the numbers in the "items" column to the numbers in the "padding" column - in other words, how I figured out how much padding each number of items needed.

items  padding 2-padding = b   3*?+b = a    a-2 = items
  0        0         2-0 = 2   3*0+2 = 2    2-2 = 0
  1        2         2-2 = 0   3*1+0 = 3    3-2 = 1
  2        1         2-1 = 1   3*1+1 = 4    4-2 = 2
  3        0         2-0 = 2   3*1+2 = 5    5-2 = 3
  4        2         2-2 = 0   3*2+0 = 6    6-2 = 4
  5        1         2-1 = 1   3*2+1 = 7    7-2 = 5
  6        0         2-0 = 2   3*2+2 = 8    8-2 = 6
  7        2         2-2 = 0   3*3+0 = 9    9-2 = 7
  8        1         2-1 = 1   3*3+1 =10   10-2 = 8
  9        0         2-0 = 2   3*3+2 =11   11-2 = 9
 10        2         2-2 = 0   3*4+0 =12   12-2 =10
items  padding padding = 2-b     b = a%3    a = items+2

Looks complicated, but really the hardest thing about it is counting to three. I've worked out how much padding is needed for different numbers of items, and now I'm trying to figure out how to relate those two columns ("items" and "padding") to each other.

Well, for a start the numbers in "padding" decrease while those in "items" increase (because the amount of padding needed decreases as items are added to the last row). Subtracting all the numbers in the "padding" column from 2 creates a list of numbers in increasing order while still keeping them between 0 and 2. In other words, I've figured out how many items there are in the last row (with an imaginary empty row added whenever I run out of padding).

The sequence doesn't look like it's increasing - what with all the jumping from 2 down to 0 - but now that I'm counting items I can take into account the full rows. The first jump from 2 to 0 is because I filled a row of three and started a new empty row. If I now count items in full rows I really will have a steadily increasing sequence (this is the hard part: how many times three do I need to add? Hint: I'm adding one item at a time).

Ooh, almost. That fourth column is so close - but every line has two items too many. What can we do? Well, we can subtract two. And that gets us the number of items. That's the good news. The bad news is that now we have to turn around and go back. We started with the padding, and worked out how many items would require it. We were supposed to start with the items and work out the padding.

But the good news is that we've done all the work. If subtracting 2 from the fourth column gives us the fifth column, then adding 2 to the fifth column gives us the fourth column. And so on. (This is where the modulus operator comes in: it lets you throw away full rows as you complete them, leaving you with the last row and its padding). That's what the last line of the table shows: how to get from the number of items to the amount of empty padding (add 2, remainder modulo 3, subtract from 2). We can write that in code. Let's do that thing.

Note that purely for the sake of example this code has been written to display in each cell a single field from a MySQL result set. If your needs differ, you'll need to change this to suit whatever data source you're actually using. Ideally, the table-generation code should be independent of the data-collection code.

// We want three columns:
$display_columns = 3;

// We do the query, and find out how many items we'll need to list.
$result = mysql_query($sql);
$count = mysql_numrows($result);

// We have enough to figure out how many empty spaces there'll be left at
// the bottom of the table.
$padding = ($display_columns-1)-(($count-1)%$display_columns);
//
// We also know enough to know how many rows there'll be, but I for one
// don't really care about that right now.

echo '<table>';
// Let's begin our loop.
for($i=0; $i<$count; $i++)
{	// Whenever $i is a multiple of $display_columns (including 0),
	// it's a sign that we're hitting the start of a new row.
	if($i%$display_columns == 0)
		echo '<tr>';
	//Now for an item.
	echo '<td>';
	echo mysql_result($result,$i,'value');
	echo '</td>';
	//Whenever $i is one less than a multiple of $display_columns,
	// it's a sign that we're hitting the end of the current row.
	if($i%$display_columns == $display_columns-1)
		echo '</tr>';
}
// That's all the items done; now we just need to pad the table out and
// wrap up. We don't need to pad at all if $padding is zero.
if($padding!=0)
{	for($i=0;$i<$padding;$i++)
		echo '<td></td>';
	echo '</tr>';
}
echo '</table>';

xsfssg

Weedpacket,

Kind of new to PHP, so forgive me if I sound ignorant.

Do we need to specify a 'value' in line 24?

Could that be why I received a "Parse error: parse error, unexpected T_IS_EQUAL on line 28"?

Thanks,
xsfssg

Weedpacket

Two typos on my part.
my_result() should be mysql_result(); 'value' is the name of the field whose contents you want to display.

There is an extra ) on line twenty-eight (the one on the left).

Weedpacket

The previous lists the items in rows - left-to-right then top-to-bottom. To list them in columns - top-to-bottom then left-to-right - we do need to know how many rows there are, so that we know when to switch from filling one column to the next.

Again, assuming three columns:

items  rows  a=items+2 b=a%3 c=a-b c/3=rows
 0     0      2         2     0       0
 1     1      3         0     3       1
 2     1      4         1     3       1
 3     1      5         2     3       1
 4     2      6         0     6       2
 5     2      7         1     6       2
 6     2      8         2     6       2
 7     3      9         0     9       3
 8     3     10         1     9       3
 9     3     11         2     9       3
10     4     12         0    12       4

The equation rows=(items+2-(items+2)%3)/3 simplifies a bit to rows=1+(items-1-(items-1)%3)/3. It might not look like a simplification, but remember that we might want something other than three columns, so by rights the 2s in the former equation should have been written as 3-1.

Now, when we pull the data out of the result set, it's not going to come out in quite the order we'd like. The first row's worth of data is scattered at widely-spaced points in the array. The first element is result 0, the second is result $rows, the third in $rows*2, etc. And there are up to $display_columns-1 empties in the last column.

Let's make several arrays for the data - one for each column.

// We want three columns:
$display_columns = 3;

// We do the query, and find out how many items we'll need to list.
$result = mysql_query($sql);
$count = mysql_numrows($result);

///We'll want this figure fairly often, so let's save ourselves some work by
//calculating it just the once:
$countm1 = $count-1;

// We have enough to figure out how many empty spaces there'll be left at
// the bottom of the table.
$padding = $display_columns-1-($countm1%$display_columns);

// We also know enough to know how many rows there'll be.
$display_rows = 1+($countm1-$countm1%$display_columns)/$display_columns;

// $all_columns will be an array of arrays - one for each column.
$all_columns = array();

// We'll use $current_column to keep track of which element of
// $all_column's we're filling at the moment.
$current_column=-1; // First thing we'll do in the loop is change this to 0.
                    // It's kind of a cheap trick; by pretending that we've
                    // just filled "column -1", we can advance to filling
                    // column 0 in exactly the same way as we advance to filling
                    // any other column.

// Now let's start sucking down our results.
for($i=0; $i<$count; $i++)
{	// Whenever $i is a multiple of $display_rows, we start a new
	// column array.
	if($i%$display_rows==0)
		$current_column++;

//The usual "Let's push elements onto an array" deal.
$all_columns[$current_column][]=mysql_result($result,$i,'field');
}

// Done filling the columns. Now let's output them.

// A nested pair of loops to run through all but the last $padding
// elements in each of the columns except the last (which we'll run
// through completely, as it has $padding fewer elements than the
// others). We leave the last $padding elements for later, so that we
// can handle the last column specially.
echo '<table>';
for($i=0; $i<$display_rows-$padding; $i++)
{	echo '<tr>';
	for($j=0; $j<$display_columns; $j++)
	{	echo '<td>',$all_columns[$j][$i],'</td>';
	}
	echo '</tr>';
}

// Handle the last $padding rows specially, by ignoring the last column.
for(/*$i already has the correct value*/; $i<$display_rows; $i++)
{	echo '<tr>';
	for($j=0; $j<$display_columns-1; $j++)
	{	echo '<td>',$all_columns[$j][$i],'</td>';
	}
	// Empty padding cell.
	echo '<td></td>';
	echo '</tr>';
}
echo '</table>';

There are all sorts of alterations and possibilities for optimisation in the above code. For example, I could have made up the number of elements to display to a nice round multiple of $display_rows by pushing empty strings onto the last array - then I wouldn't have needed to mention $padding again, or handle padding cells differently. But you might want to handle them differently - a different-coloured background, say, such as in a calendar where you want to distinguish between a cell that is empty because there's nothing listed for that day and a cell that is empty because it doesn't represent a day. An even more obvious optimisation is if you're not interesting in making the number of columns variable: if $display_columns is always ever going to be 3, you could replace $display_columns by 3 wherever it appears, and then simplify the result.

LordRogaine

I must be setting up my select statement incorrectly because I keep getting this error message -- Warning: mysql_result(): Bad column offset specified. I'm trying to use the first example and I'm using this to call up the usernames of nine people. Three rows of three different sets of information.

$sql ="SELECT username FROM profile_table limit 0,9";

Weedpacket

LordRogaine wrote:
Warning: mysql_result(): Bad column offset specified.

mysql_result($result,'value',$i);

Should be

mysql_result($result,$i,'value');

My bad; I don't use MySQL that much (and mysql_result even less) and I don't cut-and-paste code without reading through and checking it first. I've fixed the originals.

LordRogaine

thank you!

adaniels11

Can weedpacket contact re this code? I'd like to display my shop items 3 per row but do not know how to integrate your code with mine. Can you help? you ahve access to my account details I beleive - I am unable to send you a message.

Thanks

Weedpacket

I've had a number of people ask me how to "integrate" this code with what they are doing.

Really, the answer is "you don't". Ideally, the code that generates the table should be separate from the code that collects the data. All that they should have in common is an agreement about how the data is supposed to look. For a simple table like this, a simple array should suffice. More complex tables would need more complex table-generation code, but that's programming for you.

Here's a variation on the original table code that illustrates this. To make it even more general-purpose, the job of rendering an individual table cell has been separated out from the job of rendering the whole table. Now the table just needs to expect an array: it doesn't care what the elements of the array look like - that's the responsibility of the table cell renderer. Using object-oriented techniques, different table cell renderers can be swapped in and out for different tables - if the elements of the array were objects then those objects each have their own cell rendering method - and the table generator here would never notice.

For the purposes of this example, the array being passed in will be numerically indexed and contain associative arrays with 'title', 'link', and 'image' elements. These will be used in the table cell to provide an image link.

function render_table($records, $display_columns=3)
{
	$padding = ($display_columns - 1) - (($count - 1)&#37;$display_columns);
	echo '<table>';
	for($i=0; $i<$count; $i++)
	{
		if($i % $display_columns == 0)
			echo '<tr>';
		echo '<td>';
		render_cell($records[$i]);
		echo '</td>';
		if($i % $display_columns == $display_columns - 1)
			echo '</tr>';
	}
	if($padding != 0)
	{
		for($i=0; $i<$padding; $i++)
			echo '<td></td>';
		echo '</tr>';
	}
	echo '</table>';
}


function render_cell($record)
{
	$link = $record['link'];
	$title = $record['title'];
	$image = $record['image'];
	echo "<a href='$link'><img src='$image' title='$title' alt='$title' /></a>";
}

And if you're still wanting how to get the array of records from a MySQL query:

while($record = mysql_fetch_assoc($resultset))
	$records[] = $record;

or, better,

$records = $db->fetchAll(PDO::FETCH_ASSOC);

Weedpacket

I've had a number of people ask me how to "integrate" this code with what they are doing.

Here's a variation on the original table code that illustrates this. To make it even more general-purpose, the job of rendering an individual table cell has been separated out from the job of rendering the whole table. Now render_table just needs to expect an array: it doesn't care what the elements of the array look like - that's the responsibility of the render_cell. Using object-oriented techniques, different table cell renderers can be swapped in and out for different tables - if the elements of the array were objects then those objects each have their own cell rendering method - and the table generator here would never notice.

Array
(
    [0] => Array
        (
            [title] => Grownups
            [link] => http://www.xkcd.com/150/
            [image] => http://imgs.xkcd.com/static/grownups_icon.png
        )

[1] => Array
    (
        [title] => Centrifugal Force
        [link] => http://www.xkcd.com/123/
        [image] => http://imgs.xkcd.com/static/centrifugal_force_icon.png
    )

[2] => Array
    (
        [title] => Angular Momentum
        [link] => http://www.xkcd.com/162/
        [image] => http://imgs.xkcd.com/static/angular_momentum_icon.jpg
    )

[3] => Array
    (
        [title] => Useless
        [link] => http://www.xkcd.com/55/
        [image] => http://imgs.xkcd.com/static/useless_icon.png
    )

[4] => Array
    (
        [title] => Bored with the Internet
        [link] => http://www.xkcd.com/77/
        [image] => http://imgs.xkcd.com/static/bored_with_the_internet_icon.jpg
    )

)

function render_table($records, $display_columns=3)
{
	$padding = ($display_columns - 1) - (($count - 1)%$display_columns);
	echo '<table>';
	for($i=0; $i<$count; $i++)
	{
		if($i % $display_columns == 0)
			echo '<tr>';
		echo '<td>';
		render_cell($records[$i]);
		echo '</td>';
		if($i % $display_columns == $display_columns - 1)
			echo '</tr>';
	}
	if($padding != 0)
	{
		for($i=0; $i<$padding; $i++)
			echo '<td></td>';
		echo '</tr>';
	}
	echo '</table>';
}


function render_cell($record)
{
	$link = $record['link'];
	$title = $record['title'];
	$image = $record['image'];
	echo "<a href='$link'><img src='$image' title='$title' alt='$title' /></a>";
}

And if you're still wanting how to get the array of records from a MySQL query:

while($record = mysql_fetch_assoc($resultset))
	$records[] = $record;

or, better,

$records = $db->fetchAll(PDO::FETCH_ASSOC);

But this thread isn't about how to generate HTML, or get results out of a database query or any of that. This thread is about displaying three or four items per row in a table. Letting different tasks get all mixed together into one big ball of mud is a great way of getting into trouble. (Funnily enough, I just went back and looked at the original post, and noticed that I had said "Ideally, the table-generation code should be independent of the data-collection code." That's still true.)