PHP loops How to output database results horizontally in html tables

Question

I am building a reporting application using PHP/MySQL that outputs a data set based on selected date ranges.Below is a sample of the the data in the database

date           denom     net 
2014-02-02      5        220,410
2014-02-02      11       218,460
2014-02-02      16       81,810
2014-02-02      22       51,140
2014-02-02      33       69,900

2014-02-03      5        220,410
2014-02-03      11       200,460
2014-02-03      16       71,810
2014-02-03      22       11,140
2014-02-03      33       19,900

I would like the data to be outputted horizontally using php to something like this

denom               02nd Feb           03rd Feb      
5                    220410            200,199
11                   218,460           200,460
16                   81,810            71,810
22                   51,140            11,140
33                   69,900            19,900

I have attempted to use a while loop to achieve this but my output is not arranged as it should be above. See the php i used below

$denom_netresult = mysqli_query($con,"SELECT date,denom,net 
                                      from tbl_lending 
                                      WHERE date BETWEEN '2014-02-02' 
                                                     AND '2014-02-03'");

while($row = mysqli_fetch_array($denom_netresult))
{
    echo "<tr>";
    echo "<td>" . $row['date'] . "</td>";
    echo "<td>" . $row['denom'] . "</td>";
    echo "<td>" . $row['net'] . "</td>";
 }
 echo "</tr>";

Please assist.

First of all, where is your tag ? Second: close row () should be inside loop. — dgierejkiewicz, Jul 30 '14 at 09:10
First of all change the query so it orders the data in the result set in a way you can process it. Add `ORDER BY denom,date`. — RiggsFolly, Jul 30 '14 at 09:20

score 3 · Answer 1 · answered Jul 30 '14 at 09:07

3

That's because you open the row inside the loop, but you close it outside the loop. Your code should (more or less) look like this:

$denom_netresult = mysqli_query($con,"SELECT date,denom,net from tbl_lending WHERE date BETWEEN '2014-02-02' AND '2014-02-03'");

echo '<table>';
while($row = mysqli_fetch_array($denom_netresult))
{
    echo "<tr>";
    echo "<td>" . $row['date'] . "</td>";
    echo "<td>" . $row['denom'] . "</td>";
    echo "<td>" . $row['net'] . "</td>";
    echo "</tr>";
}   
echo '</table>';

answered Jul 30 '14 at 09:07

giorgio

10,111
2
28
41

Your columns are not what he asked for. The second column should be `net` for `Feb 2`, the third column should be `net` for Feb 3`. – Barmar Jul 30 '14 at 09:13
I believe the query needs changing, as the requirements are to group data with the same `denom` value – Alex Jul 30 '14 at 09:18

PHP loops How to output database results horizontally in html tables

1 Answers1