Barand

Assuming no more than 2 changes en route mysql> select * from journey; +----+------------+--------+----------+--------+----------+ | id | date | depart | dep_time | arrive | arr_time | +----+------------+--------+----------+--------+----------+ | 1 | 2015-11-23 | A | 04:00:00 | Z | 06:00:00 | | 2 | 2015-11-23 | U | 13:30:00 | Z | 14:20:00 | | 3 | 2015-11-23 | A | 07:00:00 | T | 08:00:00 | | 4 | 2015-11-23 | A | 09:00:00 | U | 12:00:00 | | 5 | 2015-11-23 | T | 09:00:00 | B | 12:00:00 | | 6 | 2015-11-23 | B | 13:00:00 | Z | 15:00:00 | +----+------------+--------+----------+--------+----------+ SELECT * FROM ( SELECT depart as depA , dep_time as deptimeA , arrive as arrA , arr_time as arrtimeA FROM journey WHERE depart='A' AND dep_time > '06:00' ) a LEFT JOIN ( SELECT depart as depB , dep_time as deptimeB , arrive as arrB , arr_time as arrtimeB FROM journey ) b ON arrA = depB AND arrtimeA < deptimeB LEFT JOIN ( SELECT depart as depC , dep_time as deptimeC , arrive as arrC , arr_time as arrtimeC FROM journey WHERE arrive='Z' ) c ON arrB = depC AND arrtimeB < deptimeC; +------+----------+------+----------+------+----------+------+----------+------+----------+------+----------+ | depA | deptimeA | arrA | arrtimeA | depB | deptimeB | arrB | arrtimeB | depC | deptimeC | arrC | arrtimeC | +------+----------+------+----------+------+----------+------+----------+------+----------+------+----------+ | A | 09:00:00 | U | 12:00:00 | U | 13:30:00 | Z | 14:20:00 | | | | | | A | 07:00:00 | T | 08:00:00 | T | 09:00:00 | B | 12:00:00 | B | 13:00:00 | Z | 15:00:00 | +------+----------+------+----------+------+----------+------+----------+------+----------+------+----------+

If it is an existing customer then you would pass the customer_id from the form to the processing code and insert it in the task record with the other task data. If it is new customer then you add the customer record, get the last_insert_id() and use that in the task record.

No hard and fast rule. If all you want to do with date is display it then it doesn't really matter, though it's easier to to do it in the query. If you need to do some further processing of the date in your PHP then select the raw date (yyyy-mm-dd) in the query, as d/m/y it useless for anything other than display.

Customer details should be in a customer table. Just the id of the customer would go in the task record +------------+ +-----------------+ | employee | | customer | +------------+ +-----------------+ | emp_id(PK) | +----------------+ | customer_id(PK) | | emp_name | | task | | cust_name | +------------+ +----------------+ | cust_address | | | task_id(PK) | | cust_phone | | | description | +-----------------+ | | date_of_repair | | | | customer_id |>--------------------+ | +---------------+ | status | | | assignment | +----------------+ | +---------------+ | | | assign_id(PK) | | +------------------<| emp_id | | | task_id |>--------------------+ +---------------+

@benanamen, Pure bovine excrement! Plus you have been around here long enough to know you should use code tags (I have added them for you)

DATE FORMAT should not be two words, it's DATEunderscoreFORMAT eg SELECT DATE_FORMAT(t.date_of_repair, '%d/%m/%Y')

This should get you started SELECT dep1.SectorDate , dep1.Dep , arrs , firstBegin , lastEnd FROM ( SELECT t.SectorDate , IF(Dep='TPS', NULL, Dep) as Dep , firstBegin , lastEnd FROM rosters r JOIN ( SELECT SectorDate , MIN(BeginTime) as firstBegin , MAX(EndTime) as lastEnd FROM rosters GROUP BY SectorDate ) t ON r.SectorDate=t.SectorDate AND r.BeginTime=t.firstBegin ) dep1 LEFT JOIN ( SELECT SectorDate , GROUP_CONCAT(Arr ORDER BY BeginTime separator ', ') as arrs FROM rosters WHERE 'TPS' <> Arr GROUP BY SectorDate ) a USING (SectorDate) GROUP BY SectorDate; +------------+------+------+------------+---------+ | SectorDate | Dep | arrs | firstBegin | lastEnd | +------------+------+------+------------+---------+ | 2015-03-15 | HHN | CIA | 16:15 | 21:45 | | 2015-03-16 | | BGY | 18:30 | 20:15 | | 2015-03-17 | | BVA | 14:20 | 20:00 | | 2015-03-18 | | | 11:30 | 22:30 | +------------+------+------+------------+---------+

