[RESOLVED] Sorting query results in a table ...

[jme]

Hiya guys,

I have a query which I want to format into a table in the following format:

[ item 1 ][ item 2 ]
[ item 3 ][ item 4 ]
etc ...

However, all I can get is

[ item 1 ][ item 1 ]
[ item 2 ][ item 2 ]
etc ...

Could someone please point me in the right direction.

Many thanks 🙂

[paulnaj]

Hi there,

Here's a pretty fail-safe method ...

<?php 
// Assuming you have some kind of array
$items = array('item 1', 'item 2', 'item 3', 'item 4', 'item 5', 'item 6', 'item 7', 'item 8', 'item 9');

// You can choose any number here
$column_count = 2;


// Fix if you don't have an exact multiple of $column_count
$item_count = count($items);
$row_count = ceil($item_count/$column_count);
$blank_count = $row_count*$column_count - $item_count;

for($i = 0; $i < $blank_count; $i++){
	$items[] = '&nbsp;';
}

?>
<table border="1" cellpadding="0" cellspacing="0">
<?php
for($i = 0; $i < count($items); $i++){
	if(($i % $column_count) == 0)	echo '<tr>';
	echo '<td>'.$items[$i].'</td>';
	if(($i % $column_count) == $column_count - 1)	echo '</tr>';
}
?> 
</table>

Come back if you don't understand anything.

Paul 🙂

[Jack_McSlay]

well there's an aswer already so I'll explain your error... you're only passing from item to item on each row, that way it will display the same item on all colums of each line. you need to do a double check to make a proper table, here's a mysql solution for you

$num_cols = 5; //the number of columns you want
$sql = 'SELECT * FROM table';//or whatever query you're using

$result = mysql_query ($sql);

echo '<table>';
$x = 0;
do {
   echo '<tr>';
   for ($y=0;$data = mysql_fetch_assoc($result) and $y < $num_cols;$y++){
      $index = $x*$num_cols+$y;
      echo "<td>print your data here</td>";
   }
   echo '</tr>';
   $x++;
}
while ($data);
echo '</table>'

[jme]

Jack McSlay wrote:

well there's an aswer already so I'll explain your error... you're only passing from item to item on each row, that way it will display the same item on all colums of each line. you need to do a double check to make a proper table, here's a mysql solution for you

$num_cols = 5; //the number of columns you want
$sql = 'SELECT * FROM table';//or whatever query you're using

$result = mysql_query ($sql);

echo '<table>';
$x = 0;
do {
   echo '<tr>';
   for ($y=0;$data = mysql_fetch_assoc($result) and $y < $num_cols;$y++){
      $index = $x*$num_cols+$y;
      echo "<td>print your data here</td>";
   }
   echo '</tr>';
   $x++;
}
while ($data);
echo '</table>'

Thanks for this code. I have enployed it and it seems to be working just as expected, however it's not showing all of the results. I have tried to work out why but I can't.

Basically, if the SQL query returns 12 results, it will only show 8 in the html; where it should return 4 results it only displays 3...

Can anyone think why this could be ... am I just missing it?

Many thanks, Jamie 🙂

[paulnaj]

How many columns are you using ... $num_cols = ??

Paul

[jme]

2 🙂

[paulnaj]

Hmm,

Can't see why straightaway and can't really be bothered to debug it ... especially as the way I showed you how to do it in the first place is straightforward and fool-proof.

P.

[jme]

I did try using the script that you provided but I couldn't get it working. Sorry for sounding stupid.

It the

$items = array('item 1', 'item 2', 'item 3', 'item 4', 'item 5', 'item 6', 'item 7', 'item 8', 'item 9'); 

part that is confusing me. I am using a MySQL query and want the results of this to be put into the table that I'm creating.

$query1 = "SELECT cat_name FROM fps_categories WHERE cat_parent = {$_GET['id']}";  

$result1 = mysql_query ($query1);

Would you be able to clarify where I am going wrong? Many thanks for your help.

Jamie 🙂

[paulnaj]

Yeah, maybe I sort of assumed you'd know how to adapt it ... and it can look different when you're fetching data from a db at the same time as actually printing out the table.

In the meantime, here's an even simpler way. The crucial things are to calculate the number of rows needed in the table and include the blank tds (which is what I was doing in the first example, but in a more general way).

Anyway, ...

<?php  

// ...
// You can choose any number here (I've changed it to
// uppercase to signify a constant)
$COLUMN_COUNT = 2; 

$query1 = "SELECT cat_name FROM fps_categories WHERE cat_parent = {$_GET['id']}";   

$result1 = mysql_query ($query1);  

// Get the number of results returned by the query
$item_count = mysql_num_rows($result1); 

// Get the number of rows in the table
$row_count = ceil($item_count/$COLUMN_COUNT); 

echo '<table border="1" cellpadding="0" cellspacing="0">';
for($r = 0; $r < $row_count; $r++){ 
    echo '<tr>'; 
	for($c = 0; $c < $COLUMN_COUNT; $c++){ 
		if(($item = mysql_fetch_assoc($result1)) !== false){
	    	echo '<td>'.$item['cat_name'].'</td>'; 
		} else {
			// If mysql_fetch_assoc($result1) returns false, it means
			// we've come to end of our results, but we might still
			// have some blanks tds to print to complete the row
		    echo '<td>&nbsp;</td>'; 
		}
    }
	echo '</tr>'; 
} 
echo '</table>';
?>

Come back if you don't understand anything or if I've glitched something.

P.

[jme]

That has worked perfectly! 🙂

Thank you so much for your help!