How do I solve this: Invalid parameter number: parameter was not defined

PHP
1 

elseif(isset($_POST['button']) && $_POST['button'] == 'Update JC')
{
	require_once('utility_funcs.inc.php');
	
	$openDate = @convertDateToMySQL7($_POST['openmonth'], $_POST['openday'], $_POST['openyear']);
	$promDelDate = @convertDateToMySQL8($_POST['pdmonth'], $_POST['pdday'], $_POST['pdyear']);
	$revDelDate = @convertDateToMySQL9($_POST['rdmonth'], $_POST['rdday'], $_POST['rdyear']);
	$estimateDate = @convertDateToMySQL10($_POST['esmonth'], $_POST['esday'], $_POST['esyear']);

$conn = DatabaseManager::getConnection();

try

{
		
	$sql = "UPDATE jobcard SET repairId = :repairId, regNumber = :regNumber, openTime = :openTime, repairOrderType = :repairOrderType, section = :section, preparedBy = :preparedBy, promDelTime = :promDelTime, revisedDelTime = :revisedDelTime, kmIn = :kmIn, kmOut = :kmOut, estimateRef = :estimateRef, contractorCustomer = :contractorCustomer, splPackage = :splPackage, others = :others, lubService1 = :lubService1, lubService2 =:lubService2, lubService3 = :lubService3, lubService4 = :lubService4, lubService5 = :lubService5, lubService6 = :lubService6, lubService7 = :lubService7, lubService8 = :lubService8, lubService9 = :lubService9, lubService10 = :lubService10, lubService11 = :lubService11, lubService12 = :lubService12, lubService13 = :lubService13, lubService14 = :lubService14, lubService15 = :lubService15, lubService16 = :lubService16, lubService17 = :lubService17, lubService18 = :lubService18, lubService19 = :lubService19, lubService20 = :lubService20, lubService21 = :lubService21, lubService22 = :lubService22, noj1 = :noj1, noj2 = :noj2, noj3 = :noj3, noj4 = :noj4, noj5 = :noj5, noj6 = :noj6, noj7 = :noj7, noj8 = :noj8, noj9 = :noj9, noj10 = :noj10, opCode1 = :opCode1, opCode2 = :opCode2, opCode3 = :opCode3, opCode4 = :opCode4, opCode5 = :opCode5, opCode6 = :opCode6, opCode7 = :opCode7, opCode8 = :opCode8, opCode9 = :opCode9, opCode10 = :opCode10, rco1 = :rco1, rco2 = :rco2, rco3 = :rco3, rco4 = :rco4, rco5 = :rco5, rco6 = :rco6, rco7 = :rco7, rco8 = :rco8, rco9 = :rco9, rco10 = :rco10, lc1 = :lc1, lc2 = :lc2, lc3 =:lc3, lc4 = :lc4, lc5 = :lc5, lc6 = :lc6, lc7 = :lc7, lc8 = :lc8, lc9 = :lc9, lc10 = :lc10, labour = :labour, parts = :parts, tba = :tba, gasAndOil = :gasAndOil, sublet = :sublet, others2 = :others2, total = :total, warrantyLabour = :warrantyLabour, warrantyParts = :warrantyParts, services = :services, discountLabour = :discountLabour, discountParts = :discountParts, vat = :vat, netAmountPayable = :netAmountPayable, openDate = :openDate, promDelDate = :promDelDate, revisedDelDate = :revisedDelDate, estimateDate = :estimateDate WHERE id = :id";
	
		$st = $conn->prepare ( $sql );
		$st->bindValue( ":repairId", $_POST['repairId'], PDO::PARAM_INT );
		$st->bindValue( ":regNumber", $_POST['regNumber'], PDO::PARAM_STR );
		$st->bindValue( ":openTime", $_POST['openTime'], PDO::PARAM_STR );
		$st->bindValue( ":repairOrderType", $_POST['repairOrderType'], PDO::PARAM_STR );
		$st->bindValue( ":section", $_POST['section'], PDO::PARAM_STR );
		#$st->bindValue( ":pryRepairOrderNo", $this->pryRepairOrderNo, PDO::PARAM_STR );
		$st->bindValue( ":preparedBy", $_POST['preparedBy'], PDO::PARAM_STR );
		#$st->bindValue( ":promDelDate", $this->promDelDate, PDO::PARAM_INT );
		$st->bindValue( ":promDelTime", $_POST['promDelTime'], PDO::PARAM_STR );
		#$st->bindValue( ":revisedDelDate", $_POST['revisedDelDate'], PDO::PARAM_INT );
		$st->bindValue( ":revisedDelTime", $_POST['revisedDelTime'], PDO::PARAM_STR );
		$st->bindValue( ":kmIn", $_POST['kmIn'], PDO::PARAM_INT );
		$st->bindValue( ":kmOut", $_POST['kmOut'], PDO::PARAM_INT );
		$st->bindValue( ":estimateRef", $_POST['estimateRef'], PDO::PARAM_STR );
		$st->bindValue( ":contractorCustomer", $_POST['contractorCustomer'], PDO::PARAM_STR );
		$st->bindValue( ":splPackage", $_POST['splPackage'], PDO::PARAM_STR );
		#$st->bindValue( ":estimateDate", $this->estimateDate, PDO::PARAM_INT );
		$st->bindValue( ":others", $_POST['others'], PDO::PARAM_INT );
		$st->bindValue( ":lubService1", $_POST['lubService1'], PDO::PARAM_STR );
		$st->bindValue( ":lubService2", $_POST['lubService2'], PDO::PARAM_STR );
		$st->bindValue( ":lubService3", $_POST['lubService3'], PDO::PARAM_STR );
		$st->bindValue( ":lubService4", $_POST['lubService4'], PDO::PARAM_STR );
		$st->bindValue( ":lubService5", $_POST['lubService5'], PDO::PARAM_STR );
		$st->bindValue( ":lubService6", $_POST['lubService6'], PDO::PARAM_STR );
		$st->bindValue( ":lubService7", $_POST['lubService7'], PDO::PARAM_STR );
		$st->bindValue( ":lubService8", $_POST['lubService8'], PDO::PARAM_STR );
		$st->bindValue( ":lubService9", $_POST['lubService9'], PDO::PARAM_STR );
		$st->bindValue( ":lubService10", $_POST['lubService10'], PDO::PARAM_STR );
		$st->bindValue( ":lubService11", $_POST['lubService11'], PDO::PARAM_STR );
		$st->bindValue( ":lubService12", $_POST['lubService12'], PDO::PARAM_STR );
		$st->bindValue( ":lubService13", $_POST['lubService13'], PDO::PARAM_STR );
		$st->bindValue( ":lubService14", $_POST['lubService14'], PDO::PARAM_STR );
		$st->bindValue( ":lubService15", $_POST['lubService15'], PDO::PARAM_STR );
		$st->bindValue( ":lubService16", $_POST['lubService16'], PDO::PARAM_STR );
		$st->bindValue( ":lubService17", $_POST['lubService17'], PDO::PARAM_STR );
		$st->bindValue( ":lubService18", $_POST['lubService18'], PDO::PARAM_STR );
		$st->bindValue( ":lubService19", $_POST['lubService19'], PDO::PARAM_STR );
		$st->bindValue( ":lubService20", $_POST['lubService20'], PDO::PARAM_STR );
		$st->bindValue( ":lubService21", $_POST['lubService21'], PDO::PARAM_STR );
		$st->bindValue( ":lubService22", $_POST['lubService22'], PDO::PARAM_STR );
		$st->bindValue( ":noj1", $_POST['noj1'], PDO::PARAM_STR );
		$st->bindValue( ":noj2", $_POST['noj2'], PDO::PARAM_STR );
		$st->bindValue( ":noj3", $_POST['noj3'], PDO::PARAM_STR );
		$st->bindValue( ":noj4", $_POST['noj4'], PDO::PARAM_STR );
		$st->bindValue( ":noj5", $_POST['noj5'], PDO::PARAM_STR );
		$st->bindValue( ":noj6", $_POST['noj6'], PDO::PARAM_STR );
		$st->bindValue( ":noj7", $_POST['noj7'], PDO::PARAM_STR );
		$st->bindValue( ":noj8", $_POST['noj8'], PDO::PARAM_STR );
		$st->bindValue( ":noj9", $_POST['noj9'], PDO::PARAM_STR );
		$st->bindValue( ":noj10", $_POST['noj10'], PDO::PARAM_STR );
		$st->bindValue( ":opCode1", $_POST['opCode1'], PDO::PARAM_STR );
		$st->bindValue( ":opCode2", $_POST['opCode2'], PDO::PARAM_STR );
		$st->bindValue( ":opCode3", $_POST['opCode3'], PDO::PARAM_STR );
		$st->bindValue( ":opCode4", $_POST['opCode4'], PDO::PARAM_STR );
		$st->bindValue( ":opCode5", $_POST['opCode5'], PDO::PARAM_STR );
		$st->bindValue( ":opCode6", $_POST['opCode6'], PDO::PARAM_STR );
		$st->bindValue( ":opCode7", $_POST['opCode7'], PDO::PARAM_STR );
		$st->bindValue( ":opCode8", $_POST['opCode8'], PDO::PARAM_STR );
		$st->bindValue( ":opCode9", $_POST['opCode9'], PDO::PARAM_STR );
		$st->bindValue( ":opCode10", $_POST['opCode10'], PDO::PARAM_STR );
		$st->bindValue( ":rco1", $_POST['rco1'], PDO::PARAM_STR );
		$st->bindValue( ":rco2", $_POST['rco2'], PDO::PARAM_STR );
		$st->bindValue( ":rco3", $_POST['rco3'], PDO::PARAM_STR );
		$st->bindValue( ":rco4", $_POST['rco4'], PDO::PARAM_STR );
		$st->bindValue( ":rco5", $_POST['rco5'], PDO::PARAM_STR );
		$st->bindValue( ":rco6", $_POST['rco6'], PDO::PARAM_STR );
		$st->bindValue( ":rco7", $_POST['rco7'], PDO::PARAM_STR );
		$st->bindValue( ":rco8", $_POST['rco8'], PDO::PARAM_STR );
		$st->bindValue( ":rco9", $_POST['rco9'], PDO::PARAM_STR );
		$st->bindValue( ":rco10", $_POST['rco10'], PDO::PARAM_STR );
		$st->bindValue( ":lc1", $_POST['lc1'], PDO::PARAM_INT );
		$st->bindValue( ":lc2", $_POST['lc2'], PDO::PARAM_INT );
		$st->bindValue( ":lc3", $_POST['lc3'], PDO::PARAM_INT );
		$st->bindValue( ":lc4", $_POST['lc4'], PDO::PARAM_INT );
		$st->bindValue( ":lc5", $_POST['lc5'], PDO::PARAM_INT );
		$st->bindValue( ":lc6", $_POST['lc6'], PDO::PARAM_INT );
		$st->bindValue( ":lc7", $_POST['lc7'], PDO::PARAM_INT );
		$st->bindValue( ":lc8", $_POST['lc8'], PDO::PARAM_INT );
		$st->bindValue( ":lc9", $_POST['lc9'], PDO::PARAM_INT );
		$st->bindValue( ":lc10", $_POST['lc10'], PDO::PARAM_INT );
		$st->bindValue( ":labour", $_POST['labour'], PDO::PARAM_INT );
		$st->bindValue( ":parts", $_POST['parts'], PDO::PARAM_INT );
		$st->bindValue( ":tba", $_POST['tba'], PDO::PARAM_INT );
		$st->bindValue( ":gasAndOil", $_POST['gasAndOil'], PDO::PARAM_INT );
		$st->bindValue( ":sublet", $_POST['sublet'], PDO::PARAM_INT );
		$st->bindValue( ":others2", $_POST['others2'], PDO::PARAM_INT );
		$st->bindValue( ":total", $_POST['total'], PDO::PARAM_INT );
		$st->bindValue( ":warrantyLabour", $_POST['warrantyLabour'], PDO::PARAM_INT );
		$st->bindValue( ":warrantyParts", $_POST['warrantyParts'], PDO::PARAM_INT );
		$st->bindValue( ":services", $_POST['services'], PDO::PARAM_INT );
		$st->bindValue( ":discountLabour", $_POST['discountLabour'], PDO::PARAM_INT );
		$st->bindValue( ":discountParts", $_POST['discountParts'], PDO::PARAM_INT );
		$st->bindValue( ":vat", $_POST['vat'], PDO::PARAM_INT );
		$st->bindValue( ":netAmountPayable", $_POST['netAmountPayable'], PDO::PARAM_INT );
		$st->bindValue( ":id", $_POST['id'], PDO::PARAM_INT );
		
		if($openDate[0] === true){
          // go ahead and try and insert it into the db
		
             $st->bindValue( ":openDate", $openDate[1]);
          }
		  else
		  {
			  echo "Warning: $openDate[1]" ;
			  }


		if($promDelDate[0] === true){
          // go ahead and try and insert it into the db
		
             $st->bindValue( ":promDelDate", $promDelDate[1]);
          }
		  else
		  {
			  echo "Warning: $promDelDate[1]" ;
			  }
		
		if($revDelDate[0] === true){
          // go ahead and try and insert it into the db
		
             $st->bindValue( ":revDelDate", $revDelDate[1]);
          }
		  else
		  {
			  echo "Warning: $revDelDate[1]" ;
			  }


		if($estimateDate[0] === true){
          // go ahead and try and insert it into the db
		
             $st->bindValue( ":estimateDate", $estimateDate[1]);
          }
		  else
		  {
			  echo "Warning: $estimateDate[1]" ;
			  }

        $st->execute();
        $conn = null;
	}
catch (PDOException $e)
{
$error = 'Error updating submitted information, Please try again later.' . $e->getMessage();
include 'error.html.php';
exit();

} //Closing tag

if($st->rowCount() == 1)
  {
  $success = 'Info successfully updated';
  include 'regSuccess.html.php';
  exit();
  }
else
{
	$error = 'Unable to update information, Please try again later.';
    include 'error.html.php';
exit();
 }

}