If you want the count for each team JOIN ( SELECT team_id, status, COUNT(*) AS nap FROM `team_players` WHERE status = '1' GROUP BY team_id ) AS tp ON td.team_id = tp.team_id

SELECT td.*, CONCAT(me.First_Name,' ', me.Surname) AS managers_full_name, me.Telephone AS managers_telephone, td.status AS status I don't see "nap" in the columns selected by the query (remember your subquery behaves as though it were another (temp) table

... and the subquery "tp" doesn't have a team_id to join on. All it has is a "nap" field.

Alternative version if you prefer to group the timezones in the menu by region $arr = timezone_identifiers_list(); $now = new DateTime(); foreach ($arr as $tz) { if (strpos($tz, '/')===false ) continue; list ($region, $city) = explode('/', $tz, 2); $d = new DateTimeZone($tz); $secs = $d->getOffset($now); $offset = number_format($secs/3600, 2); $tzArray[$region][$offset][] = $city; } ksort($tzArray); $menu = "<option value=''>- select timezone -</option>\n"; foreach ($tzArray as $region => $regzones) { $menu .= sprintf("<optgroup label='%s'>\n", $region); ksort($regzones); foreach ($regzones as $o => $cities) { foreach ($cities as $city) $menu .= sprintf("<option value='$region/$city'>GMT%+0.2f $region/$city</option>\n", $o); } $menu .= "</optgroup>\n"; } ?> <select name='tzone'> <?=$menu?> </select>

perhaps $arr = timezone_identifiers_list(); foreach ($arr as $tz) { if (strpos($tz, '/')===false ) continue; list ($region, $city) = explode('/', $tz, 2); $d = new DateTimeZone($tz); $secs = $d->getOffset(new DateTime()); $offset = number_format($secs/3600, 2); $tzArray[$offset][$region][] = $city; } ksort($tzArray); $menu = "<option value=''>- select timezone -</option>\n"; foreach ($tzArray as $o => $regzones) { $menu .= sprintf("<optgroup label='GMT %+0.2f'>\n", $o); ksort($regzones); foreach ($regzones as $region => $cities) { foreach ($cities as $city) $menu .= "<option >$region/$city</option>\n"; } $menu .= "</optgroup>\n"; } ?> <select name='tzone'> <?=$menu?> </select>

Forget that. You can use $tz_array = timezone_identifiers_list(); echo '<pre>',print_r($tz_array, true),'</pre>';

Create a database table of the timezones and build it using that http://timezonedb.com/download

Before you can update a task, you need to be able to specify which task you want to update. Then you have to get the data for that task and check the checkboxes for the employees already assigned to that task. Once that is done the existing data can be edited and saved. As you see, it's not just changing INSERT to UPDATE.

Looks like your php version is out of date. Instead of [$task_id, $emp] you will need array($task_id, $emp), and same with the other line. [] is short form of array()from PHP5.4+ http://uk3.php.net/manual/en/language.types.array.php

