RaJiska Posted November 22, 2013 Share Posted 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. Quote Link to comment Share on other sites More sharing options...
Barand Posted November 22, 2013 Share Posted 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 Quote Link to comment Share on other sites More sharing options...
RaJiska Posted November 23, 2013 Author Share Posted 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. Quote Link to comment Share on other sites More sharing options...
denno020 Posted November 23, 2013 Share Posted 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 Quote Link to comment Share on other sites More sharing options...
Barand Posted November 23, 2013 Share Posted 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. Quote Link to comment Share on other sites More sharing options...
denno020 Posted November 23, 2013 Share Posted 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.. Quote Link to comment Share on other sites More sharing options...
Barand Posted November 23, 2013 Share Posted 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 Quote Link to comment Share on other sites More sharing options...
RaJiska Posted November 23, 2013 Author Share Posted 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 . Quote Link to comment Share on other sites More sharing options...
Barand Posted November 23, 2013 Share Posted 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 Quote Link to comment Share on other sites More sharing options...
RaJiska Posted November 23, 2013 Author Share Posted 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. Quote Link to comment Share on other sites More sharing options...
Barand Posted November 23, 2013 Share Posted 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. Quote Link to comment Share on other sites More sharing options...
RaJiska Posted November 24, 2013 Author Share Posted November 24, 2013 (edited) 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 Quote Link to comment Share on other sites More sharing options...
Barand Posted November 24, 2013 Share Posted 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 | Quote Link to comment Share on other sites More sharing options...
RaJiska Posted November 25, 2013 Author Share Posted 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. Quote Link to comment Share on other sites More sharing options...
Jayden_Blade Posted November 25, 2013 Share Posted November 25, 2013 (edited) 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 Quote Link to comment Share on other sites More sharing options...
RaJiska Posted November 25, 2013 Author Share Posted November 25, 2013 No, no, the problem is not a syntax issue, I don't have any error. Quote Link to comment Share on other sites More sharing options...
Jayden_Blade Posted November 25, 2013 Share Posted 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'; Quote Link to comment Share on other sites More sharing options...
Jayden_Blade Posted November 25, 2013 Share Posted November 25, 2013 NULL = "no value" 0 = 0 Quote Link to comment Share on other sites More sharing options...
Barand Posted November 25, 2013 Share Posted 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? Quote Link to comment Share on other sites More sharing options...
Jayden_Blade Posted November 25, 2013 Share Posted 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 stringNot helping much, are you? Your right I forgot the "new". No need to get testy. Quote Link to comment Share on other sites More sharing options...
RaJiska Posted November 25, 2013 Author Share Posted 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. Quote Link to comment Share on other sites More sharing options...
Barand Posted November 25, 2013 Share Posted 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 | +--------------+----------+------------+----------+ Quote Link to comment Share on other sites More sharing options...
RaJiska Posted November 26, 2013 Author Share Posted November 26, 2013 Thanks a lot Barand, Once more you saved me, much instructive code. Thanks again. Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.