My include file: utility_funcs.inc.php is below

function convertDateToMySQL7($openmonth, $openday, $openyear) {
  $openmonth = trim($openmonth);
  $openday = trim($openday);
  $openyear = trim($openyear);
  $result[0] = false;
  if (empty($openmonth) || empty($openday) || empty($openyear)) {
	$result[1] = 'Please fill in all fields';
  } elseif (!is_numeric($openmonth) || !is_numeric($openday) || !is_numeric($openyear)) {
    $result[1] = 'Please use numbers only';
  } elseif (($openmonth < 1 || $openmonth > 12) || ($openday < 1 || $openday > 31) || ($openyear < 1000 || $openyear > 9999)) {
	$result[1] = 'Please use numbers within the correct range';
  } elseif (!checkdate($openmonth,$openday,$openyear)) {
    $result[1] = 'You have used an invalid date';
  } else {
    $result[0] = true;
    $result[1] = "$openyear-$openmonth-$openday";
  }
  return $result;
}

function convertDateToMySQL8($pdmonth, $pdday, $pdyear) {
  $pdmonth = trim($pdmonth);
  $pdday = trim($pdday);
  $pdyear = trim($pdyear);
  $result[0] = false;
  if (empty($pdmonth) || empty($pdday) || empty($pdyear)) {
	$result[1] = 'Please fill in all fields';
  } elseif (!is_numeric($pdmonth) || !is_numeric($pdday) || !is_numeric($pdyear)) {
    $result[1] = 'Please use numbers only';
  } elseif (($pdmonth < 1 || $pdmonth > 12) || ($pdday < 1 || $pdday > 31) || ($pdyear < 1000 || $pdyear > 9999)) {
	$result[1] = 'Please use numbers within the correct range';
  } elseif (!checkdate($pdmonth,$pdday,$pdyear)) {
    $result[1] = 'You have used an invalid date';
  } else {
    $result[0] = true;
    $result[1] = "$pdyear-$pdmonth-$pdday";
  }
  return $result;
}

