insert data to database using form

[ianhaney]

Within the code I have, I have added the database credentials but removed them in my post for security reasons

[ianhaney]

Sorry was my fault, the database credentials were wrong so corrected them now and is inserting the data to the task table now but unsure how to add the data to a second table as well as my first table is assignment that has assign_id, emp_id and task_id

 

the second table is employee which has emp_id, username, password

 

and the third is task table which has task_id, description, status and they are all joined but unsure how to get the task id added to assignment table as well

[Jacques1]

Well, there's definitely no table named “task” in the database you've selected. So you need to double-check both your credentials and your database. We cannot do that for you.

[ianhaney]

Sorry was my fault, the database credentials were wrong so corrected them now and is inserting the data to the task table now but unsure how to add the data to a second table as well as my first table is assignment that has assign_id, emp_id and task_id

 

the second table is employee which has emp_id, username, password

 

and the third is task table which has task_id, description, status and they are all joined but unsure how to get the task id added to assignment table as well

[Ch0cu3r]

TOPICS MERGED PLEASE DO NOT START NEW TOPICS FOR THE SAME PROBLEM/QUESTION

[ianhaney]

Sorry I was looking to see how to delete the other one or this one

[ianhaney]

would I need to do a INSERT then SELECT in the same query to get the task_id from the task table to show in the assign table on insert

 

Hope that makes sense

[Ch0cu3r]

would I need to do a INSERT then SELECT in the same query to get the task_id from the task table to show in the assign table on insert

 

Hope that makes sense

No, task_id in the task table should be primary key( set to auto increment). When you insert a new task call mysqli_insert_id after executing the query to get the task id for the row that was just inserted.

[ianhaney]

Sorry got bit lost, the task_id is auto increment and primary key

 

would something like the following work?

INSERT INTO tbl(field, field) VALUES(val, val); INSERT INTO tbl2(field2, field2) VALUES(val2, val2);

[ianhaney]

I tried the following code but just got a error

$sql = "INSERT INTO task (description,status) SELECT task_id FROM task;
INSERT INTO assignment (task_id) SELECT task_id FROM task";

ERROR: Could not able to execute INSERT INTO task (description,status) VALUES ('replace battery test 4', 'In Process') SELECT task_id FROM task; INSERT INTO assignment (task_id) SELECT task_id FROM task. You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near 'SELECT task_id FROM task; INSERT INTO assignment (task_id) SELECT task_id FROM t' at line 1

[Barand]

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>

[ianhaney]

Hi Barand

 

I put that code in but in dreamweaver, it says there is syntax error with the following lines

$stmt->execute([$_POST['descrip'], $_POST['status']]);

$stmt->execute([$task_id, $emp]);

[Barand]

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

[ianhaney]

Thank you so much, works perfect

 

I can now create the edit task form, am guessing will be similar to the form for inserting but will be UPDATE instead of INSERT for the query

[ianhaney]

Got stuck again trying to work the UPDATE form out, I got the following so far

<?php
ini_set('display_startup_errors',1);
ini_set('display_errors',1);
error_reporting(-1);
?>

<?php
$hostname='localhost';
$username='';
$password='';
?>

<?php
$db = new PDO("mysql:host=$hostname;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 = "UPDATE task (description, status) VALUES (?,?) WHERE task_id = $task_id";
            $stmt = $db->prepare($sql);
            $stmt->execute(array($_POST['descrip'], $_POST['status']));
            $task_id = $db->lastInsertId(); // get the id of new task
            
            // now insert employees assigned to the task
            $sql = "UPDATE assignment(task_id, emp_id) VALUES (?,?) WHERE task_id = $task_id";
            $stmt = $db->prepare($sql);
            foreach ($_POST['emp_id'] as $emp) {
                $stmt->execute(array($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>
<title>Add Task</title>
</head>
<body>
<div id='title'>
<h1>Add 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="Submit">
</form>
</div>
</body>
</html>

I know or think I need to put in a while loop or something somewhere just after the form I think and need the SELECT query as well but unsure where it all goes, sorry

[Barand]

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.

[ianhaney]

Ok so to do that, would it be using SELECT query and a WHERE clause to get the correct task_id to update

[ianhaney]

Sorry am trying to get my head around this, I have got the correct task_id in the url to update as the url looks like the following

 

http://www.it-doneright.co.uk/admin/staff-tasks/update-staff-task.php?task_id=3

 

but in the description input field, I have <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>70</b><br />

 

then is not pulling any data out for example the checkboxes and status is not pulled from the database which is where I am stuck as unsure how to do that part

[ianhaney]

Hi

 

Still trying to work out the update task issue but am still stuck

 

The page does not show any errors but does not show any data from the database tables to update

 

I have the following 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
include("config.php");

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

?>

<html>
<head>
<title>Add 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="Submit">
<?php } ?>
</form>
</div>
</body>
</html>

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

[ianhaney]

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

[Ch0cu3r]

Not sure why you are querying the employee table when you are editing a task. 

 

You need to be querying the task table to return the row where the task_id column matches the task_id query string parameter ($_GET['task_id']).

// return the task which matches $_GET['task_id'];
$sql=$dbh->prepare("
    SELECT task_id
           description 
    FROM task
    WHERE task_id = ?");
$sql->execute(array($_GET['task_id']));

After calling  $sql->setFetchMode(PDO::FETCH_ASSOC);  you need to actually fetch the row using

// fetch the row from the result
$row = $sql->fetch();

To show which employess that are assigned to the task you need to create a new function, you pass the task id to the function when calling it. The function will query the employees table, joining the assignment table on the emp_id columns. You will check the checkbox if the emp_id is not null in the joined table.

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

To call the function you will use  <?= emps_assigned_by_taskid($db, $_GET['task_id']) ?>

[ianhaney]

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>

[Ch0cu3r]

Sorry, Remove  'task_id' =>   from this line

$stmt->execute(array('task_id' => $task_id));

So it reads as

$stmt->execute(array($task_id));

[ianhaney]

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

[Ch0cu3r]

Left off the table alias and also the WHERE clause should of been AND

    $sql = "SELECT e.emp_id, 
                   e.emp_name,
                   IF(a.emp_id IS NULL, 0, 1) as isAssigned
            FROM employees e
            LEFT JOIN assignment a ON e.emp_id = a.emp_id AND a.task_id = ?";
    $stmt = $db->prepare($sql);