Dynamic Table from MySQL

[RaJiska]

Hello,

 

Here is what I would like to do, every weeks, I would like to do a check of who attended a meeting, for that, I would like to do a table in PHP from a MySQL database, the thing is this database would be dynamic, every weeks a new column would create with the week number, the problem, is: how can I show this on a web page dinamically ? I mean, without having to add each time a new line for a new column.

 

Thanks for your help.

[Barand]

If you are continually adding columns to your table and changing the db structure then you are doing it wrong.

 

You need to correctly normalize your data. http://forums.phpfreaks.com/topic/273634-best-way-to-set-up-tables-when-multiple-values/?do=findComment&comment=1408360

[RaJiska]

Thanks for your help,

 

then, what I should do is something like this, right:

 

Date  -  Name  -  Attended

 

23/11 - Roger  -        1

23/11 - Charles -       1

23/11 - Pierre   -        0

24/11 - Steph  -         1

24/11 -  Rath  -          0

etc...

 

Is that like that ?

The problem is I don't really know how to code the table in PHP by doing like that, I don't see how I could create the PHP table dinamically.

[denno020]

If I were to create database tables for this kind of thing, I would create 3 tables.

 

People table - containing information about the People who attend the meetings; name, phone, any other info

Meeting table - which will contain the date of a meeting, maybe some meeting notes or something

Attendance table - which will contain 3 fields, ID (auto-generated, primary key), people_id, meeting_id. So for every person who attends, put their ID into a row, along with the ID of the meeting they attended.

 

Then in PHP, you can perform queries with joins to join the three tables, and have all information about the meetings and who attended, at your disposal, to display on your page however you like.

 

I'm not sure how that fits with the Normalisation that was suggested earlier, but I think it would be a fairly simple and effective way to store the information.

 

Hope that helps

Denno

[Barand]

Almost there Denno

 

However, if you wanted to find who did not attend then you would get a long list including those people who were not due to attend in the first place. You would need to know the invitees to the meetings in addition to attendees.

[denno020]

However, if you wanted to find who did not attend then you would get a long list including those people who were not due to attend in the first place. You would need to know the invitees to the meetings in addition to attendees.

 

Ahh yes, the non-attendees, I forgot about them..

[Barand]

Thanks for your help,

 

then, what I should do is something like this, right:

 

Date  -  Name  -  Attended

 

23/11 - Roger  -        1

23/11 - Charles -       1

23/11 - Pierre   -        0

24/11 - Steph  -         1

24/11 -  Rath  -          0

etc...

 

Is that like that ?

The problem is I don't really know how to code the table in PHP by doing like that, I don't see how I could create the PHP table dinamically.

 

Might be able to help with that if I knew exactly what you were trying to display

[RaJiska]

Thanks Denno, effectively, I would also need the one that did not join.

So, since you're asking Barand, yes, I'm going to write it down, the meeting was the easier example I found.

Basically, it would be for a game, there are 100 members in a group that must attend to this meeting (getting if attended is simulated by a link triggered by the game).