function convertDateToMySQL9($rdmonth, $rdday, $rdyear) {
  $rdmonth = trim($rdmonth);
  $rdday = trim($rdday);
  $rdyear = trim($rdyear);
  $result[0] = false;
  if (empty($rdmonth) || empty($rdday) || empty($rdyear)) {
	$result[1] = 'Please fill in all fields';
  } elseif (!is_numeric($rdmonth) || !is_numeric($rdday) || !is_numeric($rdyear)) {
    $result[1] = 'Please use numbers only';
  } elseif (($rdmonth < 1 || $rdmonth > 12) || ($rdday < 1 || $rdday > 31) || ($rdyear < 1000 || $rdyear > 9999)) {
	$result[1] = 'Please use numbers within the correct range';
  } elseif (!checkdate($rdmonth,$rdday,$rdyear)) {
    $result[1] = 'You have used an invalid date';
  } else {
    $result[0] = true;
    $result[1] = "$rdyear-$rdmonth-$rdday";
  }
  return $result;
}

//Function for purchase date
function convertDateToMySQL10($esmonth, $esday, $esyear) {
  $esmonth = trim($esmonth);
  $esday = trim($esday);
  $esyear = trim($esyear);
  $result[0] = false;
  if (empty($esmonth) || empty($esday) || empty($esyear)) {
	$result[1] = 'Please fill in all fields';
  } elseif (!is_numeric($esmonth) || !is_numeric($esday) || !is_numeric($esyear)) {
    $result[1] = 'Please use numbers only';
  } elseif (($esmonth < 1 || $esmonth > 12) || ($esday < 1 || $esday > 31) || ($esyear < 1000 || $esyear > 9999)) {
	$result[1] = 'Please use numbers within the correct range';
  } elseif (!checkdate($esmonth,$esday,$esyear)) {
    $result[1] = 'You have used an invalid date';
  } else {
    $result[0] = true;
    $result[1] = "$esyear-$esmonth-$esday";
  }
  return $result;
}

