Jump to content

insert data to database using form


ianhaney

Recommended Posts

  • Replies 73
  • Created
  • Last Reply

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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>

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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.

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

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

Link to comment
Share on other sites

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

Link to comment
Share on other sites

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

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

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.