PHP PDO MySQL insert dynamic variables

[spertuit]

I've been trying to learn OOP using PDO and I've been doing pretty well, but this threw me for a loop. I have a form that creates more inputs using javascript.

I can pull the post variables and echo them, but can't figure out how to pass it to my function. Here is some code

if(isset($_REQUEST['insert'])){
	$departTime = $_POST['departTime'];
	$departPlace = $_POST['departPlace'];
	$arriveTime = $_POST['arriveTime'];
	$arrivePlace = $_POST['arrivePlace'];
	$numberOfPass = $_POST['numberOfPass'];
	$purpose = $_POST['purpose'];
	$cargo = $_POST['cargo'];
	$txtbox = $_POST['remarks'];
 //I can echo my variables I want just fine
foreach($departTime as $a => $b)
  echo "$departTime[$a]  -  $departPlace[$a]  -  $arriveTime[$a] <br />";
 //I need to get them in this statement and to my function
	extract($_REQUEST);
	if($obj->createlog($logDay,$logMonth,$logYear,$logCaptain,$logEmployer,$logLocation,$logWeather,$logSeaCondition,$logVisibility,$logWind,$logRunningTime,$logStandByTime,$logDownTime,$logMate,$logEngineer,$logDeckhand,$logDeckhand2,$logCook,$logAccidents,$logCaptainRemarks,$logLubeOnBoardPrevious,$logFuelOnBoardPrevious,$logFuelReceived,$logFuelTotal,$logFuelUsed,$logFuelTransferred,$logMeterClosing,$logMeterOpening,$logMeterPumped,$logFuelOnBoardEnd,$logLubeReceived,$logLubeTotal,$logLubeUsed,$logLubeOnBoardEnd,$logHoursOnLubePort,$logHoursOnLubeStbd,$totalHoursOnEnginePort,$totalHoursOnEngineStbd,$vesselName,$vesselNumber,$vesselType,$vesselOwner,"dailymasterlogs","vessels")){
		header("location:displayLogs.php?status_insert=success"); 
	}
}

I hope this isn't too much code. If I have too much going on let me know and I can cut it down to help illustrate my question. The main things is I need departTime, departPlace, and arriveTime sent to my function. Here is my function

	public function createlog($logDay,$logMonth,$logYear,$logCaptain,$logEmployer,$logLocation,$logWeather,$logSeaCondition,$logVisibility,$logWind,$logRunningTime,$logStandByTime,$logDownTime,$logMate,$logEngineer,$logDeckhand,$logDeckhand2,$logCook,$logAccidents,$logCaptainRemarks,$logFuelOnBoardPrevious,$logFuelReceived,$logFuelTotal,$logFuelUsed,$logFuelTransferred,$logMeterClosing,$logMeterOpening,$logMeterPumped,$logFuelOnBoardEnd,$logLubeOnBoardPrevious,$logLubeReceived,$logLubeTotal,$logLubeUsed,$logLubeOnBoardEnd,$logHoursOnLubePort,$logHoursOnLubeStbd,$totalHoursOnEnginePort,$totalHoursOnEngineStbd,$vesselName,$vesselNumber,$vesselType,$vesselOwner,$table,$table2){
		$sql = "INSERT INTO $table2 SET vesselName=:vesselName,vesselNumber=:vesselNumber,vesselType=:vesselType,vesselOwner=:vesselOwner";
		if(!$stmt = $this->conn->prepare($sql)){
    		// prepare failed
   			 echo "<pre>Prepare failed:\n";
    		print_r($pdo->errorInfo());
    		echo "</pre>";
		} else {
   		 	if(!$stmt->execute(array(':vesselName'=>$vesselName,':vesselNumber'=>$vesselNumber,':vesselType'=>$vesselType,':vesselOwner'=>$vesselOwner))){
        		// execute failed
        		echo "<pre>Execute failed:\n";
        		print_r($stmt->errorInfo());
        		echo "</pre>";
    		} else {
        	// query ran without any errors, you can test if it returned any rows and use them here
					$sql = "INSERT INTO $table SET logDay=:logDay,logMonth=:logMonth,logYear=:logYear,logCaptain=:logCaptain,logEmployer=:logEmployer,logLocation=:logLocation,logWeather=:logWeather,logSeaCondition=:logSeaCondition,logVisibility=:logVisibility,logWind=:logWind,logRunningTime=:logRunningTime,logStandByTime=:logStandByTime,logDownTime=:logDownTime,logMate=:logMate,logEngineer=:logEngineer,logDeckhand=:logDeckhand,logDeckhand2=:logDeckhand2,logCook=:logCook,logAccidents=:logAccidents,logCaptainRemarks=:logCaptainRemarks,logFuelOnBoardPrevious=:logFuelOnBoardPrevious,logLubeOnBoardPrevious=:logLubeOnBoardPrevious,logFuelReceived=:logFuelReceived,logFuelTotal=:logFuelTotal,logFuelUsed=:logFuelUsed,logFuelTransferred=:logFuelTransferred,logMeterClosing=:logMeterClosing,logMeterOpening=:logMeterOpening,logMeterPumped=:logMeterPumped,logFuelOnBoardEnd=:logFuelOnBoardEnd,logLubeReceived=:logLubeReceived,logLubeTotal=:logLubeTotal,logLubeUsed=:logLubeUsed,logLubeOnBoardEnd=:logLubeOnBoardEnd,logHoursOnLubePort=:logHoursOnLubePort,logHoursOnLubeStbd=:logHoursOnLubeStbd,totalHoursOnEnginePort=:totalHoursOnEnginePort,totalHoursOnEngineStbd=:totalHoursOnEngineStbd";
					if(!$stmt = $this->conn->prepare($sql)){
    				// prepare failed
   			 		echo "<pre>Prepare failed:\n";
    				print_r($pdo->errorInfo());
    				echo "</pre>";
					} else {
   		 				if(!$stmt->execute (array(':logDay'=>$logDay,':logMonth'=>$logMonth,':logYear'=>$logYear,':logCaptain'=>$logCaptain,':logEmployer'=>$logEmployer,':logLocation'=>$logLocation,':logWeather'=>$logWeather,':logSeaCondition'=>$logSeaCondition,':logVisibility'=>$logVisibility,':logWind'=>$logWind,':logRunningTime'=>$logRunningTime,':logStandByTime'=>$logStandByTime,':logDownTime'=>$logDownTime,':logMate'=>$logMate,':logEngineer'=>$logEngineer,':logDeckhand'=>$logDeckhand,':logDeckhand2'=>$logDeckhand2,':logCook'=>$logCook,':logAccidents'=>$logAccidents,':logCaptainRemarks'=>$logCaptainRemarks,':logFuelOnBoardEnd'=>$logFuelOnBoardEnd,':logFuelReceived'=>$logFuelReceived,':logFuelTotal'=>$logFuelTotal,':logFuelUsed'=>$logFuelUsed,':logFuelTransferred'=>$logFuelTransferred,':logMeterClosing'=>$logMeterClosing,':logMeterOpening'=>$logMeterOpening,':logMeterPumped'=>$logMeterPumped,':logFuelOnBoardPrevious'=>$logFuelOnBoardPrevious,':logLubeOnBoardPrevious'=>$logLubeOnBoardPrevious,':logLubeReceived'=>$logLubeReceived,':logLubeTotal'=>$logLubeTotal,':logLubeUsed'=>$logLubeUsed,':logLubeOnBoardEnd'=>$logLubeOnBoardEnd,':logHoursOnLubePort'=>$logHoursOnLubePort,':logHoursOnLubeStbd'=>$logHoursOnLubeStbd,':totalHoursOnEnginePort'=>$totalHoursOnEnginePort,':totalHoursOnEngineStbd'=>$totalHoursOnEngineStbd))){
        				// execute failed
        				echo "<pre>Execute failed:\n";
        				print_r($stmt->errorInfo());
        				echo "</pre>";
    				} else {
        			// query ran without any errors, you can test if it returned any rows and use them here
					return true;
    				}
				}
    		}
		}		
	}	