I keep getting this error: Invalid parameter number: parameter was not defined. What are my missing because I’ve studied this code over and again. The same thing goes for the INSERT command for the code above which display a blank page without inserting anything into the database. The insert command uses $this as it is from within a class. The INSERT version is below.


public function insertJobCard()
	{
		require_once('utility_funcs.inc.php');
	
	$openDate = @convertDateToMySQL7($_POST['openmonth'], $_POST['openday'], $_POST['openyear']);
	$promDelDate = @convertDateToMySQL8($_POST['pdmonth'], $_POST['pdday'], $_POST['pdyear']);
	$revDelDate = @convertDateToMySQL9($_POST['rdmonth'], $_POST['rdday'], $_POST['rdyear']);
	$estimateDate = @convertDateToMySQL10($_POST['esmonth'], $_POST['esday'], $_POST['esyear']);

	
		// Does the Asset object already have an ID?
	if ( !is_null( $this->id ) ) trigger_error ( "JobCardManager::insertJobCard(): Attempt to insert a JobCard object that already has its ID property set (to $this->id).", E_USER_ERROR );
		
		// Insert the Job Card
		$conn = DatabaseManager::getConnection();
		$sql = "INSERT INTO jobcard ( repairId, regNumber, openTime, repairOrderType, section, preparedBy, promDelTime, revisedDelTime, kmIn, kmOut, estimateRef, contractorCustomer, splPackage, others, lubService1, lubService2, lubService3, lubService4, lubService5, lubService6, lubService7, lubService8, lubService9, lubService10, lubService11, lubService12, lubService13, lubService14, lubService15, lubService16, lubService17, lubService18, lubService19, lubService20, lubService21, lubService22, noj1, noj2, noj3, noj4, noj5, noj6, noj7, noj8, noj9, noj10, opCode1, opCode2, opCode3, opCode4, opCode5, opCode6, opCode7, opCode8, opCode9, opCode10, rco1, rco2, rco3, rco4, rco5, rco6, rco7, rco8, rco9, rco10, lc1, lc2, lc3, lc4, lc5, lc6, lc7, lc8, lc9, lc10, labour, parts, tba, gasAndOil, sublet, others2, total, warrantyLabour, warrantyParts, services, discountLabour, discountParts, vat, netAmountPayable, openDate, promDelDate, revisedDelDate, estimateDate ) VALUES ( :repairId, :regNumber, :openTime, :repairOrderType, :section, :preparedBy, :promDelTime, :revisedDelTime, :kmIn, :kmOut, :estimateRef, :contractorCustomer, :splPackage, :others, :lubService1, :lubService2, :lubService3, :lubService4, :lubService5, :lubService6, :lubService7, :lubService8, :lubService9, :lubService10, :lubService11, :lubService12, :lubService13, :lubService14, :lubService15, :lubService16, :lubService17, :lubService18, :lubService19, :lubService20, :lubService21, :lubService22, :noj1, :noj2, :noj3, :noj4, :noj5, :noj6, :noj7, :noj8, :noj9, :noj10, :opCode1, :opCode2, :opCode3, :opCode4, :opCode5, :opCode6, :opCode7, :opCode8, :opCode9, :opCode10, :rco1, :rco2, :rco3, :rco4, :rco5, :rco6, :rco7, :rco8, :rco9, :rco10, :lc1, :lc2, :lc3, :lc4, :lc5, :lc6, :lc7, :lc8, :lc9, :lc10, :labour, :parts, :tba, :gasAndOil, :sublet, :others2, :total, :warrantyLabour, :warrantyParts, :services, :discountLabour, :discountParts, :vat, :netAmountPayable, :openDate, :promDelDate, :revisedDelDate, :estimateDate)";
		$st = $conn->prepare ( $sql );
		$st->bindValue( ":repairId", $this->repairId, PDO::PARAM_INT );
		$st->bindValue( ":regNumber", $this->regNumber, PDO::PARAM_STR );
		$st->bindValue( ":openTime", $this->openTime, PDO::PARAM_INT );
		$st->bindValue( ":repairOrderType", $this->repairOrderType, PDO::PARAM_STR );
		$st->bindValue( ":section", $this->section, PDO::PARAM_INT );
		#$st->bindValue( ":pryRepairOrderNo", $this->pryRepairOrderNo, PDO::PARAM_STR );
		$st->bindValue( ":preparedBy", $this->preparedBy, PDO::PARAM_STR );
		$st->bindValue( ":promDelTime", $this->promDelTime, PDO::PARAM_INT );
		#$st->bindValue( ":revisedDelDate", $this->revisedDelDate, PDO::PARAM_INT );
		$st->bindValue( ":revisedDelTime", $this->revisedDelTime, PDO::PARAM_INT );
		$st->bindValue( ":kmIn", $this->kmIn, PDO::PARAM_INT );
		$st->bindValue( ":kmOut", $this->kmOut, PDO::PARAM_INT );
		$st->bindValue( ":estimateRef", $this->estimateRef, PDO::PARAM_STR );
		$st->bindValue( ":contractorCustomer", $this->contractorCustomer, PDO::PARAM_STR );
		$st->bindValue( ":splPackage", $this->splPackage, PDO::PARAM_STR );
		#$st->bindValue( ":estimateDate", $this->estimateDate, PDO::PARAM_INT );
		$st->bindValue( ":others", $this->others, PDO::PARAM_INT );
		$st->bindValue( ":lubService1", $this->lubService1, PDO::PARAM_STR );
		$st->bindValue( ":lubService2", $this->lubService2, PDO::PARAM_STR );
		$st->bindValue( ":lubService3", $this->lubService3, PDO::PARAM_STR );
		$st->bindValue( ":lubService4", $this->lubService4, PDO::PARAM_STR );
		$st->bindValue( ":lubService5", $this->lubService5, PDO::PARAM_STR );
		$st->bindValue( ":lubService6", $this->lubService6, PDO::PARAM_STR );
		$st->bindValue( ":lubService7", $this->lubService7, PDO::PARAM_STR );
		$st->bindValue( ":lubService8", $this->lubService8, PDO::PARAM_STR );
		$st->bindValue( ":lubService9", $this->lubService9, PDO::PARAM_STR );
		$st->bindValue( ":lubService10", $this->lubService10, PDO::PARAM_STR );
		$st->bindValue( ":lubService11", $this->lubService11, PDO::PARAM_STR );
		$st->bindValue( ":lubService12", $this->lubService12, PDO::PARAM_STR );
		$st->bindValue( ":lubService13", $this->lubService13, PDO::PARAM_STR );
		$st->bindValue( ":lubService14", $this->lubService14, PDO::PARAM_STR );
		$st->bindValue( ":lubService15", $this->lubService15, PDO::PARAM_STR );
		$st->bindValue( ":lubService16", $this->lubService16, PDO::PARAM_STR );
		$st->bindValue( ":lubService17", $this->lubService17, PDO::PARAM_STR );
		$st->bindValue( ":lubService18", $this->lubService18, PDO::PARAM_STR );
		$st->bindValue( ":lubService19", $this->lubService19, PDO::PARAM_STR );
		$st->bindValue( ":lubService20", $this->lubService20, PDO::PARAM_STR );
		$st->bindValue( ":lubService21", $this->lubService21, PDO::PARAM_STR );
		$st->bindValue( ":lubService22", $this->lubService22, PDO::PARAM_STR );
		$st->bindValue( ":noj1", $this->noj1, PDO::PARAM_STR );
		$st->bindValue( ":noj2", $this->noj2, PDO::PARAM_STR );
		$st->bindValue( ":noj3", $this->noj3, PDO::PARAM_STR );
		$st->bindValue( ":noj4", $this->noj4, PDO::PARAM_STR );
		$st->bindValue( ":noj5", $this->noj5, PDO::PARAM_STR );
		$st->bindValue( ":noj6", $this->noj6, PDO::PARAM_STR );
		$st->bindValue( ":noj7", $this->noj7, PDO::PARAM_STR );
		$st->bindValue( ":noj8", $this->noj8, PDO::PARAM_STR );
		$st->bindValue( ":noj9", $this->noj9, PDO::PARAM_STR );
		$st->bindValue( ":noj10", $this->noj10, PDO::PARAM_STR );
		$st->bindValue( ":opCode1", $this->opCode1, PDO::PARAM_STR );
		$st->bindValue( ":opCode2", $this->opCode2, PDO::PARAM_STR );
		$st->bindValue( ":opCode3", $this->opCode3, PDO::PARAM_STR );
		$st->bindValue( ":opCode4", $this->opCode4, PDO::PARAM_STR );
		$st->bindValue( ":opCode5", $this->opCode5, PDO::PARAM_STR );
		$st->bindValue( ":opCode6", $this->opCode6, PDO::PARAM_STR );
		$st->bindValue( ":opCode7", $this->opCode7, PDO::PARAM_STR );
		$st->bindValue( ":opCode8", $this->opCode8, PDO::PARAM_STR );
		$st->bindValue( ":opCode9", $this->opCode9, PDO::PARAM_STR );
		$st->bindValue( ":opCode10", $this->opCode10, PDO::PARAM_STR );
		$st->bindValue( ":rco1", $this->rco1, PDO::PARAM_STR );
		$st->bindValue( ":rco2", $this->rco2, PDO::PARAM_STR );
		$st->bindValue( ":rco3", $this->rco3, PDO::PARAM_STR );
		$st->bindValue( ":rco4", $this->rco4, PDO::PARAM_STR );
		$st->bindValue( ":rco5", $this->rco5, PDO::PARAM_STR );
		$st->bindValue( ":rco6", $this->rco6, PDO::PARAM_STR );
		$st->bindValue( ":rco7", $this->rco7, PDO::PARAM_STR );
		$st->bindValue( ":rco8", $this->rco8, PDO::PARAM_STR );
		$st->bindValue( ":rco9", $this->rco9, PDO::PARAM_STR );
		$st->bindValue( ":rco10", $this->rco10, PDO::PARAM_STR );
		$st->bindValue( ":lc1", $this->lc1, PDO::PARAM_INT );
		$st->bindValue( ":lc2", $this->lc2, PDO::PARAM_INT );
		$st->bindValue( ":lc3", $this->lc3, PDO::PARAM_INT );
		$st->bindValue( ":lc4", $this->lc4, PDO::PARAM_INT );
		$st->bindValue( ":lc5", $this->lc5, PDO::PARAM_INT );
		$st->bindValue( ":lc6", $this->lc6, PDO::PARAM_INT );
		$st->bindValue( ":lc7", $this->lc7, PDO::PARAM_INT );
		$st->bindValue( ":lc8", $this->lc8, PDO::PARAM_INT );
		$st->bindValue( ":lc9", $this->lc9, PDO::PARAM_INT );
		$st->bindValue( ":lc10", $this->lc10, PDO::PARAM_INT );
		$st->bindValue( ":labour", $this->labour, PDO::PARAM_INT );
		$st->bindValue( ":parts", $this->parts, PDO::PARAM_INT );
		$st->bindValue( ":tba", $this->tba, PDO::PARAM_INT );
		$st->bindValue( ":gasAndOil", $this->gasAndOil, PDO::PARAM_INT );
		$st->bindValue( ":sublet", $this->sublet, PDO::PARAM_INT );
		$st->bindValue( ":others2", $this->others2, PDO::PARAM_INT );
		$st->bindValue( ":total", $this->total, PDO::PARAM_INT );
		$st->bindValue( ":warrantyLabour", $this->warrantyLabour, PDO::PARAM_INT );
		$st->bindValue( ":warrantyParts", $this->warrantyParts, PDO::PARAM_INT );
		$st->bindValue( ":services", $this->services, PDO::PARAM_INT );
		$st->bindValue( ":discountLabour", $this->discountLabour, PDO::PARAM_INT );
		$st->bindValue( ":discountParts", $this->discountParts, PDO::PARAM_INT );
		$st->bindValue( ":vat", $this->vat, PDO::PARAM_INT );
		$st->bindValue( ":netAmountPayable", $this->netAmountPayable, PDO::PARAM_INT );
		
		if($openDate[0] === true){
          // go ahead and try and insert it into the db
		
             $st->bindValue( ":openDate", $openDate[1]);
          }
		  else
		  {
			  echo "Warning: $openDate[1]" ;
			  }


		if($promDelDate[0] === true){
          // go ahead and try and insert it into the db
		
             $st->bindValue( ":promDelDate", $promDelDate[1]);
          }
		  else
		  {
			  echo "Warning: $promDelDate[1]" ;
			  }
		
		if($revDelDate[0] === true){
          // go ahead and try and insert it into the db
		
             $st->bindValue( ":revDelDate", $revDelDate[1]);
          }
		  else
		  {
			  echo "Warning: $revDelDate[1]" ;
			  }


		if($estimateDate[0] === true){
          // go ahead and try and insert it into the db
		
             $st->bindValue( ":estimateDate", $estimateDate[1]);
          }
		  else
		  {
			  echo "Warning: $estimateDate[1]" ;
			  }

		$st->execute();
		$this->id = $conn->lastInsertId();
		$conn = null;
		}

