Barand

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']));

yes

When the user logs in, store their id in $_SESSION variable. Then you uses a WHERE clause to get that users data 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 = $loggedInUser

You join table on the matching fields. So join employee to assignment using the emp_id fields and join task to assignment using the task_id fields. SELECT e.emp_id , e.emp_name , t.description FROM employee e JOIN assignment a ON e.emp_id = a.emp_id JOIN task t ON a.task_id = t.task_id

Not a difficult problem. It's basically maintaining three tables +-------------+ +--------------+ | employee | | task | +-------------+ +--------------+ | emp_id (PK) | | task_id (PK) | | empname | | description | +-------------+ | status | | +--------------+ | | | | | | | +----------------+ | | | assignment | | | +----------------+ | +---------------------<| assign_id (PK) |>------------------+ | emp_id | | task_id | +----------------+ Use the assignment table to find the tasks assigned to an employee

It should have. That's THE PHP MANUAL. What have you been reading?

http://php.net/manual/en/function.file.php Parameter section flags subsection

Store your pdf file locations relative to the root, not relative to your application. Then it doesn't matter which folder the application runs from