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 |

... or use var_export instead of print_r()

If you need dates and times I'd recommend keeping them together. There may be an occasion when, for instance, you want all records between noon yesterday and noon today. Much simpler with the single datetime field.

It doesn't keep any logs. It just displays the last 84 days of the log that you keep It ends on "last saturday". Tomorrow the last saturday will be 23/11 and it should show then.

If you are getting that error message then your query is failing. Check value of mysql_error() after running the query. To get the last 10 days data, say, then your query would be SELECT * FROM points WHERE DATE(Date_Time) > CURDATE() - INTERVAL 10 DAY DATE() removes the time element from the datetime CURDATE() returns todays date

My sample data 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

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

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.

My data was ordered by subject, date in the subquery so it would look like subject date ------- --------- 1 day 1 1 day 2 1 day 3 1 day 4 1 day 5 2 day 1 2 day 2 2 day 3 2 day 4 2 day 5 When calculating the @row value I want to start again at 1 when the subject changes so that the same days fall into the same groups of five days

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

I am attempting to future-proof it for you so you won't have to rewrite the query when you come to add extra subjects as you said you would be doing. So long as you have a subject column it won't matter if there are 1 or 1001 different subjects. If there is only 1 subject just put 1, say, in every records' subject field. If you add more students to the table then you will need to change it however. (The simplest change it that case would be to add "WHERE studentId = $id").

The query I gave you before does give a simple average of of each week (five day group) however if they won't be consecutive then I adopted an approach similar to you. For future proofing, don't add columns for subjects and no need for separate table for each student. Your setup would look something like this +-------------+ +---------------+ +-------------+ | student | | testresult | | subject | +-------------+ +---------------+ +-------------+ | studentId |----+ | resultId | +-----| subjectId | | studentName | +----<| studentId | | | subjectName | | etc | | subjectId |>----+ +-------------+ +-------------+ | testdate | | score | +---------------+ As before this is the code I used to create the revised test data $db->query("DROP TABLE IF EXISTS testresult"); $db->query("DROP TABLE IF EXISTS subject"); $sql = "CREATE TABLE testresult ( resultId INT NOT NULL AUTO_INCREMENT, studentId INT, subjectId INT, testdate DATE NOT NULL DEFAULT 0, score INT, PRIMARY KEY (resultId) )"; $db->query($sql); $sql = "CREATE TABLE subject ( subjectId INT NOT NULL , subjectName VARCHAR(20), PRIMARY KEY (subjectId) )"; $db->query($sql); $sql = "INSERT INTO subject VALUES (1,'English'),(2,'Maths'), (3,'Science')"; $db->query($sql); // // generate test result data // $subjects = array (1,2,3); $data = array(); $di = dateinterval::createFromDateString('next weekday'); $startDate = '2013-06-03'; $dt = new DateTime($startDate); $dp = new DatePeriod($dt, $di, 14); // 15 day deriod foreach ($dp as $day) { foreach ($subjects as $sub) { $data[] = sprintf("(null, 1, %d, '%s', %d)", $sub, $day->format('Y-m-d'), rand(20,100)); } } $sql = "INSERT INTO testresult VALUES " . join(',', $data); $db->query($sql); DATA +----------+-----------+-----------+------------+-------+ | resultId | studentId | subjectId | testdate | score | +----------+-----------+-----------+------------+-------+ | 1 | 1 | 1 | 2013-06-03 | 78 | | 2 | 1 | 2 | 2013-06-03 | 22 | | 3 | 1 | 3 | 2013-06-03 | 77 | | 4 | 1 | 1 | 2013-06-04 | 53 | | 5 | 1 | 2 | 2013-06-04 | 22 | | 6 | 1 | 3 | 2013-06-04 | 81 | | 7 | 1 | 1 | 2013-06-05 | 23 | | 8 | 1 | 2 | 2013-06-05 | 80 | | 9 | 1 | 3 | 2013-06-05 | 63 | | 10 | 1 | 1 | 2013-06-06 | 50 | | 11 | 1 | 2 | 2013-06-06 | 95 | | 12 | 1 | 3 | 2013-06-06 | 43 | | 13 | 1 | 1 | 2013-06-07 | 72 | | 14 | 1 | 2 | 2013-06-07 | 42 | | 15 | 1 | 3 | 2013-06-07 | 99 | | 16 | 1 | 1 | 2013-06-10 | 68 | | 17 | 1 | 2 | 2013-06-10 | 58 | | 18 | 1 | 3 | 2013-06-10 | 55 | | 19 | 1 | 1 | 2013-06-11 | 76 | | 20 | 1 | 2 | 2013-06-11 | 28 | | 21 | 1 | 3 | 2013-06-11 | 96 | | 22 | 1 | 1 | 2013-06-12 | 91 | | 23 | 1 | 2 | 2013-06-12 | 51 | | 24 | 1 | 3 | 2013-06-12 | 62 | | 25 | 1 | 1 | 2013-06-13 | 89 | | 26 | 1 | 2 | 2013-06-13 | 89 | | 27 | 1 | 3 | 2013-06-13 | 62 | | 28 | 1 | 1 | 2013-06-14 | 98 | | 29 | 1 | 2 | 2013-06-14 | 68 | | 30 | 1 | 3 | 2013-06-14 | 74 | | 31 | 1 | 1 | 2013-06-17 | 76 | | 32 | 1 | 2 | 2013-06-17 | 100 | | 33 | 1 | 3 | 2013-06-17 | 24 | | 34 | 1 | 1 | 2013-06-18 | 32 | | 35 | 1 | 2 | 2013-06-18 | 96 | | 36 | 1 | 3 | 2013-06-18 | 25 | | 37 | 1 | 1 | 2013-06-19 | 64 | | 38 | 1 | 2 | 2013-06-19 | 96 | | 39 | 1 | 3 | 2013-06-19 | 50 | | 40 | 1 | 1 | 2013-06-20 | 53 | | 41 | 1 | 2 | 2013-06-20 | 41 | | 42 | 1 | 3 | 2013-06-20 | 59 | | 43 | 1 | 1 | 2013-06-21 | 30 | | 44 | 1 | 2 | 2013-06-21 | 85 | | 45 | 1 | 3 | 2013-06-21 | 93 | +----------+-----------+-----------+------------+-------+ Query SELECT MAX(testdate) as DateEnding, subjectName, AVG(score) as average FROM ( SELECT testdate, score, @row:=IF(@prev=subjectID, @row+1, 1) as row, @prev:=subjectId as subjectId FROM testresult JOIN (SELECT @row:=0, @prev:='') as init ORDER BY subjectId, testdate ) as rows INNER JOIN subject USING (subjectId) GROUP BY FLOOR((row-1)/5), subjectName ORDER BY DateEnding, subjectName RESULTS +------------+-------------+---------+ | DateEnding | subjectName | average | +------------+-------------+---------+ | 2013-06-07 | English | 55.2000 | | 2013-06-07 | Maths | 52.2000 | | 2013-06-07 | Science | 72.6000 | | 2013-06-14 | English | 84.4000 | | 2013-06-14 | Maths | 58.8000 | | 2013-06-14 | Science | 69.8000 | | 2013-06-21 | English | 51.0000 | | 2013-06-21 | Maths | 83.6000 | | 2013-06-21 | Science | 50.2000 | +------------+-------------+---------+