And the code calling the insert on the index page is below:


elseif(isset($_POST['button']) && $_POST['button'] == 'Submit JC')
{
  	
  $results = array();
  $data = new JobCardManager();
  $data->storeFormValues( $_POST );
  $data->insertJobCard();

	  $output = "Information successfully added to the database";
	  include 'output.html.php';
      exit();

}
2

Appears to be a similar problem (resolved) here on stackoverflow. Hope it helps.

3

Thanks man. Ive gone through that post but my challenge is still there. Maybe I need a fresh eye to look this code up for me. Funny enough, I have a similar code elsewhere in the project which is working fine.

4

OMG, this code for updating and inserting a row looks like a nightmare! This is what happens when people flock to the PDO-like concept of “let’s use prepared statements wherever possible” without actually thinking if it makes sense. You have a very long list of field names and their values that you pass to the UPDATE/INSERT statement and those lists have to match, that is what you first inject in the SET part of the statement must match all the bindValue() occurrences. In the case when these lists are long it becomes hard to see if they match and when, as is the case here, you also have some conditional bindValue() occurrences added to the mix, it becomes even harder to track any mismatches. In this case a simple old-style building of the query by concatenating string parts results in much more maintainable code because you have all values in succession as a single list instead of two. Consider this:


$sql = "UPDATE jobcard SET ";

