Here is something I threw together in my own local WAMP install… and works pretty much exactly as you want:
CalibrationForm.php
<?
//database stuff
ini_set("display_errors", "1");
error_reporting(E_ALL);
//db connection
include('../test_db_pdo.php');
//set utf8 mode
$conn->exec("set names utf8");
//set error mode
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
//PDO should parameterize and sanitize good enough, but in case any screen output
function cleanInput($dirtyData){
$cleanedData = htmlspecialchars(strip_tags($dirtyData));
return $cleanedData;
}
$equipment_number = isset($_POST["equipment_number"]) ? cleanInput($_POST["equipment_number"]) : '' ;
$employee_number = isset($_POST["employee_number"]) ? cleanInput($_POST["employee_number"]) : '' ;
//check from 'mode' and set state
$mode = isset($_GET['mode']) ? $_GET['mode'] : '';
?>
<!-- jQuery 3.3.1 -->
<script src="https://code.jquery.com/jquery-3.3.1.slim.min.js"></script>
<!-- Scripts -->
<script type="text/javaScript">
//jQuery (event actions)
document.addEventListener("DOMContentLoaded", function(event) {
//enter key
$(document).bind('keypress', function(e) {
//enter key (code)
if(e.keyCode==13){
$('#submitBtn').trigger('click');
}
});
//submit button listener
$("#submitBtn").click(function() {
//console.log("submit button clicked");
//validation message holder
var errormessage = '';
//any potential field validation goes here
//equipment
if ($("#equipment_number").val() === "") {
errormessage += "\n Please select equipment first";
}
//employee
if ($("#employee_number").val() === "") {
errormessage += "\n Please select employee first";
}
//check if any errors were found
if(errormessage != ''){
event.preventDefault(); //do not submit form
//output missing data
alert("The following items need attention: \n" + errormessage);
}else{
//submit form
$('#appForm').submit();
}
});
});
</script>
<!-- example form -->
<?
//get equipment id's
$equipmentDetails_sql = "SELECT * FROM equipment";
$equipmentDetails_stmt = $conn->prepare($equipmentDetails_sql);
//$equipmentDetails_stmt->bindValue(':equipment_number', $equipment_number);
$equipmentDetails_stmt->execute();
$equipmentDetails_stmt->setFetchMode(PDO::FETCH_ASSOC);
//$_equipmentDetails = $equipmentDetails_stmt->fetch(); //single row
$_equipmentDetails = $equipmentDetails_stmt->fetchAll(); //returns multi-dimensional array (changes way you access object below)
$colcount = $equipmentDetails_stmt->columnCount();
$rowcount = $equipmentDetails_stmt->rowCount();
//var_dump($_equipmentDetails);
if($rowcount == 0){
echo 'NO EQUIPMENT RETURNED';
}
//get employee id's
$employeeDetails_sql = "SELECT * FROM employee";
$employeeDetails_stmt = $conn->prepare($employeeDetails_sql);
//$employeeDetails_stmt->bindValue(':employee_number, $employee_number);
$employeeDetails_stmt->execute();
$employeeDetails_stmt->setFetchMode(PDO::FETCH_ASSOC);
//$_employeeDetails = $employeeDetails_stmt->fetch(); //single row
$_employeeDetails = $employeeDetails_stmt->fetchAll(); //returns multi-dimensional array (changes way you access object below)
$colcount = $employeeDetails_stmt->columnCount();
$rowcount = $employeeDetails_stmt->rowCount();
//var_dump($_employeeDetails);
if($rowcount == 0){
echo 'NO EMPLOYEES RETURNED';
}
?>
<div id='appContainer'>
<form id="appForm" name="appForm" method="post" action="<?=$_SERVER['PHP_SELF']?>?mode=submit">
<select id="equipment_number" name="equipment_number">
<option value="">Select Equipment</option>
<?
for($i=0; $i<count($_equipmentDetails); $i++){
echo '<option value="' . $_equipmentDetails[$i]['equipment_number'] . '">' . $_equipmentDetails[$i]['equipment_brand'] . '</option>';
}
?>
</select>
<select id="employee_number" name="employee_number">
<option value="">Select Employee</option>
<?
for($i=0; $i<count($_employeeDetails); $i++){
echo '<option value="' . $_employeeDetails[$i]['employee_number'] . '">' . $_employeeDetails[$i]['employee_name'] . '</option>';
}
?>
</select>
<div id="buttonContainer" name="buttonContainer">
<br><button id="submitBtn" type="button">SCHEDULE EQUIPMENT CALIBRATION</button><br>
</div>
</form>
</div>
<?
if($mode == 'submit') {
//$calibrationDetails_sql = "SELECT equipment.*, employee.* FROM equipment, employee WHERE equipment_number = 'Toro' AND employee_number = '1'";
$calibrationDetails_sql = "SELECT equipment.*, employee.* FROM equipment, employee WHERE equipment_number = :equipment_number AND employee_number = :employee_number";
$calibrationDetails_stmt = $conn->prepare($calibrationDetails_sql);
$calibrationDetails_stmt->bindValue(':equipment_number', $equipment_number);
$calibrationDetails_stmt->bindValue(':employee_number', $employee_number);
$calibrationDetails_stmt->execute();
$calibrationDetails_stmt->setFetchMode(PDO::FETCH_ASSOC);
$_calibrationDetails = $calibrationDetails_stmt->fetch(); //single row
//$_calibrationDetails = $calibrationDetails_stmt->fetchAll(); //returns multi-dimensional array (changes way you access object below)
$colcount = $calibrationDetails_stmt->columnCount();
$rowcount = $calibrationDetails_stmt->rowCount();
//var_dump($_calibrationDetails);
if($rowcount <= 0){
//no member record found
return 'There was no equipment/employee match to schedule calibration for.<br>Please try again..<br>';
echo '<br><br>';
}else{
echo "<p>Equipment/Employee Match Found</p>";
//declare vars
$_equipment_id = $_calibrationDetails['equipment_number'];
$_equipment_brand = $_calibrationDetails['equipment_brand'];
$_equipment_desc = $_calibrationDetails['equipment_description'];
$_equipment_calibration_int = $_calibrationDetails['equipment_calibration_interval'];
$_equipment_calibration_date = date('Y-m-d H:i:s');
$_equipment_next_calibration_date = date('Y-m-d H:i:s', strtotime(' + ' . $_equipment_calibration_int . ' days'));
$_employee_id = $_calibrationDetails['employee_number'];
$_employee_name = $_calibrationDetails['employee_name'];
$_employee_start_date = $_calibrationDetails['employee_start_date'];
//if equipment/employee match found, save scheduled time.
$scheduleDetails_sql = "INSERT INTO calibration (equipment_number, equipment_brand, equipment_description, equipment_calibration_interval, employee_number, employee_name, calibration_date, next_calibration_date) VALUES(:equipment_number, :equipment_brand, :equipment_description, :equipment_calibration_interval, :employee_number, :employee_name, :calibration_date, :next_calibration_date)";
$scheduleDetails_qryparams = array(
':equipment_number' => $_equipment_id,
':equipment_brand' => $_equipment_brand,
':equipment_description' => $_equipment_desc,
':equipment_calibration_interval' => $_equipment_calibration_int,
':employee_number' => $_employee_id,
':employee_name' => $_employee_name,
':calibration_date' => $_equipment_calibration_date,
':next_calibration_date' => $_equipment_next_calibration_date
);
$scheduleDetails_qry = $conn->prepare($scheduleDetails_sql);
$scheduleResults = $scheduleDetails_qry->execute($scheduleDetails_qryparams);
if(!$scheduleResults ){
//db post/insert failed.
echo "<p>Calibration scheduling failed</p>";
echo '<p>' . var_dump($scheduleDetails_qry->errorInfo()) . '</p>';
}else{
//save was successful
echo '<p><strong>Scheduled Calibration was successful</strong></p>';
echo '<br><p><strong>Scheduled Details:</strong></p>';
//display schedule details
echo 'EMPLOYEE ID: ' . $_employee_id . '<br>';
echo 'EMPLOYEE NAME: ' . $_employee_name . '<br>';
echo 'EMPLOYEE START DATE: ' . $_employee_start_date . '<br><br>';
echo 'EQUIPMENT ID: ' . $_equipment_id . '<br>';
echo 'EQUIPMENT BRAND: ' . $_equipment_brand . '<br>';
echo 'EQUIPMENT DESC: ' . $_equipment_desc . '<br>';
echo 'EQUIPMENT CALIBRATION INTERVAL: (days): ' . $_equipment_calibration_int. '<br>';
echo 'EQUIPMENT CALIBRATION DATE: ' . $_equipment_calibration_date . '<br>';
echo 'EQUIPMENT NEXT CALIBRATION DATE: ' . $_equipment_next_calibration_date . '<br>';
}
}
}
?>Displays output like so:
Equipment/Employee Match Found
Scheduled Calibration was successful
Scheduled Details:
EMPLOYEE ID: 7
EMPLOYEE NAME: Jane Doe
EMPLOYEE START DATE: 2018-01-21EQUIPMENT ID: 56
EQUIPMENT BRAND: Ariens
EQUIPMENT DESC: blah blah blah
EQUIPMENT CALIBRATION INTERVAL: (days): 30
EQUIPMENT CALIBRATION DATE: 2019-11-16 16:57:02
EQUIPMENT NEXT CALIBRATION DATE: 2019-12-16 16:57:02
