Dynamic Table from MySQL

RaJiska · November 22, 2013

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 · November 22, 2013

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 · November 23, 2013

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 · November 23, 2013

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 · November 23, 2013

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 · November 23, 2013

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 · November 23, 2013

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 · November 23, 2013

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 · November 23, 2013

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 · November 23, 2013

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 · November 23, 2013

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 · November 24, 2013

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 · November 24, 2013

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 · November 25, 2013

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 · November 25, 2013

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 · November 25, 2013

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

Jayden_Blade · November 25, 2013

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 · November 25, 2013

NULL = "no value"

0 = 0

Barand · November 25, 2013

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 · November 25, 2013

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 · November 25, 2013

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 · November 25, 2013

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 · November 26, 2013

Thanks a lot Barand,

Once more you saved me, much instructive code.

Thanks again.

Sign In

Dynamic Table from MySQL

Recommended Posts

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information