mysql php columns
An answer to this question on Stack Overflow.
Question
<?php
$say = array("ann","brenda","charles","david",
"edward","florence","geoff","harry",
"ingrid","james","kelly","liam");
$columns = 5;
for ($p=0; $p<count($say); $p++) {
// Start of table or line?
if ($p==0) { // Start of table
print "<table border=0><tr>";
} elseif ($p%$columns == 0) { // Start of row
print "<tr>";
}
print "<td>".htmlspecialchars($say[$p])."</td>";
// End of table or line?
if (($p+1)%$columns == 0) { // End of row
print "</tr>";
}
if ($p==count($say)-1) { // End of table
$empty = $columns - (count($say)%$columns) ;
if ($empty != $columns) {
print "<td colspan=$empty> </td>";
}
print "</tr></table>";
}
}
?>
The result:
ann brenda charles david edward
florence geoff harry ingrid james
kelly liam
I'm trying to do the same with mysql so far i got
<?php
$con = mysql_connect("localhost","root","lol");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("test", $con);
$result = mysql_query("SELECT * FROM test");
while($row = mysql_fetch_array($result))
{
$id=$row['id'];
$nam=$row['nam'];
$columns = 3;
for ($p=0; $p<count($id); $p++) {
// Start of table or line?
if ($p==0) { // Start of table
print "<table border=0><tr>";
} elseif ($p%$columns == 0) { // Start of row
print "<tr>";
}
print "<td>".$nam."</td>";
// End of table or line?
if (($p+1)%$columns == 0) { // End of row
print "</tr>";
}
if ($p==count($nam)-1) { // End of table
$empty = $columns - (count($nam)%$columns) ;
if ($empty != $columns) {
print "<td colspan=$empty> </td>";
}
print "</tr></table>";
}
}
}
mysql_close($con);
?>
Result:
ann
brenda
charles
david
edward
florence
geoff
harry
ingrid
james
kelly
liam
Question: what's wrong?
Dabase table
id nam
1 ann
2 brenda
3 charles
4 david
5 edward
6 florence
7 geoff
8 harry
9 ingrid
10 james
11 kelly
12 liam
Answer
I'd suggest that you split your code into two distinct functions.
One function will read information from the database or the array, the other will format the output.
Right now, it looks an awful lot like you took your first chunk of code and put it into the middle of the while loop in the second piece.
MySQL is returning results to you, one result row at a time. So what you should do is collect all those results first and then print them out second (either that, or make a counter on the number of rows returned). In your second piece of code, you're treating each result row as you were the entire array of results in the first piece.
That is, the line while($row = mysql_fetch_array($result)) returns a single row from the table.
Because of this, the line $id=$row['id']; does not assign an array to $id.
Because of this, the line for ($p=0; $p<count($id); $p++) { iterates over a single item, resulting in what you're seeing.
My code still looks a little hackish, but it may give you an idea. I'm afraid I haven't tested it.
print "<table><tr>";
$p=0;
$columns=3;
while( $row = mysql_fetch_array($result) ) {
if ( $p>0 && ($p % $columns)==0 )
print "</tr><tr>";
print "<td>{$row['nam']}</td>";
$p++;
}
for(true;($p % $columns)!=0;$p++) //Finish off $p from above
print "<td> </td>";
print "</tr></table>";
To do this in a more modular way:
function display($stuff,$cols){
//Make sure the table is some multiple of $cols to eliminate special cases
//Hackish
while( (count($stuff) % $cols)!=0 )
$stuff.push_back(" ");
//Start table and first row, eliminating another special case
print "<table><tr>";
for($i=0;$i<count($stuff);$i++){
if($i>0 && ($i % $cols)==0)
print "</tr><tr>";
print "<td>{$stuff[$i]}</td>";
}
print "</tr></table>";
}
$names=array()
while( $row=mysql_fetch_array($result) )
$names.push_back($row['nam']);
display($names,5);