ok i see that, and thats great and all , i think i may be approaching this wrong. as that still didnt work as i expected it to.
here is what i have,
i have a table that stores information on equipment check in and out
heres what i want
when user checks out a unit the row is inserted into this table with a NULL check in date/time
user would check in this equipment at end of shift which updates the only row in the table that has a null checkin time as long as that same row has a check out time within the last 16 hours, thus ignoring any null check in greater than 16 hours ago, however this should also fail and not allow the check in due to equipment never checked in if there is a null check in older than 16 hours ago.
here is what i have :
The Functions
[php]<?php
function get_user($utmid)
{
$mysqli = new mysqli(‘localhost’, ‘root’, ‘***’, ‘lockers2’);
if ($mysqli->connect_errno) {
echo “Sorry, this website is experiencing problems.”;
echo “Error: Failed to make a MySQL connection, here is why: \n”;
echo "Errno: " . $mysqli->connect_errno . “\n”;
echo "Error: " . $mysqli->connect_error . “\n”;
exit;
}
$sql = “SELECT * FROM users WHERE tm_id = '” . $utmid . “’”;
if (!$result = $mysqli->query($sql)) {
echo “Sorry, the website is experiencing problems.”;
echo “Error: Our query failed to execute and here is why: \n”;
echo "Query: " . $sql . “\n”;
echo "Errno: " . $mysqli->errno . “\n”;
echo "Error: " . $mysqli->error . “\n”;
exit();
}
if ($result->num_rows === 0) {
echo “We could not find a match for ID $utmid, sorry about that. Please try again.”;
exit();
}
$user_data = $result->fetch_assoc();
global $ujobid;
global $utmid;
global $utmname;
$ujobid = $user_data['job_id'];
$utmid = $user_data['tm_id'];
$utmname = $user_data['tm_name'];
return $ujobid;
return $utmid;
return $utmname;
}
function get_equip($jobid)
{
$mysqli = new mysqli(‘localhost’, ‘root’, ‘***’, ‘lockers2’);
if ($mysqli->connect_errno) {
echo “Sorry, this website is experiencing problems.”;
echo “Error: Failed to make a MySQL connection, here is why: \n”;
echo "Errno: " . $mysqli->connect_errno . “\n”;
echo "Error: " . $mysqli->connect_error . “\n”;
exit;
}
$sql = “SELECT * FROM equipment WHERE job_id = '” . $jobid . “’ AND e_status = 0 ORDER BY rand() LIMIT 1”;
if (!$result = $mysqli->query($sql)) {
echo “Sorry, the website is experiencing problems.”;
echo “Error: Our query failed to execute and here is why: \n”;
echo "Query: " . $sql . “\n”;
echo "Errno: " . $mysqli->errno . “\n”;
echo "Error: " . $mysqli->error . “\n”;
exit();
}
if ($result->num_rows === 0) {
echo “We could not find a match for HW, sorry about that. Please try again.”;
exit();
}
$equip_data = $result->fetch_assoc();
global $ehwid;
global $etype;
global $ersid;
global $estatus;
global $ejobid;
global $elkrid;
global $elkrcom;
if ($equip_data['rs_inc'] == "1"){
$ersid = $equip_data['rs_id'];
}else{
$ersid = "0" ;
}
$ehwid = $equip_data['hw_id'];
$etype = $equip_data['type'];
$ersid = $equip_data['rs_id'];
$estatus = $equip_data['e_status'];
$ejobid = $equip_data['job_id'];
$elkrid = $equip_data['lkr_id'];
$elkrcom = $equip_data['lkr_com'];
return $ehwid;
return $etype;
return $ersinc;
return $ersid;
return $estatus;
return $ejobid;
return $elkrid;
return $elkrcom;
$mysqli->close();
}
function lookup_chk($tmid)
{
$mysqli = new mysqli(‘localhost’, ‘root’, ‘***’, ‘lockers2’);
if ($mysqli->connect_errno) {
echo “Sorry, this website is experiencing problems.”;
echo “Error: Failed to make a MySQL connection, here is why: \n”;
echo "Errno: " . $mysqli->connect_errno . “\n”;
echo "Error: " . $mysqli->connect_error . “\n”;
exit();
}
$sql = “SELECT * FROM checkinout WHERE tm_id = '” . $tmid . “’ AND chk_in IS NULL”;
if (!$result = $mysqli->query($sql)) {
echo “Sorry, the website is experiencing problems.”;
echo “Error: Our query failed to execute and here is why: \n”;
echo "Query: " . $sql . “\n”;
echo "Errno: " . $mysqli->errno . “\n”;
echo "Error: " . $mysqli->error . “\n”;
exit();
}
$row_cnt = mysqli_num_rows($result);
$check_data = $result->fetch_assoc();
global $ctmid;
global $ctmname;
global $chwid;
global $chwtype;
global $crsid;
global $cjobid;
global $clkrid;
global $clkrcom;
global $chkout;
global $chkin;
global $cflag;
$crsid = $check_data[‘rs_id’];
$ctmid = $check_data[‘tm_id’];
$ctmname = $check_data[‘tm_name’];
$chwid = $check_data[‘hw_id’];
$chwtype = $check_data[‘hw_type’];
$cjobid = $check_data[‘job_id’];
$clkrid = $check_data[‘lkr_id’];
$clkrcom = $check_data[‘lkr_com’];
$chkout = $check_data[‘chk_out’];
$chkin = $check_data[‘chk_in’];
$cflag = $row_cnt;
return $ctmid;
return $ctmname;
return $chwid;
return $chwtype;
return $crsid;
return $cjobid;
return $clkrid;
return $clkrcom;
return $chkout;
return $chkin;
return $cflag;
$mysqli->close();
}
function set_status($estatus,$ehwid)
{
$mysqli = new mysqli(‘localhost’, ‘root’, ‘***’, ‘lockers2’);
if ($mysqli->connect_errno) {
echo “Sorry, this website is experiencing problems.”;
echo “Error: Failed to make a MySQL connection, here is why: \n”;
echo “Errno: " . $mysqli->connect_errno . “\n”;
echo “Error: " . $mysqli->connect_error . “\n”;
exit();
}
$sql = “UPDATE equipment SET e_status = '”.$estatus.”’ WHERE hw_id = '”.$ehwid."’;";
if (!$result = $mysqli->query($sql)) {
echo “Sorry, the website is experiencing problems.”;
echo “Error: Our query failed to execute and here is why: \n”;
echo "Query: " . $sql . “\n”;
echo "Errno: " . $mysqli->errno . “\n”;
echo "Error: " . $mysqli->error . “\n”;
exit();
}
$mysqli->close();
}
function check_out($utmid,$utmname,$ehwid,$etype,$ersid,$ujobid,$elkrid,$elkrcom,$datenow)
{
$mysqli = new mysqli(‘localhost’, ‘root’, ‘****’, ‘lockers2’);
if ($mysqli->connect_errno) {
echo “Sorry, this website is experiencing problems.”;
echo “Error: Failed to make a MySQL connection, here is why: \n”;
echo "Errno: " . $mysqli->connect_errno . “\n”;
echo "Error: " . $mysqli->connect_error . “\n”;
exit();
}
$sql = “INSERT INTO checkinout (tm_id,tm_name,hw_id,hw_type,rs_id,job_id,lkr_id,lkr_com,chk_out)
VALUES (’” . $utmid . “’,’” . $utmname . “’,’” . $ehwid . “’,’” . $etype . “’,’” . $ersid . “’,’” . $ujobid . “’,’” . $elkrid . “’,’” . $elkrcom . “’,’” . $datenow . “’)”;
if (!$result = $mysqli->query($sql)) {
echo “Sorry, the website is experiencing problems.”;
echo “Error: Our query failed to execute and here is why: \n”;
echo "Query: " . $sql . “\n”;
echo "Errno: " . $mysqli->errno . “\n”;
echo "Error: " . $mysqli->error . “\n”;
exit();
}
$mysqli->close();
}
function check_in($tm_id, $hw_id)
{
$mysqli = new mysqli(‘localhost’, ‘root’, ‘****’, ‘lockers2’);
if ($mysqli->connect_errno) {
echo “Sorry, this website is experiencing problems.”;
echo “Error: Failed to make a MySQL connection, here is why: \n”;
echo "Errno: " . $mysqli->connect_errno . “\n”;
echo "Error: " . $mysqli->connect_error . “\n”;
exit();
}
$sql = “UPDATE checkinout SET chk_in = CURRENT_TIMESTAMP WHERE YEAR(chk_out) = YEAR(NOW()) AND MONTH(chk_out) = MONTH(NOW()) AND DAY(chk_out) = DAY(NOW()) AND tm_id=’” . $tm_id . “’ AND hw_id = '” . $hw_id . “’”;
if (!$result = $mysqli->query($sql)) {
echo “Sorry, the website is experiencing problems.”;
echo “Error: Our query failed to execute and here is why: \n”;
echo "Query: " . $sql . “\n”;
echo "Errno: " . $mysqli->errno . “\n”;
echo "Error: " . $mysqli->error . “\n”;
exit();
}
$mysqli->close();
}
?>[/php]
The Page
[php]
Test
![Logo]()
DC29 Lakeland
Locker Information Lookup
Team Member ID:
Check In?
Submit
.
<?php
date_default_timezone_set('America/New_York');
global $datenow;
$datenow = date("Y-m-d H:i:s");
require('usr_functions.php');
if (isset($_GET['tm_id'])) {
$tmid = $_GET['tm_id'];
} else {
exit("TM ID Must be entered");
}
?>
×
<?php
if (isset($_GET['checkin'])) {
get_user($tmid);
lookup_chk($utmid, $ujobid);
set_status(0, $chwid);
check_in($ctmid, $chwid);
echo "
" . $utmname . "
";
echo "TM ID:" . $utmid . "
";
echo "Job ID:" . $ujobid . "
";
echo "
Please return equipment to:
";
echo "
Locker: " . $clkrid . " - Combo: " . $clkrcom . "
";
} else {
get_user($tmid);
lookup_chk($utmid);
if ($cflag >= "1") {
echo "
EQUIPMENT STILL OUT!
";
echo "
" . $utmid . " - " . $utmname . "
";
echo "
";
echo "
Items Out: " . $cflag . "
";
echo "
Date Checked Out: " . $chkout . "
";
echo "
RF ID: " . $chwid . "
";
if (!$crsid == "0") {
echo "
RS ID: " . $crsid . "
";
}
echo "
Locker: " . $clkrid . " - Combo: " . $clkrcom . "
";
echo "
Please See A Systems Specialist!
";
} else {
get_equip($ujobid);
echo "
" . $utmid . " - " . $utmname . "
";
echo "
";
echo "
RF ID:" . $ehwid . "
";
if (!$ersid == "0") {
echo "
RS ID: " . $ersid . "
";
}
echo "
Locker:" . $elkrid . " - Combo:" . $elkrcom . "
";
echo "Inserting";
set_status(1, $ehwid);
check_out($utmid, $utmname, $ehwid, $etype, $ersid, $ujobid, $elkrid, $elkrcom, $datenow);
}
}
?>
[/php]
now before anyone yells at me for the poor coding – I KNOW – this is just a proof of concept project and is never going to be connected to a network so i am not worried about injection or error checking at this point, that will come later once the project passes onto the next phase.
