Jump to content

insert data to database using form


ianhaney

Recommended Posts

  • Replies 73
  • Created
  • Last Reply

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?

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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>
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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?

Link to comment
Share on other sites

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']);
}
Link to comment
Share on other sites

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
Link to comment
Share on other sites

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']));
Link to comment
Share on other sites

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; ?>
Link to comment
Share on other sites

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) {
Link to comment
Share on other sites

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.';
}
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

?>
Link to comment
Share on other sites

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

Link to comment
Share on other sites

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       |>--------------------+                                                  
                                 +---------------+                                   

Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.


×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.