Getting Selected Row from Drop Down List to perform SQL

[chrismoje]

Hi Gurus,
I would like to use a drop down list as my Query Input, may I know how to do it?

<?php //Display Machine Number via Dropdown List require "../config.php"; require "../common.php"; $pdo = new PDO ($dsn, $username, $password, $options); $sql = "SELECT mach_no FROM equipment"; $stmt = $pdo->prepare($sql); $stmt->execute(); $results = $stmt->fetchAll(PDO::FETCH_ASSOC); if ($stmt->rowCount() >0) { ?>

<select name="MachineNumber">
	<?php foreach ($results as $row) { ?>
	<option value="<?php echo $row['mach_no']; ?>"><?php echo $row['mach_no']; ?></option>
<?php } ?>

</select>

<?php } ?>

From the result of the dropdown list, I would like to search:

$sql = “SELECT * FROM equipment WHERE equip_id IN (SELECT equip_id from calrecord where mach_no LIKE :mach_no)”;

and display them in textboxes.

Thanks in advance

[whispers]

How would like you this done?

1.) A user make a selection from a drop down… and then hits a ‘submit’ button?

I gave a long/working example here if you want to use it:

Auto fill form

Here is a quick -n- dirty way to do it with a normal HTML/PHP form type of approach: <? //PDO should parameterize and sanitize good enough, but in case any screen output function cleanInput($dirtyData){ $cleanedData = htmlspecialchars(strip_tags($dirtyData)); return $cleanedData; } $employee_id = isset($_POST["employee_id"]) ? cleanInput($_POST["employee_id"]) : '' ; //check from 'mode' and set state $mode = isset($_GET['mode']) ? $_GET['mode'] : ''; ?> <!-- Scripts --> <script type="…

This approach has the dropdown already on the page ((not created from query results… but you can add yours in )… and then shows how after submitting the page (to itself)… you can then grab the submitted data, run a PDO/query with it… and then display the results as you want.

2.) If you are looking for a way that DOES not have a submit button (ie: once the dropdown selection is changed, the action happens)… then you will need to use some AJAX and an external .php script.

I can whip up an example of that too if that is route you prefer to go?

Let us know.

[chrismoje]

Hi Whisper,

Yes I would like to:
1.) select a dropdown list of machine numbers from a table which contains my “equipment”
2.) then I would like to select employee number from a dropdown list from a table which contains “employee”
3.) Then a button to save the values from the selected(table: equipment) Equipment Number, Brand, Description, Calibration_interval in combination with the Employee Number (table: employee).
4.) When the button “Save” is pressed, the data from step 3 will be saved in a table: Calibration_Record.
5.) Finally display table Calibration_Record: Cal_ID, Equipment_number, Brand, Description, Calibration_Interval, Employee_Number, Calibration_Date and a calculated column which will show the Next Calibration Date (result of Calibration_Date + Calibration_Interval)

Hope you can help me. I am very new in PHP. Thanks

[whispers]

Well… I’m not going to give you an EXACT solution to YOUR exact problem… (I dont have the data or the tables set up to be that precise)…

The link I gave you should get you 90% there… (just missing the ‘save’ part you just mentioned)…

But that shouldnt be a big deal., just add the SAVE portion above the SELECT portion… and upon a successful ‘SAVE’… run the SELECT query.

Although I guess I’m a little confused?
So lets re-cap:

You dropdown #1 populated by an identifying value from data in table: equipment
You dropdown #2 populated by an identifying value from data in table: employee

After a user make a selection from both dropdowns… andhits the submit/save button…

you need to do the following:

• take the equipment value from dropdown #1
• take the employee value form dropdown #2
• do a JOIN query to get all the -other- data/values associated with selected equipment ID & selected employee ID from those two tables… and -SAVE- this to a 3rd table called: calibration_record

Is this correct so far? or is there something else missing?

[chrismoje]

Hi whispers, this is correct. The 3rd table (calibration_record) will include info from Equipment and Employee. This will be composed of the following:
**Calibration_Record.**Cal_ID, Equipment.Mach_No,Equipment.Brand, Equipment.Description, Employee.Employee_Number, Equipment.Calibration_Interval, Calibration_Record.Date, Calibration_Record.Next_Calibration

