insert data to database using form

[ianhaney]

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?

[ianhaney]

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?

[Ch0cu3r]

You are getting that error because when the form is submited it is not passing the task_id in the query string, therefor the variable $_GET['task_id'] will no longer exist.

If you are going to be submitting the form to itself, then leave the form action blank

<form method="post" action="">

If you want the description and status to be updated when the form is submitted then you need to use an update query, you will want to to do this before the select query.

Edited November 24, 2015 by Ch0cu3r

[ianhaney]

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>

[ianhaney]

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

[Muddy_Funster]

Yeah, you don't want to do that, you are just forcing the variables to be empty strings.  I'm coming to the party a bit late here, so I may miss the mark, but I would suggest that what you want to do is wrap the SELECT  and update inside an isset check on the $_GET array, so that it performs a different action depending on whither you have selected the task id or not

 

if(!isset($_GET['task_id']){
//select goes here
}
else{
//update goes here
unset($_GET['task_id']);
}

 

Also, I see you have been using prepared statements for the SELECT, but have chosen not to do this for the UPDATE - was there a reason for this?

[ianhaney]

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

[Ch0cu3r]

No, you dont to do as Muddy_Funster suggested.

 

What you want to do is similar to what Barand used for inserting the data. You only need to perform the update query if a post request has been made. Something like this

// 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([$_POST['descrip'], $_POST['status'], $_GET['task_id']]);
        }
        catch (PDOException $e) {
            $db->rollBack();
            die($e->getMessage());
        }
    }
}

// select query here

Edited November 24, 2015 by Ch0cu3r

[ianhaney]

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

[Ch0cu3r]

Yes, you would check to make sure the update query did execute and it did affect a row by checking PDOStatement::rowCount

$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.';
}

Then before the opening <form> tag output the message

<?php if(isset($msg)): ?>
   <p><?=$msg; ?></p>
<?php endif; ?>

[ianhaney]

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) {

[ianhaney]

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.';
}

[Ch0cu3r]

After rowCount add  ()  , it is a function not a variable.

[ianhaney]

Thank you so much, is perfect now

[ianhaney]

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

[ianhaney]

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.

[benanamen]

You are using fieldset and legend wrong. There is only ONE fieldset and ONE legend per form. You should work on your code formatting. It will make it easier to read. There are several established styles to chose from. You can see a list of them here: http://www.terminally-incoherent.com/blog/2009/04/10/the-only-correct-indent-style/

Also, you keep escaping out of Php when you are still in Php.

 

 

Formatted code from your last code post:

<?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;
    }

?>

Edited November 24, 2015 by Barand
add code tags

[Ch0cu3r]

I thought you are setting up tasks for assigning to employees? Why is customer information being inserted into the tasks table? Should that information not be stored in its own table?

 

What does customer details go to do with the task?

[Barand]

You are using fieldset and legend wrong. There is only ONE fieldset and ONE legend per form.

 

@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)

[ianhaney]

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

[Barand]

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]

@Barand, it was supposed to say form "element". He is missing an opening tag for a fieldset. I didn't use the code tags because the code was highlighted and formatted.  "bovine excrement!" LOL! I will have to use that one.

[ianhaney]

@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

Edited November 24, 2015 by ianhaney

[Barand]

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.