insert data to database using form

ianhaney · November 20, 2015

Hi

Sorry if is in the wrong place but was seeing if anyone knows of a script that can do the following

1) login to an admin side using admin login info

2) add tasks and assign them to employees

3) the employee can log in with their own log in info

4) the employee can then view their tasks that are just for them and not view others that are assigned to other employees

5) update the task themselves once completed

6) admin can also see the task and the update of it if it has been completed or working on

Thank you in advance

Ian

Barand · November 20, 2015

Not a difficult problem. It's basically maintaining three tables

                                                                                                                          
       +-------------+                                             +--------------+                                       
       | employee    |                                             | task         |                                       
       +-------------+                                             +--------------+                                       
       | emp_id (PK) |                                             | task_id (PK) |                                       
       | empname     |                                             | description  |                                       
       +-------------+                                             | status       |                                       
              |                                                    +--------------+                                       
              |                                                           |                                               
              |                                                           |                                               
              |                                                           |                                               
              |                      +----------------+                   |                                               
              |                      | assignment     |                   |                                               
              |                      +----------------+                   |                                               
              +---------------------<| assign_id (PK) |>------------------+                                               
                                     | emp_id         |                                                                   
                                     | task_id        |                                                                   
                                     +----------------+

Use the assignment table to find the tasks assigned to an employee

ianhaney · November 20, 2015

Hi Barand

Thank you for the reply, I have created the database with the three tables and created the db connect php file which is the easy part and now just created a view page, at the mo I have not done the login etc for it as that is quite straight forward but thinking about it, I need to do that first to make sure when logging in that that specific employee can only see their tasks and no one elses

So far I have the following, does the SELECT line look ok as always get bit confused on the JOINS part

$sql = "SELECT emp_id, emp_name FROM employee JOIN task ON employee.emp_id = task.task_id JOIN assignment on employee.assign_id = assignment.task_id";

Barand · November 20, 2015

You join table on the matching fields.

So join employee to assignment using the emp_id fields and join task to assignment using the task_id fields.

SELECT 
  e.emp_id
, e.emp_name
, t.description 
FROM employee e
JOIN assignment a ON e.emp_id = a.emp_id
JOIN task t ON a.task_id = t.task_id

ianhaney · November 20, 2015

Just a update, I have built the register and login php scripts and that works perfect and now working on the view page but got stuck with it a bit

I got the following code

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

<?php

include("db-connect.php");