(The game link triggered looks like this: http://host.com/?password=pass&youInfo=1&whatyouwant=2&... ).

 

I want them to join the server each Saturday and Sunday, it has been done with a 'if ($current_day == "Sat" || $current_day == "Sun") { }', but now, I need to know what to do in this if.

What has to be displayed like this: http://img4.hostingpics.net/pics/115542Sanstitre.png (Here is a google doc) and the color would define if the player has joined or not.

 

In order to see if the player hasn't joined, we'll get a list of all players name (a list registred in another table) and if they did not joined, then we add them as not attended.

Thanks again for your help .

[Barand]

My sample data

 

 

mysql> SELECT * FROM people;
+----------+---------+------------+
| idpeople | name    | rank       |
+----------+---------+------------+
|        1 | Roger   | General    |
|        2 | Charles | Major      |
|        3 | Pierre  | NCO        |
|        4 | Steph   | Captain    |
|        5 | Rath    | Captain    |
|        6 | Peter   | Captain    |
|        7 | Mary    | Lieutenant |
|        8 | Julie   | Lieutenant |
|        9 | Martin  | Lieutenant |
|       10 | Alan    | NCO        |
|       11 | Amanda  | NCO        |
|       12 | John    | Major      |
|       13 | Ruth    | NCO        |
+----------+---------+------------+

+--------------+----------+------------+----------+
| idattendance | idpeople | gamedate   | attended |
+--------------+----------+------------+----------+
|            1 |        1 | 2013-08-24 |        1 |
|            2 |        2 | 2013-08-24 |        0 |
|            3 |        3 | 2013-08-24 |        1 |
|            4 |        4 | 2013-08-24 |        1 |
|            5 |        5 | 2013-08-24 |        1 |
|            6 |        6 | 2013-08-24 |        1 |
|            7 |        7 | 2013-08-24 |        1 |
|            8 |        8 | 2013-08-24 |        1 |
|            9 |        9 | 2013-08-24 |        1 |
|           10 |       10 | 2013-08-24 |        1 |
|           11 |       11 | 2013-08-24 |        1 |
|           12 |       12 | 2013-08-24 |        1 |
|           13 |       13 | 2013-08-24 |        1 |
|           14 |        1 | 2013-08-25 |        1 |
|           15 |        2 | 2013-08-25 |        1 |
|           16 |        3 | 2013-08-25 |        0 |
|           17 |        4 | 2013-08-25 |        1 |
|           18 |        5 | 2013-08-25 |        0 |
|           19 |        6 | 2013-08-25 |        0 |
|           20 |        7 | 2013-08-25 |        1 |
|           21 |        8 | 2013-08-25 |        1 |
|           22 |        9 | 2013-08-25 |        1 |
|           23 |       10 | 2013-08-25 |        0 |
|           24 |       11 | 2013-08-25 |        1 |
|           25 |       12 | 2013-08-25 |        1 |
|           26 |       13 | 2013-08-25 |        1 |
|           27 |        1 | 2013-08-31 |        1 |
|           28 |        2 | 2013-08-31 |        1 |
|           29 |        3 | 2013-08-31 |        1 |
|           30 |        4 | 2013-08-31 |        1 |
|           31 |        5 | 2013-08-31 |        1 |
|           32 |        6 | 2013-08-31 |        1 |
etc

 

 

 

The code

<?php
include ("/db_inc.php");
#mysql_connect(HOST, USERNAME, PASSWORD);
#mysql_select_db(DATABASE);
error_reporting(-1);
$db = new mysqli(HOST, USERNAME, PASSWORD, 'rajiska');

/*******************************************
* Calculate the table date range
* Last Saturday and the 12 previous weekends
********************************************/
$end = new DateTime('last saturday');
$end->modify('+7 days');
$start = new DateTime('last saturday');
$start->modify('-84 days');
/*******************************************
* Put required dates for the table date range
* into an array.
* This array will be used for the table
* heading rows and also to create empty
* data arrays for each person to ensure
* there is an entry for every date even
* if the person has no data for that date
********************************************/
$daterange = array();
$di = new DateInterval('P7D');
$dp = new DatePeriod($start, $di, $end);
foreach ($dp as $d) {
    $daterange[] = $d->format('Y-m-d'); // Sat
    $d->modify('+1 days');
    $daterange[] = $d->format('Y-m-d'); // Sun
}
/****************************************
* create empty array to be used for each 
* person
*****************************************/
$emptydata = array_fill_keys($daterange,'-1');
/****************************************
* Create table heading rows
*****************************************/
$prevmonth='';
$headrow1 = "<tr><th rowspan='2'>Name</th><th rowspan='2'>Rank</th>";
$headrow2 = "<tr>";
foreach ($daterange as $day) {
    $d = new DateTime($day);
    if ($d->format('M') != $prevmonth) {
        $headrow1 .= "<th>" . $d->format('M') . "</th>";
        $prevmonth = $d->format('M');
    }
    else $headrow1 .= "<th> </th>";
    $headrow2 .= "<th>" . strtoupper($d->format('D')) . '<br>' . $d->format('j/m') . "</th>";
}
$headrow1 .= "</tr>\n";
$headrow2 .= "</tr>\n";
/****************************************
* 
* Create the main table data rows
* 
*****************************************/
$tablerows = '';
$sql = "SELECT p.name, p.rank, a.gamedate, a.attended
    FROM people p
    LEFT JOIN attendance a USING (idpeople)
    ORDER BY FIELD(p.rank,'General', 'Major', 'Captain', 'Lieutenant', 'NCO'), 
        p.name, a.gamedate";

$res = $db->query($sql);
$curname = $currank = '';
while ($row = $res->fetch_assoc()) {
    if ($row['name'] != $curname) {  // has the name changed in the query data? if yes, output
        if ($curname) {
            $tablerows .= "<tr><td class='name'>$curname</td><td class='name'>$currank</td>";
            foreach ($persdata as $val) {
                switch($val) {
                    case 1: $cls = "class='att'"; break;
                    case 0: $cls = "class='not'"; break;
                    default: $cls = '';
                }
                $tablerows .= "<td $cls> </td>";
            }
            $tablerows .= "</tr>\n";
        }
        $persdata = $emptydata;  // reset the empty array
        $curname = $row['name'];
        $currank = $row['rank'];
    }
    if (isset($persdata[$row['gamedate']])) {
        $persdata[$row['gamedate']] = $row['attended']; // store current persons attendance in array
    }
}
// don't forget to output the array for last person in query
$tablerows .= "<tr><td class='name'>$curname</td><td class='name'>$currank</td>";
foreach ($persdata as $val) {
    switch($val) {
        case 1: $cls = "class='att'"; break;
        case 0: $cls = "class='not'"; break;
        default: $cls = '';
    }
    $tablerows .= "<td $cls> </td>";
}
$tablerows .= "</tr>\n";

?>
<html>
<head>
<meta http-equiv="pragma" content="no-cache">
<meta http-equiv="cache-control" content="no-cache">
<meta name="generator" content="PhpED Version 8.1 (Build 8115)">
<title>Sample dynamic table</title>
<meta name="author" content="Barand">
<meta name="creation-date" content="11/23/2013">
<style type="text/css">
table {
    border-collapse: collapse;
}
th {
    background-color: white;
    color: black;
    font-weight: 500;
    text-align: left;
    font-family: arial;
    font-size: 9pt;
}
td {
    background-color: aqua;
    width: 40px;
}
td.name {
    background-color: black;
    color: white;
    font-family: arial;
    font-size: 9pt;}
td.att {
    background-color: #80FF80;
}
td.not {
    background-color: blue;
}
</style>
</head>
<body>

    <table border='1' >
    <?php echo $headrow1?>
    <?php echo $headrow2?>
    <?php echo $tablerows?>
    </table>
</body>
</html>

Sample output attached

[RaJiska]

Thank you so much Barand.

I'll have to learn from your code since there are few things not clear yet.

For exemple, it keeps logs for up to 84 days ago, right ?

Also, why the 23/11 couldn't be seen ?

I mean today is a saturday and it's the 23/11 .
 

Anyway, thanks A LOT for your help, much helpful.

[Barand]

For exemple, it keeps logs for up to 84 days ago, right ?

 

It doesn't keep any logs. It just displays the last 84 days of the log that you keep

 

 

Also, why the 23/11 couldn't be seen ?

I mean today is a saturday and it's the 23/11 .

 

Anyway, thanks A LOT for your help, much helpful.

 

It ends on "last saturday". Tomorrow the last saturday will be 23/11 and it should show then.

[RaJiska]

Ok, perfect, thanks.

Yes, I saw that this morning.

 

After long hours at looking at your code, I can say that I don't understand how is displayed the color.

I would like to change the color of the person in function of its rank and add new possibilities (not only the not recorded, attended and not attended).

 

Then, I found this:

foreach ($persdata as $val) {
switch($val) {
case 1: $cls = "class='att'"; break;
case 0: $cls = "class='not'"; break;
default: $cls = '';

But I don't see where are the colors defined here.

Also, where is the value from the database taken ? The one that defines if the player did or not attended.

Anyway, thanks again for your help.

Edited November 24, 2013 by RaJiska

[Barand]

the colours are defined in the <style> section of the HTML head code.

td {
background-color: aqua;
width: 40px;
}

td.name {
background-color: black;
color: white;
font-family: arial;
font-size: 9pt;
}

td.att {
background-color: #80FF80;
}

td.not {
background-color: blue;
}

"attended" or not is from the attendance table shown in the sample data

+--------------+----------+------------+----------+
| idattendance | idpeople | gamedate   | attended |
+--------------+----------+------------+----------+
|            1 |        1 | 2013-08-24 |        1 |
|            2 |        2 | 2013-08-24 |        0 |
|            3 |        3 | 2013-08-24 |        1 |
|            4 |        4 | 2013-08-24 |        1 |
|            5 |        5 | 2013-08-24 |        1 |

[RaJiska]

Ok, perfect, yes, I saw for the sample, but I meant how is it taken from the database to the page in order to see what value there is in attended ?

Also, something else, now, I would like to get the people that didn't come.

 

For that, I did it: SELECT people.* FROM people LEFT JOIN attendance ON people.idpeople = attendance.idpeople WHERE people.idpeople IS NULL AND attendance.gamedate = '2013-11-24'; , the problem is I don't get anything back.

 

Thanks again - much instructive.

[Jayden_Blade]

It hates your

 

'2013-11-24'

 

set

$gamedate = DateTime('2013-11-24');

 

then

 

SELECT people.* FROM people LEFT JOIN attendance ON people.idpeople = attendance.idpeople WHERE people.idpeople IS NULL AND attendance.gamedate = $gamedate;

 

something like that should work.

 

Wish you luck!

Edited November 25, 2013 by Jayden_Blade

[RaJiska]

No, no, the problem is not a syntax issue, I don't have any error.

[Jayden_Blade]

Did you use:

 

error_reporting(E_ALL);

 

if no error no data

try

 

SELECT people.* FROM people LEFT JOIN attendance ON people.idpeople = attendance.idpeople WHERE people.idpeople IS 0 AND attendance.gamedate = '2013-11-24';

[Jayden_Blade]

NULL = "no value"

0 = 0

[Barand]

Jayden_Blade

 

Did you use:

 

error_reporting(E_ALL);

 

 

Did you. You really should take your own advice

$gamedate = DateTime('2013-11-24');
//--> Fatal error: Call to undefined function DateTime()

However, if you meant

$gamedate = new DateTime('2013-11-24');

$sql = "SELECT people.* FROM people LEFT JOIN attendance ON people.idpeople = attendance.idpeople WHERE people.idpeople IS NULL AND attendance.gamedate = $gamedate";
// --> Catchable fatal error: Object of class DateTime could not be converted to string

Not helping much, are you?

 

[Jayden_Blade]

Jayden_Blade

 

 

 

Did you. You really should take your own advice

 

$gamedate = DateTime('2013-11-24');
//--> Fatal error: Call to undefined function DateTime()

However, if you meant

$gamedate = new DateTime('2013-11-24');

$sql = "SELECT people.* FROM people LEFT JOIN attendance ON people.idpeople = attendance.idpeople WHERE people.idpeople IS NULL AND attendance.gamedate = $gamedate";
// --> Catchable fatal error: Object of class DateTime could not be converted to string

Not helping much, are you?

Your right I forgot the "new".

No need to get testy.

[RaJiska]

I probably didn't make myself clear enough.

What I would like is get the 'negative' of this: SELECT people.* FROM people LEFT JOIN attendance ON people.idpeople = attendance.idpeople WHERE people.idpeople AND attendance.gamedate = '2013-11-24';

 

By doing this, I get all those who attended the 2013-11-24.

What I wish to get is the substration, so if I've: Roger, Pierre, Stephan, and Jaques in total and I have Roger Pierre, Stephan who appears when I do the previous comand, I would like to get a comand that would only show me Jaques.

 

Thanks anyway for your help.

[Barand]

So assuming there's no problem getting those who attended and you have this in your log so far for the 24 Nov

+--------------+----------+------------+----------+
| idattendance | idpeople | gamedate   | attended |
+--------------+----------+------------+----------+
|          302 |        1 | 2013-11-24 |        1 |
|          303 |        3 | 2013-11-24 |        1 |
|          304 |        5 | 2013-11-24 |        1 |
|          305 |        7 | 2013-11-24 |        1 |
|          306 |        9 | 2013-11-24 |        1 |
|          307 |       10 | 2013-11-24 |        1 |
|          308 |       11 | 2013-11-24 |        1 |
|          309 |       12 | 2013-11-24 |        1 |
|          310 |       13 | 2013-11-24 |        1 |
+--------------+----------+------------+----------+

You can now run this script to add the non-attendees to the log for the 24th

$gamedate = '2013-11-24';

$sql = "INSERT INTO attendance(idpeople,gamedate,attended)
    SELECT p.idpeople, '$gamedate', 0
    FROM people p
    LEFT JOIN attendance a
        ON p.idpeople = a.idpeople AND a.gamedate = '$gamedate'
    WHERE a.idpeople IS NULL";
$db->query($sql);

Which now gives

+--------------+----------+------------+----------+
| idattendance | idpeople | gamedate   | attended |
+--------------+----------+------------+----------+
|          302 |        1 | 2013-11-24 |        1 |
|          303 |        3 | 2013-11-24 |        1 |
|          304 |        5 | 2013-11-24 |        1 |
|          305 |        7 | 2013-11-24 |        1 |
|          306 |        9 | 2013-11-24 |        1 |
|          307 |       10 | 2013-11-24 |        1 |
|          308 |       11 | 2013-11-24 |        1 |
|          309 |       12 | 2013-11-24 |        1 |
|          310 |       13 | 2013-11-24 |        1 |
|          317 |        2 | 2013-11-24 |        0 |
|          318 |        4 | 2013-11-24 |        0 |
|          319 |        6 | 2013-11-24 |        0 |
|          320 |        8 | 2013-11-24 |        0 |
+--------------+----------+------------+----------+

[RaJiska]

Thanks a lot Barand,

 

Once more you saved me, much instructive code.

Thanks again.