I hope I am clear enough. Any help is appreciated.

[mac_gyver]

if you want to do this to learn something about OOP, i recommend creating a general purpose CRUD ( http://en.wikipedia.org/wiki/CRUD ) database class using pdo prepared queries. you would then call the methods of this class from your application to run the queries you have shown in your code. you would form the queries and an array of the data types/values in your application code and pass them into the CRUD class methods. the Create (insert) method would be for running an INSERT query.

[spertuit]

I have an entire CRUD system setup with prepared statements and error checking. I understand all of this very well. I am just trying to figure out passing these dynamic fields into my function. I have the basic functions written and everything works fine.

[Jessica]

If you can echo them, you can pass them into a function.

 

Rather than using extract, you should probably just make your function accept an array.

[mac_gyver]

if the $departTime[$a] - $departPlace[$a] - $arriveTime[$a] are multiple segments belonging to one trip, they don't belong in any of the queries you have posted above because they belong in a separate table, such as a trip_details table, one row per segment and they use the trip log id to associate them with the trip they are part of.

 

your trip log table should contain global/one-time information for each trip. any information for a trip where there are multiple sets for any one trip belong in a separate database table.

[spertuit]

Yes mac_gyver I do have a separate table for the trip_details. I am attempting to add another insert statement to my function.

 

Thanks again Jessica, that's what I was thinking. still trying to get syntax right and good coding. I have it working by calling a nested function to loop through and perform the insert for my trip details. Only problem I have now

is after I perform my insert of my first log I need to grab the id from that insert and pass it to my insert for my trip details. Anyway here is the function to insert the data into the trips table.

	public function createtrip($id,$table){
		try{
                //write query
				
				$departTime = $_POST['departTime'];
				$departPlace = $_POST['departPlace'];
				$arriveTime = $_POST['arriveTime'];
				$arrivePlace = $_POST['arrivePlace'];
				$numberOfPass = $_POST['numberOfPass'];
				$purpose = $_POST['purpose'];
				$cargo = $_POST['cargo'];
				$remarks = $_POST['remarks'];
	
				foreach($departTime as $a => $b)
				{
                $query = "INSERT INTO trips SET departTime = ?, departPlace = ?, arriveTime = ?, arrivePlace  = ?,numberOfPass = ?,purpose = ?,cargo = ?,remarks = ?,masterLogID = ?";
                $stmt = $this->conn->prepare($query);
                $stmt->bindParam(1,$departTime[$a]);
                $stmt->bindParam(2,$departPlace[$a]);
                $stmt->bindParam(3,$arriveTime[$a]);
				$stmt->bindParam(4,$arrivePlace[$a]);
                $stmt->bindParam(5,$numberOfPass[$a]);
                $stmt->bindParam(6,$purpose[$a]);
                $stmt->bindParam(7,$cargo[$a]);
                $stmt->bindParam(8,$remarks[$a]);
                $stmt->bindParam(9,$id);

                // Execute the query
                $stmt->execute();
				}
                echo "Record was saved.";
        
        }catch(PDOException $exception){ //to handle error
                echo "Error: " . $exception->getMessage();
		}
	}

[spertuit]

If anyone needs it ....

 

if you have a statement something like $this->conn->prepare($sql)

you can grab the last generated id of that row by doing $id = $this->conn->lastInsertId();