loop to populate html table vertically

Question

I have a mysql query that returns an array of rows. How would i populate my html table using php vertically? there is no limit to how many columns i my HTML table allowed.

My MYSQL query returns about 40 columns per row.

MYSQL row1 =>  10|11|12|13|14|15|16|17|18|19
      row2 =>  20|21|22|23|24|25|26|27|28|29
      row3 =>  30|31|32|33|34|35|36|37|38|39

HTML output should look like this

   10 | 20 | 30
   11 | 21 | 31
   12 | 22 | 32
   13 | 23 | 33
   14 | 24 | 34
   15 | 25 | 35
   16 | 26 | 36
   17 | 27 | 37
   18 | 28 | 38
   19 | 29 | 39

this is my code, and it's displaying nothing.

$values = array();

$sql = "SELECT * FROM `TABLE_NAME` ORDER BY `id` ASC LIMIT 0,12";
$result = $_db->query($sql);

$numrows = $_db->num_rows($result);

$c = 1;
while ($c <= $numrows)
{
    $values['col_'.$c] = array(); 
    $c++;
}

$r = 1;

while ($row = $_db->fetch_array($result)) 
{
    $values['col_'.$c][$r] = $row;

    $r++; 
}
echo "<table border='1'>";


for ($r = 1; $r <= $numrows; $r++) 
{
    echo "<tr>";
    for ($c = 1; $c <= sizeof($values['col_1']); $c++) 
    { 
        echo "<td>".$values['col_'.$c][$r]."</td>"; 
    }
    echo "</tr>" ;
}
echo "</table>" ;

Any idea what i'm doing wrong? or how to make it simpler? (i think there are too many while loops)

Just to clarify: A row from your mysql table contains the data for 1 column? — Sumurai8, Jul 12 '13 at 06:35
nope, 1 row returns 30 columns. and i want to display the 30 columns vertically. — srakl, Jul 12 '13 at 06:43
In your example output, 10 till 19 are in 1 column (and 10 rows) of your html table. Is this data (10 till 19) in 1 row of your MYSQL table, or 10 rows of your MYSQL table? — Sumurai8, Jul 12 '13 at 06:52
`code`$numrows = $_db->num_rows($result); //check here if ($numrows > 0) { //1 row $r = 1; while ($row = $_db->fetch_array($result)) { //value row column $values[$r] = $row; //increase row $r++; } echo ""; //display row and columns $c = 0; foreach ($values as $row) { echo ""; for ($r = 1; $r <= sizeof($row); $r++) { echo ""; } echo ""; $c++; } echo "
".$values[$r][$c]."
"; }`code` — srakl, Jul 12 '13 at 07:20
it still doesn't display ALL columns from the mysql query. it does display ALL rows from the mysql query tho — srakl, Jul 12 '13 at 07:21

score 4 · Accepted Answer · edited May 23 '17 at 10:26

4

I think what you want is creating the php array from the mysql query, transposing the array (like you would transpose a matrix) and display it.

Transposing an array is a solved problem (transposing multidimentional arrays in php)

For the rest, it is pretty simple ... here is my code:

$res = mysqli_query(...);

$anarr = array();

while ($row = mysqli_fetch_array($res,$result_type=MYSQLI_ASSOC)){

    $anarr[] = $row;

}

// here is the transpose part
array_unshift($anarr, null);
$transposedarr = call_user_func_array('array_map', $anarr);
// end of the transpose part

echo '<table>';

foreach ($transposedarr as $r){
    echo '<tr>';
    foreach ($r as $c){
        echo '<td>'.$c.'</td>';
    }
    echo '</tr>';
}

echo '</table>';

?>

edited May 23 '17 at 10:26

Community

1
1

answered Jul 12 '13 at 07:46

Julien Spronck

15,069
4
47
55

thank you! what does call_user_func_array() do? i read it on php.net but don't quite get it. and why array_unshift()? doesn't $anarr[] = $row; add to the array? – srakl Jul 12 '13 at 08:10
as you can see in the link I sent (just copy pasted since its better explained than what i would have come up with): "the first line inserts NULL as the first value of the array. The next line calls array_map with all the entries of $array as the parameters. So it's the same as calling array_map(NULL, $array[0], $array[1], $array[2], etc etc)." see the array_map documentation for how to construct an array of arrays, which can be easily performed by using NULL as the name of the callback function" – Julien Spronck Jul 12 '13 at 08:15
how do i echo the $key of the array before the $c ? my mysql queries results look like this array("name"=> bla, "id" => bla .... ); – srakl Jul 12 '13 at 14:19
so i assume that you want to get the keys corresponding to each row like a row header. – Julien Spronck Jul 12 '13 at 17:24
so i assume that you want to get the keys corresponding to each row like a row header. The keys are lost when you use array_map. So, you need to get them before that. What I would do is $keys = array_keys($anarr[0]) before doing array_unshift(...) and echo them after echo ''. I would add an index before the first loop ($ind = 0), and replace echo ''; by echo ''.$keys[$ind].'';$ind ++; – Julien Spronck Jul 12 '13 at 17:32

score 0 · Answer 2 · answered Jul 12 '13 at 06:20

You are assigning only one row in your while loop. Change that with below code:

while ($row = $_db->fetch_assoc($result)) 
{
    $values['col_'.$c][$r] = $row;
    $c++;
    $r++; 
}

Here you are assigning the value to $value['col_1'][$r] and not increasing the value of $c. So at the end it override the values.

