Display data from a table

[Jamesnl1]

Hi!

 

I'm new at PHP and I'm trying to display data from a table but this is more complex than I initially thought.

 

I have the following table:

 

id | Day | Name | Donate
-----------------------------------------------------
1 | 2014-02-10 | John | 100
2 | 2014-02-10 | Peter | 200
3 | 2014-02-10 | Chris | 30
4 | 2014-02-11 | John | 0
5 | 2014-02-11 | Peter | 99
6 | 2014-02-11 | Chris | 10
7 | 2014-02-12 | John | 80
 

 

I want to display this data like this with only the last 5 days:

 

Name | 2014-02-10 | 2014-02-11 | 2014-02-12
------------------------------------------------------------
John | 100 | 0 | 80
Peter | 200 | 99 | 0
Chris | 30 | 10 | 0

I have no idea where to start, this is what I have so far...

        $query = "SELECT day FROM stats GROUP BY day DESC LIMIT 5";

        $result = mysqli_query($link, $query);

        while ($row = mysqli_fetch_array($result, MYSQL_ASSOC)) {

        /* fetch associative array */
        while ($row = mysqli_fetch_array($result)) {


           echo '<th scope="col">'.$row["day"].'</th>';


           }

Any help to get me in the right direction would be appreciated.

 

[jazzman1]

Then GROUP BY name and order by day DESC limit 5. Have you tried?

[Jamesnl1]

When I try:

 

SELECT * FROM stats GROUP BY day, name ORDER BY day DESC LIMIT 5

 

or

 

SELECT * FROM stats GROUP BY name ORDER BY day DESC LIMIT 5

 

I get multiple dates so this doesn't seem to work...

Edited February 3, 2014 by Jamesnl1

[jazzman1]

Try to explain us again what exactly result do you want to get above and tell us about whole story as well, because I'm missing something

[Barand]

the method will be very similar to this

http://forums.phpfreaks.com/topic/285398-pivot-table-or-cross-tab-in-php-using-mysql-for-attendance/?do=findComment&comment=1465773

[Jamesnl1]

Ok I'll give it a try

 

Some days people do donations to our club and some days they don't. A club member is responsible for inserting these donations (using an existing form on a webpage) and it gets stored in a table. This table contains 4 fields (id, day, name, donate).

 

Now we want to know how much someone is donating on a weekly (5 days) basis so I'm trying to set up a webpage that displays a table like this:

 

[Jamesnl1]

Well I fixed the problem with the table header by using:

 

$query = "SELECT DISTINCT day FROM stats ORDER BY day LIMIT 1,6";

 

So the headers look like fine now but I'm stuck on the rest of the table

[Barand]

I'd get the latest date then generate the previous days in case there is a date in the table with no donations.

 

Something like this

<?php
$db = new mysqli(HOST,USERNAME,PASSWORD,'pascal'); // use your credentials

/***********************************
* Generate last five dates         *
* in case there are missing dates  *
* in the data (no donations)       *
************************************/
$sql = "SELECT MAX(day) FROM stats";
$res = $db->query($sql);
list($d) = $res->fetch_row();

$d1 = new DateTime($d);
$d1->modify('-4 days');
$dp = new DatePeriod($d1, new DateInterval('P1D'), 4);

/***********************************
* Generate table date headings     *
* and create and empty array whose *
* keys are the dates               *
************************************/
$heads = "<tr><th>Name</th>";
foreach ($dp as $d) {
    $heads .= "<th>" . $d->format('jS M') . "</th>";
    $emptyArray[$d->format('Y-m-d')] = '';
}
$heads .= "</tr>\n";

/***********************************
* Generate table data              *
************************************/
$sql = "SELECT day, name, donate 
    FROM stats
    WHERE day > CURDATE()-INTERVAL 5 DAY
    ORDER BY name";
$res = $db->query($sql);
$curname = '';
$tdata = '';

while (list($day, $name, $donate) = $res->fetch_row()) {
    // is it a new name
    if ($name != $curname) {
        if ($curname) {
            $tdata .= "<tr><td>$curname</td><td>" . join('</td><td>', $donations) . "</td></tr>\n";
        }
        $curname = $name; // reset prevous name
        $donations = $emptyArray; // reset array for next name
    }
    if (isset($donations[$day]))
        $donations[$day] = $donate; // store the donation in the array
}
// process last name
$tdata .= "<tr><td>$curname</td><td>" . join('</td><td>', $donations) . "</td></tr>\n";

?>

<html>
<head>
<style type="text/css">
    table {
        border-collapse: collapse;
    }
    
    th,td {
        padding: 4px;
    }
    th {
        background-color: black;
        color: white;
    }
</style>
</head>
<body>
    <table border='1'>
        <?php
            echo $heads, $tdata;
        ?>
    </table>
</body>
</html>