$sql.= "  repairId=" .$conn->quote($_POST['repairId'], PDO::PARAM_INT);
$sql.= ", regNumber=" .$conn->quote($_POST['regNumber'], PDO::PARAM_STR);
$sql.= ", openTime=" .$conn->quote($_POST['openTime'], PDO::PARAM_STR);
$sql.= ", repairOrderType=" .$conn->quote($_POST['repairOrderType'], PDO::PARAM_STR);
$sql.= ", section=" .$conn->quote($_POST['section'], PDO::PARAM_STR);
// here come the rest of the fields...
// ...

// and lastly conditional fields like this:
if($openDate[0] === true)
{
	// go ahead and try and insert it into the db
	$sql.= ", openDate=" .$conn->quote($_POST['openDate'], PDO::PARAM_STR);
}
else
{
	echo "Warning: $openDate[1]" ;
}

// and so on...

// finally we execute the query
$conn->exec($sql);

If you want to add, remove or change a field for UPDATE you only have to change one line of code instead of having to find two places where the field name is mentioned and maintaining their mutual relationship.

In the case of your INSERT statement the mess is even greater because you actually have three separate lists of fields to maintain. You might use the same INSERT INTO … SET … syntax as in the case of UPDATE but this will not work for all types of databases. But still simple building the query by concatenation results in a much more readable code in my opinion.