score 0 · Answer 3 · answered Jul 12 '13 at 06:20

You can simplify the problem by just saying

$values[$rowIndex] = $columnArray

So in this case

$values[0] = array( 10, 20, 30 );
$values[1] = array( 11, 21, 31 );

And then loop across each array

echo "<table border='1'>";
foreach( $values as $row )
{
    echo "<tr>";
    foreach( $row as $columnValue )
    {
        echo ..whatever..
    }
    echo "<tr>";
}
echo "</table>" ;

Or something along these lines. I just basically psuedo coded this though, I have no access to php interpreter right now.

Lorenzo Marcon · Answer 4 · 2013-07-12T07:55:23.457

0

You can do everything in a single loop. Additionally, at least for your purposes in this example, I don't understand why you're putting everything in an array and then echo, instead of echoing it directly.

e.g. (tested):

$sql = "SELECT * FROM `TABLE_NAME` ORDER BY `id` ASC LIMIT 0,12";
$result = $_db->query($sql);

echo "<table border='1'>";

$tab = array();
while ($row = $result->fetch_row())
{
    $tab[] = $row;
}

for( $i = 0, $l = count($tab[$i]); $i < $l; $i++){
    echo "<tr>";
    for( $j = 0, $m = count($tab); $j < $m; $j++){
        echo "<td>".$tab[$j][$i]."</td>";
    }
    echo "</tr>" ;
}

echo "</table>";

UPDATE: I completely changed my code. I didn't get initially what you needed.

Does this code help you?

edited Jul 12 '13 at 07:55

answered Jul 12 '13 at 06:21

Lorenzo Marcon

8,029
5
38
63

this displaying every row of the array horizontally. 11|12|1321|22|23 and so on – srakl Jul 12 '13 at 06:39
uhm.. the output should be 11<1213.. and so on.. but I can't test it now. did you try it? for sure it can't be what you said, because there must be also some s – Lorenzo Marcon Jul 12 '13 at 06:49
@user2035819 did you test it? – Lorenzo Marcon Jul 12 '13 at 07:09
yup, still displaying all horizontally. – srakl Jul 12 '13 at 07:32
I changed the code, now it should be what you're looking for.. check it out – Lorenzo Marcon Jul 12 '13 at 07:56

score 0 · Answer 5 · answered Jul 12 '13 at 06:24

//while and foreach loop can do this
<?php 

$values = array();

$sql = "SELECT * FROM `TABLE_NAME` ORDER BY `id` ASC LIMIT 0,12";
$result = $_db->query($sql);

$numrows = $_db->num_rows($result);

//check here
if($numrows>0)
{
    //1 row
    $r = 1;
    //column
    $c=0;

    while ($row = $_db->fetch_assoc($result))
    {
        //value row column  
        $values[$r][$c] = $row; 

        //column == 3
        if($c==2)
        {
            //increase row
            $r++;
            //reset column
            $c = 0;

        }else{

            $c++;
        }
    }
    echo "<table border='1'>";


    //display row and columns
    foreach($values as $row)
    {
        echo "<tr>";
        echo "<td>".$values[0]."</td>";
        echo "<td>".$values[1]."</td>";
        echo "<td>".$values[2]."</td>";
        echo "</tr>" ;
    }
    echo "</table>" ;
}

$value is an $row what you want to print in that $row.. you didn't mention anything.. $value[0]['field_name of your choice]; — Sundar, Jul 12 '13 at 06:44

score 0 · Answer 6 · answered Jul 12 '13 at 08:14

This is how I would tackle it. The most difficult part is preparing the structure in which you prepare the table. It uses the following syntax: $somearray[] = $x, which appends $x to array $somearray. implode() concats an array together with a string you define. Last but not least, you were using mysql_fetch_assoc, which returns an associative array (Array( [column1] => "val1" ); etc). You want a numbered array instead for these kind of operations. This can be accomplished with mysql_fetch_array with a second argument of MYSQL_NUM.

$arrayOfRows = Array();

$sql = "SELECT * FROM `TABLE_NAME` ORDER BY `id` ASC LIMIT 0,12";
$result = $_db->query($sql);

$firstrun = true;
while( $row = $_db->fetch_array($result, MYSQL_NUM) ) {
  #Setup structure on first run
  if( $firstrun ) {
    $firstrun = false;
    for( $i = 0; $i < count( $row ); $i++ ) {
      $arrayOfRows[$i] = Array();
    }
  }

  #Each field in this mysql row needs to be in a different html row
  foreach( $row as $k => $v ) {
    $arrayOfRows[$k][] = $v;
  }
}

#Now simply print it
echo '<table>';

foreach( $arrayOfRows as $k => $row ) {
  echo '<tr>';
  echo '<td>' . implode( '</td><td>', $row ) . '</td>';
  echo '</tr>';
}

echo '</table>';

score -1 · Answer 7 · answered Jul 12 '13 at 06:23

<?php 

$arr = array(array(1,2,3,4,5,6,7,8,9), array(10,11 etc . . .

for($i = 0; $i < 9; ++ $i){

    for($x = 0; $x < $num_rows; ++ $x){

         echo $arr[$x][$i];

    }
    echo '<br/>';

}

?>

U may replace 9 with number of columns in tables

I not shure what it this code correct (can't test now), but i think it can help you Sorry if i do something wrong, i just try to help

loop to populate html table vertically

7 Answers7

Related