Easiest way in this situation is a form with task description task status a list of checkboxes for each employee that can be assigned. On posting, insert new task and get the generated id using lastInsertId() Loop through the posted checkbox values (emp_ids) and insert assignment record for each Here's an example $db = new PDO("mysql:host=localhost;dbname=DBNAME",USERNAME,PASSWORD); $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); if ($_SERVER['REQUEST_METHOD']=='POST') { // was data sent if ($_POST['descrip'] != '') { try { $db->beginTransaction(); $sql = "INSERT INTO task (description, status) VALUES (?,?)"; $stmt = $db->prepare($sql); $stmt->execute([$_POST['descrip'], $_POST['status']]); $task_id = $db->lastInsertId(); // get the id of new task // now insert employees assigned to the task $sql = "INSERT INTO assignment(task_id, emp_id) VALUES (?,?)"; $stmt = $db->prepare($sql); foreach ($_POST['emp_id'] as $emp) { $stmt->execute([$task_id, $emp]); } $db->commit(); } catch (PDOException $e) { $db->rollBack(); die($e->getMessage()); } } } function emps($db) /******************************************* * function to list employees with checkboxes ********************************************/ { $sql = "SELECT emp_id, emp_name FROM employee ORDER BY emp_name"; $emps=''; foreach($db->query($sql) as $row) { $emps .= "<input type='checkbox' name='emp_id[]' value='{$row['emp_id']}'> {$row['emp_name']}<br>"; } return $emps; } ?> <html> <head> <meta name="generator" content="PhpED 14.0 (Build 14039, 64bit)"> <title>Add Task</title> <meta name="author" content="Barand"> <meta name="creation-date" content="11/21/2015"> <style type="text/css"> .label { width: 150px; display: inline-block; } </style> </head> <body> <div id='title'> <h1>Add Task</h1> <form method="post"> <fieldset> <legend>Task</legend> <div class='label'><label for='descrip'>Description</label></div> <input type="text" name="descrip" id="descrip" size="50" /> <br> <div class='label'><label for='status'>Status</label></div> <select name='status' id='status'> <option value='0'>Not started</option> <option value='1'>In progress</option> <option value='2'>Completed</option> </select> </fieldset> <br> <fieldset> <legend>Assign to</legend> <?= emps($db) ?> </fieldset> <input type="submit" name="btnSubmit" value="Submit"> </form> </div> </body> </html>

you need to use fetchObject() and not fetchColumn(). This worked for me $sql=$dbh->prepare("SELECT e.emp_id , t.task_id , t.description , t.status FROM employee e JOIN assignment a ON e.emp_id = a.emp_id JOIN task t ON a.task_id = t.task_id WHERE e.emp_id = ?"); $sql->execute(array($_SESSION['user'])); // were any rows found? if ($row = $sql->fetchObject()) { // display records in a table echo "<table>"; // set table headers echo "<tr> <th>Task ID</th> <th>Description</th> <th>Status</th> <th>Emp ID</th> <th>Status</th> <th colspan='1'>Actions</th> </tr>"; do { // set up a row for each record echo "<tr>"; echo "<td>" . $row->task_id . "</td>"; echo "<td>" . $row->description . "</td>"; echo "<td>" . $row->status . "</td>"; echo "<td>" . $row->emp_id . "</td>"; echo "<td>" . $row->status . "</td>"; echo "<td><a href='records.php?task_id=" . $row->task_id . "'>Edit</a></td>"; echo "</tr>"; } while ($row = $sql->fetchObject()); echo "</table>"; } // if there are no records in the database, display an alert message else { echo "No results to display!"; }

So you now have two functions which do exactly the same thing, good thinking. The correct solution was to define the function once then call it each time you need it.

Your connection object is $db, but you are trying to use $mysqli->prepare()

http://dev.mysql.com/doc/refman/5.6/en/numeric-type-attributes.html

Do you still have this line? while ($row = $result->fetch_object()) because you don't have a $result object, you have $sql as a statement object. Do it like you did in the query above that one in your code.

Does your assignment table contain any records for emp_id = 2?

why have you changed "prepare" to "query"?

$loggedInUser is just an example to demonstrate the query syntax. So in your case it will probably be $sql=$dbh->prepare("SELECT e.emp_id , e.emp_name , t.task_id , t.description , t.status FROM employee e JOIN assignment a ON e.emp_id = a.emp_id JOIN task t ON a.task_id = t.task_id WHERE e.emp_id = ?"); $sql->execute(array($_SESSION['user']));