Need help with displaying mysql data.

[iPixel]

So i pull some records out of a mysql table and i want to display them in 5 even columns.

I'm not entirely sure how to do the math & logic to accomplish this.

 

The pull is simple

$qry = "SELECT DIST_PART_NUM FROM $tablename";
$sql = mysql_query($qry) or die(mysql_error());
while($res = mysql_fetch_assoc($sql))
{
	// CREATE 5 even columns here.
}

 

so let's say i just retrieved 5,000 part numbers, i'd like to display then in a table of 5 columns with 1000 records per column. This is easy math, but i need the script to automatically figure out the #'s.

 

Also the tricky part is that i dont want to display the part numbers like so

 

11111    22222          33333        44444          55555

66666    77777          88888        99999          00000

 

but rather

 

11111    44444    77777

22222    55555    88888

33333    66666    99999

                              00000

the remainder if there is one can go in the last column or whatever is easier.

 

I'd tried googling this, but it's not easy to phrase what i'm looking for.

 

 

Thanks for the help.

 

PS: I'm not looking to copy and paste code, if possible please explain your way so that i can learn the logic.

[Jessica]

You need to get the count() of the array, and then divide by how many columns you want. That gives you how many per column.

 

If you're still stuck after this part, post code and we'll go from there.

[iPixel]

My main issue with diplaying it is.. if i were going from left to right that would be easy.. but i have to display from left to right through code but not visually. it's like first display the 1st record, then the 1001st then 2001st etc... now 2nd row, do 2, 1002, 2002, etc... that confuses me.

[Jessica]

You need to get the count() of the array, and then divide by how many columns you want. That gives you how many per column.

 

If you're still stuck after this part, post code and we'll go from there.

[iPixel]

$qry = "SELECT DIST_PART_NUM FROM $tablename";
echo $qry . "<BR><BR>";
$sql = mysql_query($qry) or die(mysql_error());
$res = mysql_fetch_assoc($sql);
$cnt = mysql_num_rows($sql);

echo $cnt;

 

so now i have $cnt which is 5,000 let's say.

so $rows = $cnt/5;

 

it's the display that kills me.

[Jessica]

So count is 1000 right? So that means 1-1000 go in one column. The next column would then start with result number.... what?

[litebearer]

Some psuedo code to consider (needs some 'brushing up' for handling empty cells in last row if there are any) ...

(look here for controlling displays per page - http://www.nstoia.com/sat/disp_pag/)

x = 1;

loop till all records are done

  row cell1 data[x] cell2 data[x+1] cell3 data[x+2] cell4 data[x+3] cell5 data[x+4] end row

  x = x + 5;

end loop

 

 

[Psycho]

Do you need each record to go in it's own TD tags or can all of column one go in a TD tag, all of column 2 into another, etc.? The latter is much simpler, but the former isn't all that much more work.

[Jessica]

Some psuedo code to consider (needs some 'brushing up' for handling empty cells in last row if there are any) ...

(look here for controlling displays per page - http://www.nstoia.com/sat/disp_pag/)

x = 1;

loop till all records are done

  row cell1 data[x] cell2 data[x+1] cell3 data[x+2] cell4 data[x+3] cell5 data[x+4] end row

  x = x + 5;

end loop

 

 

 

That will put them all in a horizontal row.

 

You need to do x+1000, x+2000, etc, to get the vertical rows.

[iPixel]

preferably 1 td per record... i can see where litebearer was going with the array idea... i'm trying something with that now. i'll post some code in a bit if i hit a wall or manage.

 

To the post above::: it wont always be 1000 so it should be +$variable.

 

[Jessica]

To the post above::: it wont always be 1000 so it should be +$variable.

 

Which is what my first few posts told you...

[gizmola]

This is easy to do with html tables. 

 

-You start an outer table.

-You set a counter variable $x = 1.

-you set a column variable $c = 1;

-start your fetch loop

- if $x == 1 then emit a

-emit a

-if $x == $rows (your rows per column variable calculated above)  AND $c

--- $c++

--- $x = 1

--- emit a

rowdata

to close that nested table and column.

- $x++

When the loop finally exits because all data has been fetched you have to close the nested table, last column, the tr and the outer table.

 

--

 

You'll have the exact format you wanted, and overflows will go into the last nested column/table , exactly as you mocked it up.

[iPixel]

To the post above::: it wont always be 1000 so it should be +$variable.

 

Which is what my first few posts told you...

 

yes yes... i meant that in a more question like manner not a corrective one as it seemed.

Thanks for the help guys/gals i'm going at it now, seems feasable.

[Psycho]

<?php
//Create variable for the number of columns to use
$max_columns = 5;

//Create and run query
$query = "SELECT DIST_PART_NUM FROM $tablename";
$result = mysql_query($query) or die(mysql_error());
//Put results into an array
$records = array();
while($rec = mysql_fetch_assoc($result))
{
    $records[] = $rec;
}

//Determine rows needed
$max_rows = ceil(count($records)/$max_columns);
  
//Create the ouput
$outout = '';
for($row = 0; $row < $max_rows; $row++)
{
    //Open row
    $recordsHTML .= "<tr>\n";
    for($col =0; $col<$max_columns; $col++)
    {
        //Determine value from array to use for this row/column
        $idx = $row+($col*$max_rows);
        $value = (isset($records[$idx])) ? $records[$idx] : '';
        $recordsHTML .= "<td>$value</td>\n";
    }
    //Close row
   $recordsHTML .= "<tr>\n";
}

?>
<table>
<?php echo $recordsHTML; ?>
</table>

 

EDIT: Fixed, but the last row will be used for all "offsets"

[iPixel]

Voila!

 

$cols = 5;
$partnumbers = array();

$qry = "SELECT DIST_PART_NUM FROM $tablename";
$sql = mysql_query($qry) or die(mysql_error());
$cnt = mysql_num_rows($sql);
while($res = mysql_fetch_assoc($sql))
{
    array_push($partnumbers,$res['DIST_PART_NUM']);
}

#print_r($partnumbers);

//Figure out rows per column
$rows = ceil($cnt / $cols);

echo '<center><table border="0" width="100%" cellspacing="1">';
for ($r=0; $r < $rows; $r++) 
{
	echo '<tr>';
	for ($c=0; $c < $cols; $c++) 
		{	
			$x = $r + $c * $rows;
			if ( $x < $cnt)
				{
			 	echo '<td align="left">';
				echo $partnumbers[$x];
				echo '</td>';
				}
			else
				{
			 	echo '<td> </td> ';
				}
		}
    	echo '</tr>';
}
echo '</table></center>';
?>

 

Thanks for the help!!!

[Psycho]

Putting the "structure" (i.e. the HTML output) in both the IF and ELSE conditions is not wise, IMHO. Because if you ever need to change the display you have to remember to change both. This will take care of that as well as make your code a little neater

    $x = $r + $c * $rows;
    if ( $x < $cnt)
    {
        $value = $partnumbers[$x];
    }
    else
    {
        $value = ' ';
    }
    echo "<td align=\"left\">{$value}</td>\n";