The best solution would be to extend the PDO class and write your own method for inserting rows, which would handle all the query creation for you while the way you use it remains pretty clear, for example:


$stmt = $conn->createInsertStmt("jobcard");
$stmt->addField('repairId', $this->repairId, PDO::PARAM_INT);
$stmt->addField('regNumber', $this->regNumber, PDO::PARAM_STR);
$stmt->addField('openTime', $this->openTime, PDO::PARAM_STR);
$stmt->addField('repairOrderType', $this->repairOrderType, PDO::PARAM_STR);
// add the rest of fields in the same way...
//...

// execute the statement
$stmt->exec();

Isn’t that much more maintainable?

I don’t mean to criticize your code here. But this is where I think PDO fails most - it doesn’t provide any methods to perform common database operations in an easy and elegant way, and by “strongly recommending” the use of prepared statements actually makes people create such hard to maintain code as you have posted in your example. And to be honest I’m not surprised you are lost when trying to spot the problem here!

5

Thanks a lot man. I’m new to OOP and just trying to see how I can implement it. I will try out your solutions and get back to you. Thanks once again for the new lesson.

6

Lemon Juice, I have tried the code you showed me but the challenge now is how do I concatenate the WHERE id = :id in the new sql structure you showed me. Thanks in advance.

7

Just do this after you have concatenated all fields:


$sql.= " WHERE id = " .$conn->quote($_POST['id'], PDO::PARAM_INT);
8

Thanks man. I’ll get it done now.

9

Lemon Juice, thanks so far. The update was carried out now but all my dates was reset to 1970. What do I do to rectify this? Please don’t forget that my date is coming from 3 different fields which the utility function I included at the of the INSERT command help me concatenate.

10

I have the date issue handled. Thanks a lot.

11

Okay, thanks for letting us know!