Jump to content

insert data to database using form


ianhaney

Recommended Posts

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

 

 

Link to comment
Share on other sites

  • Replies 73
  • Created
  • Last Reply

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

Link to comment
Share on other sites

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

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

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

Link to comment
Share on other sites

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

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

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

Link to comment
Share on other sites

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

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

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

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

Link to comment
Share on other sites

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

$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.

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.