This code grabs data from my server and allows me to edit and save the data back into the server. Error is clearly coming from the SQL but I can't see to figure out why.
I've been going over this code for hours trying to find where it's giving me error SQLSTATE[HY093]: Invalid parameter number, but I can't seem to find it.
As far as I can tell all the parameters match.
Do you guys see anything I'm missing?
<?php require 'templates/header.php'; ?>
<?php include '../common.php'; ?>
<!-- container -->
<div class="container">
<div class="page-header">
<h1>Update Product</h1>
</div>
<?php
$id=isset($_GET['id']) ? $_GET['id'] : die('Error: Record ID not found.');
include '../config.php';
try {
$connection = new PDO($dsn, $username, $password, $options);
//Prepare select query
$sql = 'SELECT * FROM inspections WHERE id = ? LIMIT 0,1';
$statement = $connection->prepare($sql);
$statement->bindParam(1, $id);
//execute query
$statement->execute();
//store retrieved row to a variable
$row = $statement->fetch(PDO::FETCH_ASSOC);
//values to fill up the form
$inspectionAddress = $row['inspectionAddress'];
$city = $row['city'];
$zipCode = $row['zipCode'];
$subdivision = $row['subdivision'];
$unitNumber = $row['unitNumber'];
$buildingNumber = $row['buildingNumber'];
$squareFootage = $row['squareFootage'];
$stories = $row['stories'];
$year = $row['year'];
$bedroomNumber = $row['bedroomNumber'];
$bathroomNumber = $row['bathroomNumber'];
$garageNumber = $row['garageNumber'];
$acNumber = $row['acNumber'];
$buyerFirstName = $row['buyerFirstName'];
$buyerLastName = $row['buyerLastName'];
$buyerCellPhone = $row['buyerCellPhone'];
$buyerEmail = $row['buyerEmail'];
$buyerHomePhone = $row['buyer2Phone'];
$buyerAgentOfficeName = $row['buyerAgentOfficeName'];
$buyerAgentName = $row['buyerAgentName'];
$buyerAgentCellPhone = $row['buyerAgent1Phone'];
$buyerAgentEmail = $row['buyerAgent1Email'];
$buyerAgentOfficePhone = $row['buyerAgent2Phone'];
$sellerLastName = $row['sellerLastName'];
$sellerCellPhone = $row['sellerPhone1'];
$sellAgentOfficeName = $row['sellAgentOfficeName'];
$sellAgentName = $row['sellAgentName'];
$sellAgentCellPhone = $row['sellAgentCellPhone'];
$sellAgentOfficePhone = $row['sellAgentOfficePhone'];
$inspectionNumber = $row['inspectionNumber'];
$inspectionDate = $row['inspectionDate'];
$inspectionDay = $row['inspectionDay'];
$inspectionTime = $row['inspectionTime'];
$inspector = $row['mainInspector'];
$roofer = $row['roofInspector'];
$termite = $row['termiteInspector'];
$fullInspection = $row['fullInspection'];
$fullpoolInspection = $row['fullPoolInspection'];
$townhousevillaInspection = $row['townhouseVillaInspection'];
$condoInspection = $row['condoInspection'];
$duplexInspection = $row['duplexInspection'];
$triplexInspection = $row['triplexInspection'];
$fourplexInspection = $row['fourplexInspection'];
$mitigationInspection = $row['mitigationInspection'];
$fourptInspection = $row['fourPointInspection'];
$addlAreasWell = $row['addlAreasWell'];
$addlAreasSeaWall = $row['addlAreasSeaWall'];
$addlAreasMold = $row['addlAreasMold'];
$addlAreasCrawlSpace = $row['addlAreasCrawlSpace'];
$addlAreasExtraStructure = $row['addlAreasExtraStructure'];
$addlAreasOther = $row['addlAreasOther'];
$additionalNotes = $row['additionalNotes'];
$scheduledBy = $row['scheduledBy'];
$referredBy = $row['referredBy'];
$cbsCode = $row['cbsCode'];
$lockboxCode = $row['lockboxCode'];
$gateCode = $row['gateCode'];
$county = $row['county'];
} catch (PDOException $error) {
echo $sql . "<br>" . $error->getMessage();
}
?>
<?php
// check if form was submitted
if($_POST){
try{
// write update query
$sql = "UPDATE inspections
SET id = :id,
inspectionAddress = :inspectionAddress,
city = :city,
zipCode = :zipCode,
subdivision = :subdivision,
unitNumber = :unitNumber,
buildingNumber = :buildingNumber,
squareFootage = :squareFootage,
stories = :stories,
year = :year,
bedroomNumber = :bedroomNumber,
bathroomNumber = :bathroomNumber,
garageNumber = :garageNumber,
acNumber = :acNumber,
buyerFirstName = :buyerFirstName,
buyerLastName = :buyerLastName,
buyerCellPhone = :buyerCellPhone,
buyerEmail = :buyerEmail,
buyer2Phone = :buyer2Phone,
buyerAgentOfficeName = :buyerAgentOfficeName,
buyerAgentName = :buyerAgentName,
buyerAgent1Phone = :buyerAgent1Phone,
buyerAgent1Email = :buyerAgent1Email,
buyerAgent2Phone = :buyerAgent2Phone,
sellerLastName = :sellerLastName,
sellerPhone1 = :sellerPhone1,
sellAgentOfficeName = :sellAgentOfficeName,
sellAgentName = :sellAgentName,
sellAgentCellPhone = :sellAgentCellPhone,
sellAgentOfficePhone = :sellAgentOfficePhone,
inspectionNumber = :inspectionNumber,
inspectionDate = :inspectionDate,
inspectionDay = :inspectionDay,
inspectionTime = :inspectionTime,
mainInspector = :mainInspector,
roofInspector = :roofInspector,
termiteInspector = :termiteInspector,
fullInspection = :fullInspection,
fullPoolInspection = :fullPoolInspection,
townhouseVillaInspection = :townhouseVillaInspection,
condoInspection = :condoInspection,
duplexInspection = :duplexInspection,
triplexInspection = :triplexInspection,
fourplexInspection = :fourplexInspection,
mitigationInspection = :mitigationInspection,
fourPointInspection = :fourPointInspection,
addlAreasWell = :addlAreasWell,
addlAreasSeaWall = :addlAreasSeaWall,
addlAreasMold = :addlAreasMold,
addlAreasCrawlSpace = :addlAreasCrawlSpace,
addlAreasExtraStructure = :addlAreasExtraStructure,
addlAreasOther = :addlAreasOther,
additionalNotes = :additionalNotes,
scheduledBy = :scheduledBy,
referredBy = :referredBy,
cbsCode = :cbsCode,
lockboxCode = :lockboxCode,
gateCode = :gateCode,
county = :county
WHERE id = :id";
// prepare query for excecution
$statement = $connection->prepare($sql);
// posted values
$inspectionAddress = escapePost('streetAddress');
$city = escapePost('city');
$zipCode = escapePost('zipCode');
$subdivision = escapePost('subdivision');
$unitNumber = escapePost('unitNumber');
$buildingNumber = escapePost('buildingNumber');
$squareFootage = escapePost('squareFootage');
$stories = escapePost('stories');
$year = escapePost('year');
$bedroomNumber = escapePost('bedroomNumber');
$bathroomNumber = escapePost('bathroomNumber');
$garageNumber = escapePost('garageNumber');
$acNumber = escapePost('acNumber');
$buyerFirstName = escapePost('buyerFirstName');
$buyerLastName = escapePost('buyerLastName');
$buyerCellPhone = escapePost('buyerCellPhone');
$buyerEmail = escapePost('buyerEmail');
$buyerHomePhone = escapePost('buyerCellPhone');
$buyerAgentOfficeName = escapePost('buyerAgentOfficeName');
$buyerAgentName = escapePost('buyerAgentName');
$buyerAgentCellPhone = escapePost('buyerAgentCellPhone');
$buyerAgentEmail = escapePost('buyerAgentEmail');
$buyerAgentOfficePhone = escapePost('buyerAgentOfficePhone');
$sellerLastName = escapePost('sellerLastName');
$sellerCellPhone = escapePost('sellerCellPhone');
$sellAgentOfficeName = escapePost('sellAgentOfficeName');
$sellAgentName = escapePost('sellAgentName');
$sellAgentCellPhone = escapePost('sellAgentCellPhone');
$sellAgentOfficePhone = escapePost('sellAgentOfficePhone');
$inspectionNumber = escapePost('inspectionNumber');
$inspectionDate = escapePost('inspectionDate');
$inspectionDay = escapePost('inspectionDay');
$inspectionTime = escapePost('inspectionTime');
$inspector = escapePost('inspector');
$roofer = escapePost('roofer');
$termite = escapePost('termite');
$fullInspection = escapePost('fullInspection');
$fullpoolInspection = escapePost('full-poolInspection');
$townhousevillaInspection = escapePost('townhouse-villaInspection');
$condoInspection = escapePost('condoInspection');
$duplexInspection = escapePost('duplexInspection');
$triplexInspection = escapePost('triplexInspection');
$fourplexInspection = escapePost('fourplexInspection');
$mitigationInspection = escapePost('mitigationInspection');
$fourptInspection = escapePost('4ptInspection');
$addlAreasWell = escapePost('addlAreasWell');
$addlAreasSeaWall = escapePost('addlAreasSeaWall');
$addlAreasMold = escapePost('addlAreasMold');
$addlAreasCrawlSpace = escapePost('addlAreasCrawlSpace');
$addlAreasExtraStructure = escapePost('addlAreasExtraStructure');
$addlAreasOther = escapePost('addlAreasOther');
$additionalNotes = escapePost('additionalNotes');
$scheduledBy = escapePost('scheduledBy');
$referredBy = escapePost('referredBy');
$cbsCode = escapePost('cbsCode');
$lockboxCode = escapePost('lockboxCode');
$gateCode = escapePost('gateCode');
$county = escapePost('county');
// bind the parameters
$statement->bindParam(':inspectionAddress', $inspectionAddress);
$statement->bindParam(':city', $city);
$statement->bindParam(':zipCode', $zipCode);
$statement->bindParam(':subdivision', $subdivision);
$statement->bindParam(':unitNumber', $unitNumber);
$statement->bindParam(':buildingNumber', $buildingNumber);
$statement->bindParam(':squareFootage', $squareFootage);
$statement->bindParam(':stories', $stories);
$statement->bindParam(':year', $year);
$statement->bindParam(':bedroomNumber', $bedroomNumber);
$statement->bindParam(':bathroomNumber', $bathroomNumber);
$statement->bindParam(':garageNumber', $garageNumber);
$statement->bindParam(':acNumber', $acNumber);
$statement->bindParam(':buyerFirstName', $buyerFirstName);
$statement->bindParam(':buyerLastName', $buyerLastName);
$statement->bindParam(':buyerCellPhone', $buyerCellPhone);
$statement->bindParam(':buyerEmail', $buyerEmail);
$statement->bindParam(':buyer2Phone', $buyer2Phone);
$statement->bindParam(':buyerAgentOfficeName', $buyerAgentOfficeName);
$statement->bindParam(':buyerAgentName', $buyerAgentName);
$statement->bindParam(':buyerAgent1Phone', $buyerAgent1Phone);
$statement->bindParam(':buyerAgent1Email', $buyerAgent1Email);
$statement->bindParam(':buyerAgent2Phone', $buyerAgent2Phone);
$statement->bindParam(':sellerLastName', $sellerLastName);
$statement->bindParam(':sellerPhone1', $sellerPhone1);
$statement->bindParam(':sellAgentOfficeName', $sellAgentOfficeName);
$statement->bindParam(':sellAgentName', $sellAgentName);
$statement->bindParam(':sellAgentCellPhone', $sellAgentCellPhone);
$statement->bindParam(':sellAgentOfficePhone', $sellAgentOfficePhone);
$statement->bindParam(':inspectionNumber', $inspectionNumber);
$statement->bindParam(':inspectionDate', $inspectionDate);
$statement->bindParam(':inspectionDay', $inspectionDay);
$statement->bindParam(':inspectionTime', $inspectionTime);
$statement->bindParam(':mainInspector', $mainInspector);
$statement->bindParam(':roofInspector', $roofInspector);
$statement->bindParam(':termiteInspector', $termiteInspector);
$statement->bindParam(':fullInspection', $fullInspection);
$statement->bindParam(':fullPoolInspection', $fullPoolInspection);
$statement->bindParam(':townhouseVillaInspection', $townhouseVillaInspection);
$statement->bindParam(':condoInspection', $condoInspection);
$statement->bindParam(':duplexInspection', $duplexInspection);
$statement->bindParam(':triplexInspection', $triplexInspection);
$statement->bindParam(':fourplexInspection', $fourplexInspection);
$statement->bindParam(':mitigationInspection', $mitigationInspection);
$statement->bindParam(':fourPointInspection', $fourPointInspection);
$statement->bindParam(':addlAreasWell', $addlAreasWell);
$statement->bindParam(':addlAreasSeaWall', $addlAreasSeaWall);
$statement->bindParam(':addlAreasMold', $addlAreasMold);
$statement->bindParam(':addlAreasCrawlSpace', $addlAreasCrawlSpace);
$statement->bindParam(':addlAreasExtraStructure', $addlAreasExtraStructure);
$statement->bindParam(':addlAreasOther', $addlAreasOther);
$statement->bindParam(':additionalNotes', $additionalNotes);
$statement->bindParam(':scheduledBy', $scheduledBy);
$statement->bindParam(':referredBy', $referredBy);
$statement->bindParam(':cbsCode', $cbsCode);
$statement->bindParam(':lockboxCode', $lockboxCode);
$statement->bindParam(':gateCode', $gateCode);
$statement->bindParam(':county', $county);
$statement->bindParam(':id', $id);
// Execute the query
if($statement->execute()){
echo "<div class='alert alert-success'>Record was updated.</div>";
}else{
echo "<div class='alert alert-danger'>Unable to update record. Please try again.</div>";
}
}
// show errors
catch(PDOException $error){
echo $sql . "<br>" . $error->getMessage();
}
}
?>
common.php:
<?php
/**
* Ths file will store functions we may need to use.
*/
// Escape HTML for output:
function escape($html) {
return htmlspecialchars($html, ENT_QUOTES | ENT_SUBSTITUTE, "UTF-8");
};
function escapePost($posted) {
return htmlspecialchars(strip_tags($_POST[$posted]));
}
?>