Where Calibration_Record.Next_Calibration = Calibration_Record.Date + Equipment.Calibration_Interval

Thanks in advance

[whispers]

WHat does this mean?
Calibration_Record: Cal_ID

Where is that value supposed to come from?

Just an auto-incremented value or what?

[chrismoje]

Yes its only an auto-incremented field, so as to have a unique calibration number for certificate purposes.

[whispers]

Did you want to save to the Calibration Record IMMEDIATELY after submitting the equipment choice and employee choice??

(or after the content is displayed/output on the screen?) (ie: with an additional ‘save’ button?)

[whispers]

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-21

EQUIPMENT 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

[chrismoje]

Hi Whispers, this is a great help…will try the code now…Thank you so much for this.

[whispers]

Here is the table structure to match the example code above:

Equipment Table:

-- Table structure for table `equipment`
--

CREATE TABLE IF NOT EXISTS `equipment` (
  `equipment_number` int(25) NOT NULL,
  `equipment_brand` varchar(255) NOT NULL,
  `equipment_description` text NOT NULL,
  `equipment_calibration_interval` int(6) NOT NULL,
  PRIMARY KEY (`equipment_number`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `equipment`
--

INSERT INTO `equipment` (`equipment_number`, `equipment_brand`, `equipment_description`, `equipment_calibration_interval`) VALUES
(1, 'Toro', 'blah blah blah', 30),
(4, 'Craftsman', 'blah blah blah', 90),
(56, 'Ariens', 'blah blah blah', 30),
(12, 'Honda', 'blah blah blah', 180);

Employee Table:

-- Table structure for table `employee`
--

CREATE TABLE IF NOT EXISTS `employee` (
  `employee_number` int(25) NOT NULL,
  `employee_name` varchar(255) NOT NULL,
  `employee_start_date` date NOT NULL,
  PRIMARY KEY (`employee_number`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1;

--
-- Dumping data for table `employee`
--

INSERT INTO `employee` (`employee_number`, `employee_name`, `employee_start_date`) VALUES
(1, 'Jon Doe', '2016-03-20'),
(7, 'Jane Doe', '2018-01-21'),
(23, 'Jim Doe', '2014-11-19'),
(9, 'Mary Doe', '2017-04-07');

Calibration Schedule Table:

-- Table structure for table `calibration`
--

CREATE TABLE IF NOT EXISTS `calibration` (
  `calibration_id` int(25) NOT NULL AUTO_INCREMENT,
  `equipment_number` int(25) NOT NULL,
  `equipment_brand` varchar(255) NOT NULL,
  `equipment_description` text NOT NULL,
  `equipment_calibration_interval` int(25) NOT NULL,
  `employee_number` int(25) NOT NULL,
  `employee_name` varchar(255) NOT NULL,
  `calibration_date` timestamp NOT NULL DEFAULT CURRENT_TIMESTAMP,
  `next_calibration_date` timestamp NOT NULL DEFAULT '0000-00-00 00:00:00',
  PRIMARY KEY (`calibration_id`)
) ENGINE=MyISAM  DEFAULT CHARSET=latin1 ;

[chrismoje]

Hi Whispers, this is awesome help, I would need one last assistance though, I would like to upload a calibration file (pdf) into each row and the calibration_id will have its hyperlink to each calibration file. Is there a script available to browse and upload the file before I save? Have a great day ahead.

[whispers]

@chrismoje

That is no small task if you want ti done correctly.

Things need to be VERY secure… and you also need to ensure your platform/server also have some security stuff in place (like not allowing execution/read only to the directory you have the files ultimate saved to…etc…etc)

Is this web app you (I) am making… only run/used in-house?

Or is this a real web app available outside of your intranet?

[chrismoje]

Hi Whispers, Sorry if i gave some trouble on your part, this will only be used in my local network here and only in my own group. There is no intention of using the database online or outside my small network. Only 2 or 3 computers may access this database. Appreciate all the help.

[whispers]

Is the file upload required?

[whispers]

Something like this should work… (should be some error checking and file existing checking… but it works)

<?


//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"]) : '' ;

//setup file attachment if exists
function savefile($thisfile) {	
	if ($_FILES[$thisfile]['tmp_name'] != '') {
		$fileatt = $_FILES[$thisfile]['tmp_name'];		
		if (strstr($_FILES[$thisfile]['name'],'.') != false) {
			//$_filename = explode('.',$_FILES[$thisfile]['name']);
			//$filename = $_filename[0];
			//$fileext = '.' . $_filename[1];			
			$_filename = explode('.',$_FILES[$thisfile]['name']);
			//handles if multiple '.' in file name
			$filename = substr($_FILES[$thisfile]['name'],0,strrpos($_FILES[$thisfile]['name'],'.')); 
			$fileext = '.' . $_filename[(count($_filename) - 1)];
		}	
		//aray of allowed file types
		$allowedfiletype = array('.pdf', '.jpg');
		//check if file type is allowed and f so, continue with upload process
		if(in_array($fileext, $allowedfiletype)){
			//get file data
			$file = fopen($fileatt,'rb') or exit();
			$filedata = fread($file,filesize($fileatt));
			fclose($file);		
			//make unique filename
			$fileunique = preg_replace('/[^A-Za-z0-9]/','_',$filename) . '_' . date('Ymdis') . $fileext;
			$pathfileunique = "saved_uploads/" . $fileunique; //local folder (write permissions may be needed)				
			//copy from temp location to permanent location
			move_uploaded_file($_FILES[$thisfile]['tmp_name'], $pathfileunique);
			//chmod($pathfileunique, 0644);
			//return URL
			//return $pathfileunique;					
			return 'http://localhost/projects/misc/php_help/'.$pathfileunique;					
		}
	}
}


//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();	

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();	

if($rowcount == 0){
	echo 'NO EMPLOYEES RETURNED';
}

?>
<div id='appContainer'>                                
	<form id="appForm" name="appForm" method="post" action="<?=$_SERVER['PHP_SELF']?>?mode=submit" enctype="multipart/form-data">

		<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>
		
		<br><br>
		
		Select Calibration File:<br>
		<input type="file" name="calibration_file" id="calibration_file">
		<br><br>
		
		<div id="buttonContainer" name="buttonContainer">
			<br><button id="submitBtn" type="button">SCHEDULE EQUIPMENT CALIBRATION</button><br>							
		</div>
	</form>
</div>
	
<?

if($mode == 'submit') {	
	
	//get calibration details based on equipment and employee selections
	$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();	
	
	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'];		
		
		//chek if file is present
		$_file_path = 'No File Uploaded';
		$_file_path = savefile("calibration_file");
				
		//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, calibration_file_path) VALUES(:equipment_number, :equipment_brand, :equipment_description, :equipment_calibration_interval, :employee_number, :employee_name, :calibration_date, :next_calibration_date, :calibration_file_path)";
		
		$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,		
			':calibration_file_path' =>	$_file_path		
		);		
		$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 (make a pretty table if you like here)
			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>';
			echo 'CALIBRATION FILE PATH: <a href=' . $_file_path . '>Calibration Link</a><br>';			
		}
	}	
		
}

?>

need to add a new column to the calibration table as well:
calibration_file_path

and any references to ‘localhost’ (ie: http://localhost/projects/misc/php_help/) and ‘saved_uploads’ will need to be replaced with your own server path and directory name you want to upload/save to…

[chrismoje]

Hi Whispers,

Is the test_db_pdo.php looks like this:

<?php /** * Configuration for database connection * */ $host = "localhost"; $username = "root"; $password = ""; $dbname = "test_db_pdo"; $dsn = "mysql:host=$host;dbname=$dbname"; $options = array( PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION ); Thanks truly

[chrismoje]

HI Whisper, I am afraid I can´t connect on the pdo, all text are being shown

on the screen?

[whispers]

Where are you running this? What server?

Doesnt look PHP is installed/working?

I am using a local install of WAMP on my PC to test things with…

not sure what YOU are using.

test_db_pod.php should like like:

<?
        $dbhost = 'localhost';
	$dbname = 'test';
	$dbuser = 'root';
	$dbpass = '';
	$conn = new PDO('mysql:host=' . $dbhost . ';dbname=' . $dbname, $dbuser, $dbpass);
	$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
?>

with your own credentials of course…

[chrismoje]

HI Whispers,
Yes, php is installed in my windows pc and I was running on XAMPP, let me download WAMP, have it installed and give you feed back asap. Thanks