Here is what I have for my php coding.
<?php
$USER_NAME = ' ';
$USER_PASSWORD = ' ';
$DB_SERVER = " ";
$DB_NAME = $USER_NAME."_db";
$conn = mysqli_connect($DB_SERVER, $USER_NAME, $USER_PASSWORD, $DB_NAME);
$result = mysqli_query($conn," SELECT * FROM employee,workson where workson.ESSN = employee.SSN and workson.Projnum ='" . $_GET["PNO"] . "'");
$notincluded = mysqli_query($conn, "SELECT SSN, FName, Lname FROM employee where NOT EXISTS (SELECT * FROM workson WHERE employee.SSN = workson.ESSN and workson.Projnum ='" . $_GET["PNO"] . "')") ;
if (!$conn) {
echo "Could not connect: ";
echo mysqli_connect_error(); }
?>
<table border = "1">
<b>Employee List: <b>
<td>Project Number</td>
<td>SSN</td>
<td>First Name</td>
<td>Last Name</td>
<td>Working Hours</td>
<td>Delete</td>
<td>Update</td>
</tr>
<?php
$i=0;
while($row = mysqli_fetch_array($result)) {
?>
<td style = "text-align:center; vertical-align: middle;"> <?php echo $row["Projnum"]; ?></td>
<td style = "text-align:center; vertical-align: middle;"> <?php echo $row["SSN"]; ?></td>
<td style = "text-align:center; vertical-align: middle;"> <?php echo $row["FName"]; ?></td>
<td style = "text-align:center; vertical-align: middle;"> <?php echo $row["Lname"]; ?></td>
<td style = "text-align:center; vertical-align: middle;"> <?php echo $row["NoHours"]; ?> </td>
<td><a href="delete_employee.php?SSN=<?php echo $row["SSN"]; ?>&Projnum=<?php echo $row["Projnum"];?>">Delete</a></td>
<td><a href="update_employee.php?SSN=<?php echo $row["SSN"]; ?>&Projnum=<?php echo $row["Projnum"];?>">Update Hours</a></td>
</tr>
<?php
$i++;
}
?>
</table>
<br>
<br>
<form method=POST action="http://mkimas.student.ust.hk/cgi-bin/mini_project_5.php">
<b> Employee not included : </b>
<?php
$notincluded = mysqli_query($conn,
"SELECT SSN, FName, Lname FROM employee where NOT EXISTS (SELECT * FROM workson WHERE employee.SSN = workson.ESSN and workson.Projnum ='" . $_GET["PNO"] . "')") ;
$employees = mysqli_fetch_all($notincluded);
$projnum = $_GET["PNO"];
print("<select name='Employees' id='employees'>");
foreach($employees as $employee){
//You can use the index 0, which corresponds with the first selected column in query
print("<option value='$employee[0]'>$employee[0]</option>");
}
print("</select>")
?>
<br>
Input the amount of hours and which project number new employee worked on: <br>
Hours: <input name=hours type =text> <br>
<input type=submit value="Submit">
</form>
For the second part of my php, a selection list of employees not included in the table above is printed. Then, I have the php file that has a SQL query that inserts the not included employee.
<?php
echo "Start \n";
echo "<hr>";
$USER_NAME = " ";
$USER_PASSWORD = " ";
$DB_SERVER = " ";
$DB_NAME = $USER_NAME."_db";
$ESSN = $_POST["Employees"];
$Projnum = $_POST["projnum"];
$NoHours = $_POST["hours"];
$conn = mysqli_connect($DB_SERVER, $USER_NAME, $USER_PASSWORD, $DB_NAME);
if (!$conn) {
echo "Could not connect: ";
echo mysqli_connect_error(); }
$sql="insert into workson(ESSN, Projnum, NoHours) values('$ESSN', '$Projnum', '$NoHours')";
$result=mysqli_query($conn,$sql);
if($result){
echo "insert success"; }
else{
echo "insert fail"; }
mysqli_close($conn);
?>
Somehow, my coding always outputs "insert fail". What might be the problem?