Some years ago I wrote myself a general purpose utility function for formatting strings (part numbers, phone numbers etc) function formatIt($format,$str) { $i = $j = 0; $res = ''; $kf = strlen($format); $ks = strlen($str); while ($i < $kf && $j < $ks) { $res .= $format[$i]=='#' ? $str[$j++] : $format[$i]; ++$i; } if ($j<$ks) $res .= substr($str,$j); return $res; } $str1 = '531008058'; echo formatIt('### ## ##-##', $str1); // --> 531 00 80-58 // or phone numbers eg $str2 = '01234567890222'; echo formatIt('(####) ### #### ext ###', $str2); // --> (0123) 456 7890 ext 222

Look at my example, engage brain, see what it is doing and adapt your code to do it the same way

Use the correct password

Something like this $db = new mysqli(HOST, USERNAME, PASSWORD, DATABASE); $startDate = '2013-06-03'; /****** UNCOMMENT BLOCK TO CREATE TEST DATA *************************************************** $db->query("DROP TABLE IF EXISTS testresult"); $sql = "CREATE TABLE testresult ( name VARCHAR(20) NOT NULL, testdate DATE NOT NULL DEFAULT 0, score INT, PRIMARY KEY (name, testdate) )"; $db->query($sql); // // generate test data // $students = array ('Peter', 'Paul', 'Mary'); $data = array(); $di = dateinterval::createFromDateString('next weekday'); $dt = new DateTime($startDate); $dp = new DatePeriod($dt, $di, 14); // 15 day deriod foreach ($dp as $day) { foreach ($students as $name) { $data[] = sprintf("('%s', '%s', %d)", $name, $day->format('Y-m-d'), rand(20,100)); } } $sql = "INSERT INTO testresult VALUES " . join(',', $data); $db->query($sql); ******************************************************************************************* mysql> SELECT * FROM testresult; +-------+------------+-------+ | name | testdate | score | +-------+------------+-------+ | Mary | 2013-06-03 | 58 | | Mary | 2013-06-04 | 99 | | Mary | 2013-06-05 | 34 | | Mary | 2013-06-06 | 56 | | Mary | 2013-06-07 | 37 | | Mary | 2013-06-10 | 22 | | Mary | 2013-06-11 | 45 | | Mary | 2013-06-12 | 30 | | Mary | 2013-06-13 | 58 | | Mary | 2013-06-14 | 31 | | Mary | 2013-06-17 | 98 | | Mary | 2013-06-18 | 42 | | Mary | 2013-06-19 | 55 | | Mary | 2013-06-20 | 47 | | Mary | 2013-06-21 | 100 | | Paul | 2013-06-03 | 60 | | Paul | 2013-06-04 | 98 | | Paul | 2013-06-05 | 51 | | Paul | 2013-06-06 | 61 | | Paul | 2013-06-07 | 63 | | Paul | 2013-06-10 | 71 | | Paul | 2013-06-11 | 21 | | Paul | 2013-06-12 | 85 | | Paul | 2013-06-13 | 75 | | Paul | 2013-06-14 | 65 | | Paul | 2013-06-17 | 87 | | Paul | 2013-06-18 | 85 | | Paul | 2013-06-19 | 81 | | Paul | 2013-06-20 | 46 | | Paul | 2013-06-21 | 93 | | Peter | 2013-06-03 | 79 | | Peter | 2013-06-04 | 70 | | Peter | 2013-06-05 | 21 | | Peter | 2013-06-06 | 75 | | Peter | 2013-06-07 | 54 | | Peter | 2013-06-10 | 46 | | Peter | 2013-06-11 | 57 | | Peter | 2013-06-12 | 66 | | Peter | 2013-06-13 | 48 | | Peter | 2013-06-14 | 29 | | Peter | 2013-06-17 | 24 | | Peter | 2013-06-18 | 69 | | Peter | 2013-06-19 | 73 | | Peter | 2013-06-20 | 92 | | Peter | 2013-06-21 | 87 | +-------+------------+-------+ **********************************************************************************************/ $sql = "SELECT name, MAX(testdate) as WeekEnding, AVG(score) as average FROM testresult GROUP BY name, WEEK(testdate)"; $db->query($sql); /* RESULTS +-------+------------+---------+ | name | WeekEnding | average | +-------+------------+---------+ | Mary | 2013-06-07 | 56.8000 | | Mary | 2013-06-14 | 37.2000 | | Mary | 2013-06-21 | 68.4000 | | Paul | 2013-06-07 | 66.6000 | | Paul | 2013-06-14 | 63.4000 | | Paul | 2013-06-21 | 78.4000 | | Peter | 2013-06-07 | 59.8000 | | Peter | 2013-06-14 | 49.2000 | | Peter | 2013-06-21 | 69.0000 | +-------+------------+---------+ */

