Ricky T Ricky T - 3 months ago 16
MySQL Question

PDO not updating MYSQL

Banging my head, please help. I want to update 7 out of 14 fields in my table. Not getting any errors, but not sending data to table either. I'm new at PDO and have been working on this for the last 2 days. The fields I want to update are using the bindparm with POST, the others field values are not to change. Thank You for any help.

<html>
<form role="form" method="POST" action="update.php">
<div>
<label class="control-label">Contractor ID</label>
<input type="text" name="contractor_id" />
</div>
<div>
<label class="control-label">Firstname</label>
<input type="text" name="firstname" />
</div>
<div>
<label class="control-label">Lastname</label>
<input type="text" value="Lastname" name="lastname" />
</div>
<div>
<label class="control-label">Address</label>
<input type="text" value="Address" name="address" />
</div>
<div>
<label class="control-label">City</label>
<input type="text" value="City" name="city" />
</div>
<div>
<label class="control-label">State</label>
<input type="text" value="State" name="state" />
</div>
<div>
<label class="control-label">Zipcode</label>
<input type="text" value="Zipcode" name="zip" />
</div>
<div>
<label class="control-label">Email</label>
<input type="text" value="Email" name="email" />
</div>
<div class="margiv-top-10">
<input type="submit" name="submit" class="email" value="Update" >
</div>
</form>
</html>


PHP


try {
$pdo = new PDO($dsn, $user, $password);
$pdo ->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
echo 'Connected to database';
echo '<br>';

$sql = "UPDATE contractor_keys SET
contractor_id = :contractor_id,
firstname = :firstname,
lastname = :lastname,
address = :address,
city = :city,
state = :state,
zip = :zip,
email = :email,
areacode = :areacode,
phonenumber = :phonenumber,
ssnumber = :ssnumber,
rate = :rate,
mykey = :mykey,
passwd = :passwd

WHERE contractor_id = :contractor_id";

$stmt = $pdo->prepare($sql);

$stmt->bindParam(':contractor_id', $contractor_id, PDO::PARAM_STR);
$stmt->bindParam(':firstname', $_POST['firstname'], PDO::PARAM_STR);
$stmt->bindParam(':lastname', $_POST['lastname'], PDO::PARAM_STR);
$stmt->bindParam(':address', $_POST['address'], PDO::PARAM_STR);
$stmt->bindParam(':city', $_POST['city'], PDO::PARAM_STR);
$stmt->bindParam(':state', $_POST['state'], PDO::PARAM_STR);
$stmt->bindParam(':zip', $_POST['zip'], PDO::PARAM_STR);
$stmt->bindParam(':email', $_POST['email'], PDO::PARAM_STR);
$stmt->bindParam(':areacode', $arecode, PDO::PARAM_INT);
$stmt->bindParam(':phonenumber', $phonenumber, PDO::PARAM_STR);
$stmt->bindParam(':ssnumber', $ssnumber, PDO::PARAM_STR);
$stmt->bindParam(':rate', $rate, PDO::PARAM_INT);
$stmt->bindParam(':mykey', $mykey, PDO::PARAM_STR);
$stmt->bindParam(':passwd', $passwd, PDO::PARAM_STR);
$stmt->execute();
echo 'Record Updated';

}

catch (PDOException $e) {
echo 'Connection failed: ' . $e->getMessage();
}
?>

Answer

You should replace $contractor_id by $_POST["contractor_id"].See the code below:

Before:

$stmt->bindParam(':contractor_id', $contractor_id, PDO::PARAM_STR); 

After:

$stmt->bindParam(':contractor_id', $_POST["contractor_id"], PDO::PARAM_STR); 

One more thing.You dont need to update the contractor_id field if you are using it in the where condition. See the code below:

Before:

$sql = "UPDATE contractor_keys SET  
    contractor_id = :contractor_id,
    firstname = :firstname,
    lastname = :lastname,
    address = :address,
    city = :city,
    state = :state, 
    zip = :zip,
    email = :email,
    areacode = :areacode,
    phonenumber = :phonenumber,
    ssnumber = :ssnumber,
    rate = :rate,
    mykey = :mykey,
    passwd = :passwd

WHERE contractor_id = :contractor_id";

After:

$sql = "UPDATE contractor_keys SET  
    firstname = :firstname,
    lastname = :lastname,
    address = :address,
    city = :city,
    state = :state, 
    zip = :zip,
    email = :email,
    areacode = :areacode,
    phonenumber = :phonenumber,
    ssnumber = :ssnumber,
    rate = :rate,
    mykey = :mykey,
    passwd = :passwd

WHERE contractor_id = :contractor_id";
Comments