// get the records from the database
                        if ($result = $mysqli->query("SELECT 
  e.emp_id
, t.description 
FROM employee e
JOIN assignment a ON e.emp_id = a.emp_id
JOIN task t ON a.task_id = t.task_id"))
						
                        {
                                // display records if there are records to display
                                if ($result->num_rows > 0)
                                {
                                        // display records in a table
                                        echo "<table>";
                                        
                                        // set table headers
                                        echo "<tr>
										<th>Task ID</th>
										<th>Description</th>
										<th>Status</th>
										<th>Emp ID</th>
										<th>Status</th>
										<th colspan='2'>Actions</th>
										</tr>";
                                        
                                        while ($row = $result->fetch_object())
                                        {
                                                // set up a row for each record
                                                echo "<tr>";
                                                echo "<td>" . $row->task_id . "</td>";
                                                echo "<td>" . $row->description . "</td>";
                                                echo "<td>" . $row->status . "</td>";
												echo "<td>" . $row->emp_id . "</td>";
												echo "<td>" . $row->status . "</td>";
                                                echo "<td><a href='records.php?task_id=" . $row->task_id . "'>Edit</a></td>";
                                                echo "</tr>";
                                        }
                                        
                                        echo "</table>";
                                }
                                // if there are no records in the database, display an alert message
                                else
                                {
                                        echo "No results to display!";
                                }
                        }
                        // show an error if there is an issue with the database query
                        else
                        {
                                echo "Error: " . $mysqli->error;
                        }
                        
                        // close database connection
                        $mysqli->close();

?>

I get the following errors

Notice: Undefined property: stdClass::$task_id in /home/sites/it-doneright.co.uk/public_html/admin/staff-tasks/view-employee-tasks.php on line 40

Notice: Undefined property: stdClass::$status in /home/sites/it-doneright.co.uk/public_html/admin/staff-tasks/view-employee-tasks.php on line 42

Notice: Undefined property: stdClass::$status in /home/sites/it-doneright.co.uk/public_html/admin/staff-tasks/view-employee-tasks.php on line 44

Notice: Undefined property: stdClass::$task_id in /home/sites/it-doneright.co.uk/public_html/admin/staff-tasks/view-employee-tasks.php on line 45

It is pulling the following info from the database

Description and the emp_id but not the task_id or the status

ianhaney · November 20, 2015

Sorry think I sussed it

I added the following to the select query

, t.status 
, t.task_id

ianhaney · November 20, 2015

How do I make it so that the employee logged in sees just their tasks and not everyones tasks?

Barand · November 20, 2015

When the user logs in, store their id in $_SESSION variable. Then you uses a WHERE clause to get that users data

SELECT 
  e.emp_id
, e.emp_name
, 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 = $loggedInUser

ianhaney · November 20, 2015

Is that bit like what I have done below

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

Barand · November 20, 2015

yes

ianhaney · November 20, 2015

Ok

Instead of $LoggedInUser, what would it be as not got $loggedInUser in where I am storing their id in the $_SESSION variable

Barand · November 20, 2015

$loggedInUser is just an example to demonstrate the query syntax. So in your case it will probably be

$sql=$dbh->prepare("SELECT 
      e.emp_id
    , e.emp_name
    , 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']));

ianhaney · November 20, 2015

Hmm def don't like something in my coding as got the following errors

Warning: mysqli::query() [mysqli.query]: (42000/1064): 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 '?' at line 9 in /home/sites/it-doneright.co.uk/public_html/admin/staff-tasks/view-employee-tasks.php on line 48

Notice: Trying to get property of non-object in /home/sites/it-doneright.co.uk/public_html/admin/staff-tasks/view-employee-tasks.php on line 53
No results to display!

The whole coding I have is below

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

<!DOCTYPE html>
<html>
 <head>
 <title>View Tasks</title>
<link rel="stylesheet" type="text/css" media="screen" href="css/login-styles.css" />
 </head>
 <body>

<h2>View Tasks</h2>

<?
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("db-connect.php");

// get the records from the database
                        if ($result = $mysqli->query("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']));
						
                        {
                                // display records if there are records to display
                                if ($result->num_rows > 0)
                                {
                                        // display records in a table
                                        echo "<table>";
                                        
                                        // set table headers
                                        echo "<tr>
										<th>Task ID</th>
										<th>Description</th>
										<th>Status</th>
										<th>Emp ID</th>
										<th>Status</th>
										<th colspan='1'>Actions</th>
										</tr>";
                                        
                                        while ($row = $result->fetch_object())
                                        {
                                                // set up a row for each record
                                                echo "<tr>";
                                                echo "<td>" . $row->task_id . "</td>";
                                                echo "<td>" . $row->description . "</td>";
                                                echo "<td>" . $row->status . "</td>";
												echo "<td>" . $row->emp_id . "</td>";
												echo "<td>" . $row->status . "</td>";
                                                echo "<td><a href='records.php?task_id=" . $row->task_id . "'>Edit</a></td>";
                                                echo "</tr>";
                                        }
                                        
                                        echo "</table>";
                                }
                                // if there are no records in the database, display an alert message
                                else
                                {
                                        echo "No results to display!";
                                }
                        }
                        // show an error if there is an issue with the database query
                        /*else
                        {
                                echo "Error: " . $mysqli->error;
                        }*/
                        
                        // close database connection
                        $mysqli->close();

?>

 </body>
</html>

Barand · November 20, 2015

why have you changed "prepare" to "query"?

ianhaney · November 20, 2015

Sorry I didn't, I had that in already

I changed it to prepare now and have no errors now but is displaying no results

ianhaney · November 20, 2015

I thought it was cause the task was assigned to emp_id in the database but just changed it to 2 thinking the task would show cause emp_id 2 is signed in but still says 0 results

Barand · November 20, 2015

Does your assignment table contain any records for emp_id = 2?

ianhaney · November 20, 2015

Yeah in there is assign id of 1, emp_id of 2 and task_id of 1

Barand · November 20, 2015

Do you still have this line?

while ($row = $result->fetch_object())

because you don't have a $result object, you have $sql as a statement object. Do it like you did in the query above that one in your code.

ianhaney · November 20, 2015

I currently have the following code

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

<!DOCTYPE html>
<html>
 <head>
 <title>View Tasks</title>
<link rel="stylesheet" type="text/css" media="screen" href="css/login-styles.css" />
 </head>
 <body>

<h2>View Tasks</h2>

<?
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("db-connect.php");

// get the records from the database
                        if ($result = $mysqli->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']));
						
                        {
                                // display records if there are records to display
                                if ($result->num_rows > 0)
                                {
                                        // display records in a table
                                        echo "<table>";
                                        
                                        // set table headers
                                        echo "<tr>
										<th>Task ID</th>
										<th>Description</th>
										<th>Status</th>
										<th>Emp ID</th>
										<th>Status</th>
										<th colspan='1'>Actions</th>
										</tr>";
                                        
                                        while ($row = $result->fetch_object())
                                        {
                                                // set up a row for each record
                                                echo "<tr>";
                                                echo "<td>" . $row->task_id . "</td>";
                                                echo "<td>" . $row->description . "</td>";
                                                echo "<td>" . $row->status . "</td>";
												echo "<td>" . $row->emp_id . "</td>";
												echo "<td>" . $row->status . "</td>";
                                                echo "<td><a href='records.php?task_id=" . $row->task_id . "'>Edit</a></td>";
                                                echo "</tr>";
                                        }
                                        
                                        echo "</table>";
                                }
                                // if there are no records in the database, display an alert message
                                else
                                {
                                        echo "No results to display!";
                                }
                        }
                        // show an error if there is an issue with the database query
                        /*else
                        {
                                echo "Error: " . $mysqli->error;
                        }*/
                        
                        // close database connection
                        $mysqli->close();

?>

 </body>
</html>

sorry am bit lost what to change, is it this line

if ($result = $mysqli->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']));

ianhaney · November 20, 2015

Hi

I have altered the coding slightly and got no errors now but no results still?

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

<!DOCTYPE html>
<html>
 <head>
 <title>View Tasks</title>
<link rel="stylesheet" type="text/css" media="screen" href="css/login-styles.css" />
 </head>
 <body>

<h2>View Tasks</h2>

<?
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']));
						
                        {
                                // display records if there are records to display
                                //if ($sql->num_rows > 0)
								if ($sql->fetchColumn() > 0)
                                {
                                        // display records in a table
                                        echo "<table>";
                                        
                                        // set table headers
                                        echo "<tr>
										<th>Task ID</th>
										<th>Description</th>
										<th>Status</th>
										<th>Emp ID</th>
										<th>Status</th>
										<th colspan='1'>Actions</th>
										</tr>";
                                        
                                        while ($row = $sql->fetchColumn())
                                        {
                                                // set up a row for each record
                                                echo "<tr>";
                                                echo "<td>" . $row->task_id . "</td>";
                                                echo "<td>" . $row->description . "</td>";
                                                echo "<td>" . $row->status . "</td>";
												echo "<td>" . $row->emp_id . "</td>";
												echo "<td>" . $row->status . "</td>";
                                                echo "<td><a href='records.php?task_id=" . $row->task_id . "'>Edit</a></td>";
                                                echo "</tr>";
                                        }
                                        
                                        echo "</table>";
                                }
                                // if there are no records in the database, display an alert message
                                else
                                {
                                        echo "No results to display!";
                                }
                        }
						
                        // show an error if there is an issue with the database query
                       /*else {
                                echo "Error: " . $mysqli->error;
                        }*/
                        
                        // close database connection
                        $dbh = null;

?>

 </body>
</html>

Barand · November 21, 2015

you need to use fetchObject() and not fetchColumn().

This worked for me

$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())
{
        // display records in a table
        echo "<table>";
        
        // set table headers
        echo "<tr>
        <th>Task ID</th>
        <th>Description</th>
        <th>Status</th>
        <th>Emp ID</th>
        <th>Status</th>
        <th colspan='1'>Actions</th>
        </tr>";
        
       
        do {
                // set up a row for each record
                echo "<tr>";
                echo "<td>" . $row->task_id . "</td>";
                echo "<td>" . $row->description . "</td>";
                echo "<td>" . $row->status . "</td>";
                echo "<td>" . $row->emp_id . "</td>";
                echo "<td>" . $row->status . "</td>";
                echo "<td><a href='records.php?task_id=" . $row->task_id . "'>Edit</a></td>";
                echo "</tr>";
        }  while ($row = $sql->fetchObject());
        
        echo "</table>";
}
// if there are no records in the database, display an alert message
else
{
        echo "No results to display!";
}

ianhaney · November 21, 2015

Hi Barand

Thank you so much, is working perfect

I can build the admin form now to add the tasks and assign them to employees, hopefully will be straight forward to do

if get stuck, is it ok to post

ianhaney · November 21, 2015

Hi

I have built the form and a php file to insert the data but got the following issue

ERROR: Could not able to execute INSERT INTO task (description, status) VALUES ('replace battery test', 'In Process'). Table 'cl10-itdonerig.task' doesn't exist

Below is the code I have for the form and insert data php file

form php file

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

<!DOCTYPE html>
<html lang="en">
<head>
<meta charset="UTF-8">
<title>Add Staff Task</title>
<link rel="stylesheet" type="text/css" media="screen" href="css/styles.css" />
</head>
<body>

<div id="logo">
<img src="images/logo/it-done-right.jpg" alt="" title="">
</div>

<?
session_start();

if($_SESSION['user']==''){
 header("Location:index.php");
}else{
 include("config.php");
 $sql=$dbh->prepare("SELECT * FROM users WHERE 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>";
 }
}
?>

<br>

<form action="insert-staff-task.php" method="post" class="basic-grey">
        <label for="description">Description:</label>
        <input type="text" name="description" id="description">
        <br><br>
        <label for="employee">Assign to Employee:</label>
        <select name="employee">
        <option value="Ian Haney (emp id: 2)">Ian Haney</option>
        <option value="Phil Roskams emp id: 3)">Phil Roskams</option>
        </select>
		<br><br>
        <label for="status">Status:</label>
        <select name="status">
        <option value="In Process">In Process</option>
        <option value="Complete">Complete</option>
        </select>
		<br><br>
    <input type="submit" value="Add Records">
</form>
</body>
</html>

insert data php file

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

<?php

/* Attempt MySQL server connection. Assuming you are running MySQL
server with default setting (user 'root' with no password) */
$link = mysqli_connect("", "", "", "");
 
// Check connection
if($link === false){
    die("ERROR: Could not connect. " . mysqli_connect_error());
}
 
// Escape user inputs for security
$description = mysqli_real_escape_string($link, trim($_POST['description']));
$status = mysqli_real_escape_string($link, trim($_POST['status']));
 
// attempt insert query execution
$sql = "INSERT INTO task (description, status) VALUES ('$description', '$status')";
if(mysqli_query($link, $sql)){
    echo "Records added successfully.";
} else{
    echo "ERROR: Could not able to execute $sql. " . mysqli_error($link);
}
 
// close connection
mysqli_close($link);
?>

requinix · November 21, 2015

$link = mysqli_connect("", "", "", "");

Besides not specifying a server host to connect to, the username to connect with, or the password for the user, all of which are BAD THINGS, you aren't specifying the default database.

Either that table mentioned in the error message really does not exist, or you need to specify the correct default database when connecting.

Sign In

insert data to database using form

Recommended Posts

Link to comment

Share on other sites

Top Posters In This Topic

Popular Days

Top Posters In This Topic

Popular Days

Popular Posts

Barand

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Link to comment

Share on other sites

Join the conversation

Important Information