Instead of the primary key being the auto_incrementing ID col, define the table as state VARCHAR(20) NOT NULL, roll_number INT NOT NULL auto_increment, date_of_test DATE, PRIMARY KEY (state, roll_number) If the second part of the PK is auto_increment then it will number from 1 to N for each state. The date for each will be basedate + floor((roll_number-1)/state_capacity) days

String values need to be in quotes in your update query. You have quoted them in the insert query and the same applies. Did you not get an error?

Assuming that table has a column 'company_name' $sql = sprintf("SELECT COUNT(*) as total FROM tbl_club_contacts WHERE company_name = '%s' ", $db->real_escape_string($companyname)); $result = $db->query($sql); $row = $result->fetch_assoc(); if ($row['total'] > 0) { $errors['companyname'] = "The name '$companyname' already exists."; }

Back to topic - do you have a database table of existing company names?

Your code will flag an error if "the" occurs anywhere in the name. You need to check for "the " (with space) in position 0 $company_names = array ( 'Theakstons', 'The Rams', 'Wetherspoons', 'THE HAMMERS', 'the tangerines' ); foreach ($company_names as $name) { echo "$name : "; if (stripos($name, 'the ')===0) { echo 'FAIL - cannot begin with "The"<br>'; } else { echo 'PASS<br>'; } } /* RESULTS Theakstons : PASS The Rams : FAIL - cannot begin with "The" Wetherspoons : PASS THE HAMMERS : FAIL - cannot begin with "The" the tangerines : FAIL - cannot begin with "The" */

Off topic but why would you want to bar company names like Theakstons or Wetherspoons?

Exactly. Only get what you need. Plus, if you (or someone else) come back to that query in future, it's better documented if you can see exactly what is getting and from where. You are already selecting the product id twice (once from product table and also products_img.product (which will be a match of the product.id)

You do it like this. Because you have used "product.* " which is bad practice, I don't know what you are selecting from that table so i have just referred to it as "product_stuff" $db = new mysqli(HOST, USERNAME, PASSWORD, DATABASE); $sql = "SELECT `products`.* , `products_lang`.`title` , `products_img`.`id` AS imgid , `products_img`.`imgurl` , `products_img`.`product` AS imgproduct FROM (`products`) INNER JOIN `products_lang` ON `products_lang`.`product` = `products`.`id` INNER JOIN `products_img` ON `products_img`.`product` = `products`.`id` WHERE `products_lang`.`language` = '3' AND `products`.`category` = '3' AND products_img.product = '3' "; $res = $db->query($sql); $current_prod = ''; while ($row = $res->fetch_assoc()) { if ($row['id'] != $current_prod) { // has product changed? echo "{$row['title']}<br>{$row['product_stuff']} "; $current_prod = $row['id']; // reset current_prod value } echo "<img src='{$row['imgurl']}' /><br>"; }

That's how joins work, appending selected data from each matching record to the records from the other table. The trick is to process the product data only when the value changes. Is the case that you have this for each product return by the query: 1 product rec --- 1 lang rec --- several image recs