ianhaney

Sorry my fault, I had a extra ( I now have the following and is outputting data now that looks right SELECT SUM(job_cost) as job_cost, SUM(profit) as profit FROM repairs WHERE MONTH(exrdate) = MONTH(CURDATE()) AND YEAR(exrdate) = YEAR(CURDATE())

Is this right as not getting nothing outputted? SELECT SUM(job_cost) as job_cost, SUM(profit) as profit FROM repairs WHERE MONTH(exrdate) = MONTH(CURDATE()) AND YEAR(exrdate) = YEAR(CURDATE)())

Ahh ok cool got it it is outputting amounts showing for December as well and not just for November, sorry, I want it to output the total cost and total profit for just the current month, sorry

Hi Barand Ok, I now have the following but still not getting any output SELECT SUM(job_cost) as job_cost, SUM(profit) as profit FROM repairs WHERE exrdate >= exrdate(exrdate, INTERVAL 1 MONTH

Hi I am trying to output the job_cost and profit from the previous month but am getting no output, I have the following coding <table class="dashboard"> <tr> <th colspan="5">Total Jobs Cost and Profit Made(Month)</th> </tr> <tr> <th>Month</th> <th>Total Job Costs</th> <th>Total Profit Made</th> </tr> <?php $hostname=""; $username=""; $password=""; $db = ""; $dbh = new PDO("mysql:host=$hostname;dbname=$db", $username, $password); foreach($dbh->query('SELECT SUM(job_cost, profit) as job_cost, profit FROM repairs WHERE exrdate >= exrdate(exrdate, INTERVAL 1 MONTH') as $row) { ?> <tr> <td><?php echo $row['date_of_repair']; ?></td> <td><?php echo $row['job_cost']; ?></td> <td><?php echo '£' . $row['profit']; ?></td> </tr> <?php } ?> </table> I know it is the SELECT query where I have messed up on somewhere but is about it, I am not sure what it should be, sorry Thank you in advance Ian

@Barand Oh right, is where I would get stuck if I had the customer details stored in it's own table, how would the INSERT and UPDATE query look for when adding a new record and updating it I am guessing it would be using JOINS

Ok Thank you for the advice and replies, appreciate it

Ahh ok what way is better or does it not matter as much if done the way I have done it or the DATE_FORMAT way?

Hi They are sort of like tasks, as when we get repair tasks in, we will assign them to various members of staff who then log in to view the tasks for the day and because they are repair tasks that are carried out on site, we needed to add in customer name, address etc

Sorry I did it another way by doing the following I added in this below date_default_timezone_set('Europe/London'); Then I did the following echo "<td>" . date('d/m/Y', strtotime($row->date_of_repair)) . "</td>"; Works perfect this way

Hi Sorry quick one, I am trying to make the date format into UK format and have entered the following into the SELECT query but is returning 0 results where as it should have two results I have the following SQL query $sql=$dbh->prepare("SELECT e.emp_id , t.task_id , t.customer_name , t.customer_phone , t.customer_address , DATE FORMAT(t.date_of_repair,'%d/%m/%Y') AS date_of_repair , 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']));

Sorry ignore that previous post, all seems ok, think it might of been cause I did not have any values in the database as since I put some in just now, it all seems ok and updating etc.

Sorry quick one, I have just added some more fields such as customer_name, customer_phone, customer_address and date_of_repair and added them new columns to the db and to the update php file so now it looks like the following <?php ini_set('display_startup_errors',1); ini_set('display_errors',1); error_reporting(-1); ?> <? session_start(); if($_SESSION['user']==''){ header("Location:login.php"); }else{ include("config.php"); $sql=$dbh->prepare("SELECT * FROM employee WHERE emp_id=?"); $sql->execute(array($_SESSION['user'])); while($r=$sql->fetch()){ echo "<div class='home-content'>"; echo "<center><h2>Hello, ".$r['username']."</h2>"; echo "<a href='logout.php'>Log Out</a></center>"; echo "</div>"; } } ?> <?php $hostname=''; $username=''; $password=''; $db = new PDO("mysql:host=$hostname;dbname=",$username,$password); $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // if form is submitted update task details if ($_SERVER['REQUEST_METHOD']=='POST') { // was data sent if ($_POST['descrip'] != '') { try { $sql = "UPDATE task SET customer_name = ?, customer_phone = ?, customer_address = ?, date_of_repair = ?, description = ?, status = ? WHERE task_id = ?"; $stmt = $db->prepare($sql); $result = $stmt->execute(array($_POST['customer_name'], $_POST['customer_phone'], $_POST['customer_address'], $_POST['date_of_repair'], $_POST['descrip'], $_POST['status'], $_GET['task_id'])); if($result && $stmt->rowCount() !== 0) { $msg = 'Task has been updated successfully'; } else { $msg = 'Sorry unable to update task.'; } } catch (PDOException $e) { $db->rollBack(); die($e->getMessage()); } } } // return the task which matches $_GET['task_id']; $sql=$dbh->prepare(" SELECT task_id , customer_name , customer_phone , customer_address , date_of_repair , description , status FROM task WHERE task_id = ?"); $sql->execute(array($_GET['task_id'])); $sql->setFetchMode(PDO::FETCH_ASSOC); // fetch the row from the result $row = $sql->fetch(); function emps_assigned_by_taskid($db, $task_id) /******************************************* * function to list employees with checkboxes - checkbox is checked if they are assigned to the task ********************************************/ { $sql = "SELECT e.emp_id, e.emp_name, IF(a.emp_id IS NULL, 0, 1) as isAssigned FROM employee e LEFT JOIN assignment a ON e.emp_id = a.emp_id AND a.task_id = ?"; $stmt = $db->prepare($sql); $stmt = $db->prepare($sql); $stmt->execute(array($task_id)); $emps=''; foreach($stmt->fetchAll() as $row) { // if isAssigned is set to 1 then set the checked attribute, otherwise leave blank $checked = $row['isAssigned'] == 1 ? ' checked="checked" ' : ''; $emps .= "<input type='checkbox' name='emp_id[]' value='{$row['emp_id']}'{$checked}> {$row['emp_name']}<br>"; } return $emps; } ?> <html> <head> <title>Update Task</title> </head> <body> <div id='title'> <h1>Edit Task</h1> <?php if(isset($msg)): ?> <p><?=$msg; ?></p> <?php endif; ?> <form method="post" action=""> <fieldset> <legend>Customer Name</legend> <div class='label'><label for='customer_name'>Customer Name</label></div> <input type="text" name="customer_name" id="customer_name" size="50" value="<?php echo $row['customer_name']; ?>" /> </fieldset> <fieldset> <legend>Customer Phone Number</legend> <div class='label'><label for='customer_phone'>Customer Phone Number</label></div> <input type="text" name="customer_phone" id="customer_phone" size="50" value="<?php echo $row['customer_phone']; ?>" /> </fieldset> <fieldset> <legend>Customer Address</legend> <div class='label'><label for='customer_address'>Customer Address</label></div> <textarea name="customer_address" id="customer_address"><?php echo $row['customer_address']; ?></textarea> </fieldset> <fieldset> <legend>Date of Repair</legend> <div class='label'><label for='date_of_repair'>Date of Repair</label></div> <input type="date" name="date_of_repair" id="date_of_repair" value="<?php echo $row['date_of_repair']; ?>"> </fieldset> <fieldset> <legend>Task Description</legend> <div class='label'><label for='descrip'>Description</label></div> <input type="text" name="descrip" id="descrip" size="50" value="<?php echo $row['description']; ?>" /> </fieldset> <br><br> Current Status is: <strong><?php echo $row['status']; ?></strong> <br><br> <div class='label'><label for='status'>Status</label></div> <select name='status' id='status'> <option value='Not Started'>Not started</option> <option value='In Progress'>In progress</option> <option value='Completed'>Completed</option> </select> </fieldset> <br><br> <fieldset> <legend>Assign to</legend> <?= emps_assigned_by_taskid($db, $_GET['task_id']) ?> </fieldset> <input type="submit" name="btnSubmit" value="Update"> </form> </div> </body> </html> But have missed something as got the following errors Notice: Undefined index: customer_name in /home/sites/it-doneright.co.uk/public_html/admin/staff-tasks/update-staff-task.php on line 40 Notice: Undefined index: customer_phone in /home/sites/it-doneright.co.uk/public_html/admin/staff-tasks/update-staff-task.php on line 40 Notice: Undefined index: customer_address in /home/sites/it-doneright.co.uk/public_html/admin/staff-tasks/update-staff-task.php on line 40 Notice: Undefined index: date_of_repair in /home/sites/it-doneright.co.uk/public_html/admin/staff-tasks/update-staff-task.php on line 40

Thank you so much, is perfect now

Sorry I altered the coding a bit and took a line out as it looked like it was the same line but with $result = in front I now have the following // if form is submitted update task details if ($_SERVER['REQUEST_METHOD']=='POST') { // was data sent if ($_POST['descrip'] != '') { try { $sql = "UPDATE task SET description = ?, status = ? WHERE task_id = ?"; $stmt = $db->prepare($sql); $result = $stmt->execute(array($_POST['descrip'], $_POST['status'], $_GET['task_id'])); if($result && $stmt->rowCount !== 0) { $msg = 'Task has been updated successfully'; } else { $msg = 'Sorry unable to update task.'; } } catch (PDOException $e) { $db->rollBack(); die($e->getMessage()); } } } I still get the success message as well as the following error message Notice: Undefined property: PDOStatement::$rowCount in /home/sites/it-doneright.co.uk/public_html/admin/staff-tasks/update-staff-task.php on line 43 Line 43 is below $msg = 'Task has been updated successfully'; which is from the coding below if($result && $stmt->rowCount !== 0) { $msg = 'Task has been updated successfully'; } else { $msg = 'Sorry unable to update task.'; }

I put the code in so looks like the following // if form is submitted update task details if ($_SERVER['REQUEST_METHOD']=='POST') { // was data sent if ($_POST['descrip'] != '') { try { $sql = "UPDATE task SET description = ?, status = ? WHERE task_id = ?"; $stmt = $db->prepare($sql); $stmt->execute(array($_POST['descrip'], $_POST['status'], $_GET['task_id'])); $result = $stmt->execute(array($_POST['descrip'], $_POST['status'], $_GET['task_id'])); if($result && $stmt->rowCount !== 0) { $msg = 'Task has been updated successfully'; } else { $msg = 'Sorry unable to update task.'; } } catch (PDOException $e) { $db->rollBack(); die($e->getMessage()); } } } Then before the form tag I got <?php if(isset($msg)): ?> <p><?=$msg; ?></p> <?php endif; ?> It is producing the success message but at the same time, it is producing a error Notice: Undefined property: PDOStatement::$rowCount in /home/sites/it-doneright.co.uk/public_html/admin/staff-tasks/update-staff-task.php on line 43 Line 43 is below if($result && $stmt->rowCount !== 0) {

Ok think I am slowly getting it now I now have the following code // if form is submitted update task details if ($_SERVER['REQUEST_METHOD']=='POST') { // was data sent if ($_POST['descrip'] != '') { try { $sql = "UPDATE task SET description = ?, status = ? WHERE task_id = ?"; $stmt = $db->prepare($sql); $stmt->execute(array($_POST['descrip'], $_POST['status'], $_GET['task_id'])); } catch (PDOException $e) { $db->rollBack(); die($e->getMessage()); } } } It has worked perfect by looks of it as has updated the data, is it possible to echo a update success message into the code and a failed update message using else, would it go after the following line? $stmt->execute(array($_POST['descrip'], $_POST['status'], $_GET['task_id']));

So would it look like the following if(!isset($_GET['task_id'])){ //select goes here // return the task which matches $_GET['task_id']; $sql=$dbh->prepare(" SELECT task_id , description , status FROM task WHERE task_id = ?"); } else{ //update goes here $sql=$dbh->prepare(" UPDATE task SET description ='".$description."' , status ='".$status."' WHERE task_id = '".$task_id."' "); unset($_GET['task_id']); }

I think I have solved the undefined variable errors by adding the following above the update query $description = ''; $status = ''; $task_id = ''; I clicked update and has not updated the data, am I missing a isset post submit or something? Sorry trying to work it out

I have made the action part blank so just looks like the following now <form method="post" action=""> I have added in the update query as below but got a few errors I put the following coding in above select query $sql=$dbh->prepare(" UPDATE task SET description ='".$description."' , status ='".$status."' WHERE task_id = '".$task_id."' "); below are the errors I have Notice: Undefined variable: description in /home/sites/it-doneright.co.uk/public_html/admin/staff-tasks/update-staff-task.php on line 34 Notice: Undefined variable: status in /home/sites/it-doneright.co.uk/public_html/admin/staff-tasks/update-staff-task.php on line 35 Notice: Undefined variable: task_id in /home/sites/it-doneright.co.uk/public_html/admin/staff-tasks/update-staff-task.php on line 36 To solve them, do I just do the following $description = $_POST['description']; $status= $_POST['status']; $task_id = $_POST['task_id']; or is it not as easy at that Below is my whole code now <?php ini_set('display_startup_errors',1); ini_set('display_errors',1); error_reporting(-1); ?> <? session_start(); if($_SESSION['user']==''){ header("Location:login.php"); }else{ include("config.php"); $sql=$dbh->prepare("SELECT * FROM employee WHERE emp_id=?"); $sql->execute(array($_SESSION['user'])); while($r=$sql->fetch()){ echo "<div class='home-content'>"; echo "<center><h2>Hello, ".$r['username']."</h2>"; echo "<a href='logout.php'>Log Out</a></center>"; echo "</div>"; } } ?> <?php $hostname=''; $username=''; $password=''; $db = new PDO("mysql:host=$hostname;dbname=",$username,$password); $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $sql=$dbh->prepare(" UPDATE task SET description ='".$description."' , status ='".$status."' WHERE task_id = '".$task_id."' "); // return the task which matches $_GET['task_id']; $sql=$dbh->prepare(" SELECT task_id , description , status FROM task WHERE task_id = ?"); $sql->execute(array($_GET['task_id'])); $sql->setFetchMode(PDO::FETCH_ASSOC); // fetch the row from the result $row = $sql->fetch(); function emps_assigned_by_taskid($db, $task_id) /******************************************* * function to list employees with checkboxes - checkbox is checked if they are assigned to the task ********************************************/ { $sql = "SELECT e.emp_id, e.emp_name, IF(a.emp_id IS NULL, 0, 1) as isAssigned FROM employee e LEFT JOIN assignment a ON e.emp_id = a.emp_id AND a.task_id = ?"; $stmt = $db->prepare($sql); $stmt = $db->prepare($sql); $stmt->execute(array($task_id)); $emps=''; foreach($stmt->fetchAll() as $row) { // if isAssigned is set to 1 then set the checked attribute, otherwise leave blank $checked = $row['isAssigned'] == 1 ? ' checked="checked" ' : ''; $emps .= "<input type='checkbox' name='emp_id[]' value='{$row['emp_id']}'{$checked}> {$row['emp_name']}<br>"; } return $emps; } ?> <html> <head> <title>Update Task</title> </head> <body> <div id='title'> <h1>Edit Task</h1> <form method="post" action=""> <fieldset> <legend>Task</legend> <div class='label'><label for='descrip'>Description</label></div> <input type="text" name="descrip" id="descrip" size="50" value="<?php echo $row['description']; ?>" /> </fieldset> <br><br> Current Status is: <strong><?php echo $row['status']; ?></strong> <br><br> <div class='label'><label for='status'>Status</label></div> <select name='status' id='status'> <option value='Not Started'>Not started</option> <option value='In Progress'>In progress</option> <option value='Completed'>Completed</option> </select> </fieldset> <br><br> <fieldset> <legend>Assign to</legend> <?= emps_assigned_by_taskid($db, $_GET['task_id']) ?> </fieldset> <input type="submit" name="btnSubmit" value="Update"> </form> </div> </body> </html>

Hi I got the description and status now pulled from the database but when I click update button, I get the following errors Notice: Undefined index: task_id in /home/sites/it-doneright.co.uk/public_html/admin/staff-tasks/update-staff-task.php on line 41 Notice: Undefined index: task_id in /home/sites/it-doneright.co.uk/public_html/admin/staff-tasks/update-staff-task.php on line 101 On them lines are the following line 41 $sql->execute(array($_GET['task_id'])); Line 101 <?= emps_assigned_by_taskid($db, $_GET['task_id']) ?> Is it better if I post the whole code or is it ok as it is above?

Thank you so much, got no errors now, only thing need to sort out is the description and status is not being pulled in from the db?

Hi Sorry I have done that and got the following error Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[42S22]: Column not found: 1054 Unknown column 'e.emp_id' in 'field list'' in /home/sites/it-doneright.co.uk/public_html/admin/staff-tasks/update-staff-task.php:58 Stack trace: #0 /home/sites/it-doneright.co.uk/public_html/admin/staff-tasks/update-staff-task.php(58): PDOStatement->execute(Array) #1 /home/sites/it-doneright.co.uk/public_html/admin/staff-tasks/update-staff-task.php(97): emps_assigned_by_taskid() #2 {main} thrown in /home/sites/it-doneright.co.uk/public_html/admin/staff-tasks/update-staff-task.php on line 58 The description field is still blank and the status is not pulled in from the db, sorry

I have made the changes but can it be checked over as on the page, I have the description input field blank and the status is not pulled from the database and got the following error Fatal error: Uncaught exception 'PDOException' with message 'SQLSTATE[HY093]: Invalid parameter number: parameter was not defined' in /home/sites/it-doneright.co.uk/public_html/admin/staff-tasks/update-staff-task.php:58 Stack trace: #0 /home/sites/it-doneright.co.uk/public_html/admin/staff-tasks/update-staff-task.php(58): PDOStatement->execute(Array) #1 /home/sites/it-doneright.co.uk/public_html/admin/staff-tasks/update-staff-task.php(96): emps_assigned_by_taskid() #2 {main} thrown in /home/sites/it-doneright.co.uk/public_html/admin/staff-tasks/update-staff-task.php on line 58 Below is the code on line 58 $stmt->execute(array('task_id' => $task_id)); Below is the whole code <?php ini_set('display_startup_errors',1); ini_set('display_errors',1); error_reporting(-1); ?> <? session_start(); if($_SESSION['user']==''){ header("Location:login.php"); }else{ include("config.php"); $sql=$dbh->prepare("SELECT * FROM employee WHERE emp_id=?"); $sql->execute(array($_SESSION['user'])); while($r=$sql->fetch()){ echo "<div class='home-content'>"; echo "<center><h2>Hello, ".$r['username']."</h2>"; echo "<a href='logout.php'>Log Out</a></center>"; echo "</div>"; } } ?> <?php $hostname=''; $username=''; $password=''; $db = new PDO("mysql:host=$hostname;dbname=",$username,$password); $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); // return the task which matches $_GET['task_id']; $sql=$dbh->prepare(" SELECT task_id description status FROM task WHERE task_id = ?"); $sql->execute(array($_GET['task_id'])); $sql->setFetchMode(PDO::FETCH_ASSOC); // fetch the row from the result $row = $sql->fetch(); function emps_assigned_by_taskid($db, $task_id) /******************************************* * function to list employees with checkboxes - checkbox is checked if they are assigned to the task ********************************************/ { $sql = "SELECT e.emp_id, e.emp_name, IF(a.emp_id IS NULL, 0, 1) as isAssigned FROM employee LEFT JOIN assignment a ON e.emp_id = a.emp_id WHERE a.task_id = ?"; $stmt = $db->prepare($sql); $stmt->execute(array('task_id' => $task_id)); $emps=''; foreach($stmt->fetchAll() as $row) { // if isAssigned is set to 1 then set the checked attribute, otherwise leave blank $checked = $row['isAssigned'] == 1 ? ' checked="checked" ' : ''; $emps .= "<input type='checkbox' name='emp_id[]' value='{$row['emp_id']}'{$checked}> {$row['emp_name']}<br>"; } return $emps; } ?> <html> <head> <title>Update Task</title> </head> <body> <div id='title'> <h1>Edit Task</h1> <form method="post" action="update-staff-task.php"> <fieldset> <legend>Task</legend> <div class='label'><label for='descrip'>Description</label></div> <input type="text" name="descrip" id="descrip" size="50" value="<?php echo $row['description']; ?>" /> <br> <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_assigned_by_taskid($db, $_GET['task_id']) ?> </fieldset> <input type="submit" name="btnSubmit" value="Update"> </form> </div> </body> </html>

Hi I have a update, I have managed to get the form displaying but got a error in the description input field The error is below <br /><b>Notice</b>: Undefined variable: row in <b>/home/sites/it-doneright.co.uk/public_html/admin/staff-tasks/update-staff-task.php</b> on line <b>76</b><br /> The code I have now is below <?php ini_set('display_startup_errors',1); ini_set('display_errors',1); error_reporting(-1); ?> <? session_start(); if($_SESSION['user']==''){ header("Location:login.php"); }else{ include("config.php"); $sql=$dbh->prepare("SELECT * FROM employee WHERE emp_id=?"); $sql->execute(array($_SESSION['user'])); while($r=$sql->fetch()){ echo "<div class='home-content'>"; echo "<center><h2>Hello, ".$r['username']."</h2>"; echo "<a href='logout.php'>Log Out</a></center>"; echo "</div>"; } } ?> <?php $hostname=''; $username=''; $password=''; $db = new PDO("mysql:host=$hostname;dbname=",$username,$password); $db->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION); $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'])); $sql->setFetchMode(PDO::FETCH_ASSOC); 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> <title>Update Task</title> </head> <body> <div id='title'> <h1>Edit Task</h1> <form method="post" action="update-staff-task.php"> <fieldset> <legend>Task</legend> <div class='label'><label for='descrip'>Description</label></div> <input type="text" name="descrip" id="descrip" size="50" value="<?php echo $row['description']; ?>" /> <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="Update"> </form> </div> </body> </html> Can someone take a look at my code please and see where I am going wrong, thank you