PHP question

[matthewroth]

first let me say i am new to php and this site and appreciate any help to point me in the right direction.... now on to the explanation of my question

I have a sign-in sheet for our data center that is in php

 

noc.php -

<?php
include_once('./sql_connect.php');

if (isset($_POST['submit']))
{
$Time =  date("Y-m-d H:i:s");
$FirstName = $_POST['FirstName'];
$LastName = $_POST['LastName'];
$Company = $_POST['Company'];
$Department = $_POST['Department'];
$Server = $_POST['Server'];
$System = $_POST['System'];
$Application = $_POST['Application'];


$check_denied_listSql = "SELECT * FROM nocsis_denied WHERE last_name LIKE '%LastName%'";
$check_denied_listResult = mysql_query($check_denied_listSql);
$num_of = mysql_num_rows($check_denied_listResult);

if($num_of < '0')
{
$error = "<h1><font color='red'><center><b>Access Denied</b></center></font></h1>";

} else {


$Addvisitor = mysql_query("INSERT INTO nocsis (`FirstName`, `LastName`, `Company`, `Department`, `Server`, `System`, `Application`, `TimeIn`) VALUES ('$FirstName', '$LastName', '$Company', '$Department', '$Server', '$System', '$Application', '$Time')");
if(!$Addvisitor) {
echo "Error! \n Failed to add event in the database<br />\n";
}



$getId = ("SELECT Id from nocsis ORDER BY Id DESC LIMIT 1");
$resultId = mysql_query($getId);
$NocId = mysql_result($resultId,0, "Id");

$Event = "
Name: $FirstName $LastName<BR />
Company: $Company<BR />
Department: $Department<BR />
Server: $Server<BR />
System: $System<BR />
Application: $Application<BR />
Time In: $Time<BR />";
$Action = "Added visitor to the shift report.";
$Status = "Open";
$SubmittedBy = "NOCSIS";
$AddEvent = mysql_query("INSERT INTO ShiftReport (Event, Action, Status, SubmittedBy) VALUES ('$Event', '$NocId', '$Status', '$SubmittedBy')");
}
if(!$AddEvent) {
echo "Error! \n Failed to add event in the database";
}

}



if (isset($_POST['signout']))
{
$Time =  date("Y-m-d H:i:s");
$newId = $_POST['newId'];


$query3 = ("SELECT * FROM nocsis WHERE ID='$newId'");
$result3 = mysql_query($query3);
$FirstName = mysql_result($result3,0, "FirstName");
$LastName = mysql_result($result3,0, "LastName");
$Company = mysql_result($result3,0, "Company");
$Department = mysql_result($result3,0, "Department");
$Server = mysql_result($result3,0, "Server");
$System = mysql_result($result3,0, "System");
$Application = mysql_result($result3,0, "Application");
$TimeIn = mysql_result($result3,0, "TimeIn");
$TimeOut = mysql_result($result3,0, "TimeOut");
$Action = "Time Out: $Time";



$query6 = "UPDATE `shiftreport` SET `Action`='$Action', `Status`='Closed' WHERE `SubmittedBy`='NOCSIS' && `Action`='$newId'";
$result6 = mysql_query($query6);
if(!query6)
{
echo "Update failed";
}


$query1 = "UPDATE `nocsis` SET `TimeOut`='$Time' WHERE `Id`='$newId'";
$result1 = mysql_query($query1);
if(!query1)
{
echo "Update failed";
}
$print_query1 = "UPDATE `nocsis` SET `TimeOut`='$Time' WHERE `Id`='$newId'";
}



$form ="
<form method='post' action='noc.php'>
<table border='0' width='100%'>
<tr>
<td><b>First Name</b></td>
<td><b>Last Name</b></td>
<td><b>Company</b></td>
<td><b>Department</b></td>
<td><b>Server</b></td>
<td><b>System</b></td>
<td><b>Application</b></td>
</tr><tr>
<td><input type='text' name='FirstName' size='13'></td>
<td><input type='text' name='LastName' size='13'></td>
<td><input type='text' name='Company' size='13'></td>
<td><input type='text' name='Department' size='13'></td>
<td><input type='text' name='Server' size='13'></td>
<td><input type='text' name='System' size='13'></td>
<td><input type='text' name='Application' size='13'></td>
<td><input type='submit' name='submit' value='Sign-in'></td>
</tr>
</table>
</form>";



$query = ("SELECT * FROM nocsis WHERE TimeOut IS NULL ORDER BY TimeIn ASC");
$result = mysql_query($query);
$visitors = mysql_num_rows($result);

$SignedIn = $SignedIn. 
"<table border='1' width='100%'>
<tr>
<td><b>Time In</b></td>
<td><b>Time Out</b></td>
<td><b>First Name</b></td>
<td><b>Last Name</b></td>
<td><b>Company</b></td>
<td><b>Department</b></td>
<td><b>Server</b></td>
<td><b>System</b></td>
<td><b>Application</b></td>
</tr>";


while (list($Id, $FirstName, $LastName, $Department, $Company, $Server, $System, $Application, $SignIn, $SignOut) = mysql_fetch_row($result))
{

$SignedIn = $SignedIn. 
"
<form method='post' action='noc.php'>
<tr>
<td>$SignIn</td>
<td>$SignOut</td>
<td>$FirstName</td>
<td>$LastName</td>
<td>$Company</td>
<td>$Department</td>
<td>$Server</td>
<td>$System</td>
<td>$Application</td>
<input type='hidden' name='newId' value='$Id'>
<td><input type='submit' name='signout' value='Sign-out'></td>
</tr>
</form>
";


}

$SignedIn = $SignedIn. "</table>";
echo "<div id='content'>"

.$error
.$form
."<p>"
.$SignedIn
."<p>"
."</div>";

?>

</body></html>

 

Recently I was told they want to include a photo id of users entering the building. so I created a new tabled -

 

CREATE TABLE `image` (

  `ImageId` int(10) NOT NULL auto_increment,

  `Image` longblob,

  `FileType` varchar(32) collate latin1_general_ci default NULL,

  `LastName` varchar(25) collate latin1_general_ci NOT NULL,

  PRIMARY KEY  (`ImageId`)

) ENGINE=MyISAM  DEFAULT CHARSET=latin1 COLLATE=latin1_general_ci AUTO_INCREMENT=6 ;

 

I was going to import all employees pictures into the image table but have no idea how to pull the data from both tables so after they sign in the operator would see

all information from the NOC tables and the image from the image table. I was hoping since when they sign in they use a lastname i would be able to pull the image associated with the last name. i hope this makes sense. I have been searching and racking my brain for days on this 1 and keep coming up empty.

[Barand]

It's better to save the image as a file on the server and then store path to image in the table.

 

Also, link on id rather than last name. More efficient plus last name isn't necessarily unique.

 

To get the pic

 

SELECT n.firstname, n.lastname, i.image

FROM nocsis n LEFT JOIN image i

ON n.id = i.userid

 

Note: left join in case there isn't an image for some users.