I'm trying to run a PDO update statement, but none of the fields are being updated. Here is my PDO query. I've gone through and tried to find where the values were being changed and found that where being assigned nothing. I found the problem right when the values are escaped (You'll see my comment placed there). I know it probably something I'm overlooking but I haven't been able to figure out yet.
if(isset($_POST['submit']))
{
if(isset($_POST['name'])){ $name = $_POST['name'];}else{ $name = '';}
if(isset($_POST['city'])){ $city = $_POST['city'];}else{ $city = '';}
if(isset($_POST['state'])){ $state = $_POST['state'];}else{ $state = '';}
if(isset($_POST['address_line1'])){ $address_line1 = $_POST['address_line1'];}else{ $address_line1 = '';}
if(isset($_POST['address_line2'])){ $address_line2 = $_POST['address_line2'];}else{ $address_line2 = '';}
if(isset($_POST['city'])){ $city = $_POST['city'];}else{ $city = '';}
if(isset($_POST['state'])){ $state = $_POST['state'];}else{ $state = '';}
if(isset($_POST['zip_code'])){ $zip_code = $_POST['zip_code'];}else{ $zip_code = '';}
if(isset($_POST['last_modified_by'])){ $last_modified_by = $_POST['last_modified_by'];}else{ $last_modified_by = 'admin';}
$last_modified_date = date('Y-m-d H:i:s');
$confirmcode = 'y';
if(isset($_POST['bitactive'])){ $bitactive = $_POST['bitactive'];}else{ $bitactive = '';}
//Test portion 1 = Values are correct
// echo $address_line1 . "<p>";
// echo $city . "<p>";
// echo $zip_code . "<p>";
// exit;
$support_broker_id = $_GET['id'];
$user_exists = "SELECT * FROM lu_agency WHERE agency_id =". $support_broker_id;
$statement = $conn->query($sql);
$result = $statement->fetch();
$count = $statement->rowCount();
$name = $row['name'];
$address_line1 = $row['address_line1'];
$address_line2 = $row['address_line2'];
$city = $row['city'];
$state = $row['state'];
$zip_code = $row['zip_code'];
$last_modified_by = $row['last_modified_by'];
$last_modified_date = $row['last_modified_date'];
$bitactive = $row['bitactive'];
//Test portion two: Values are correct
// echo $address_line1 . "<p>";
// echo $city . "<p>";
// echo $zip_code . "<p>";
// exit;
if($count > 0)
{
$sqlupdate = "UPDATE lu_agency
SET name = :name,
address_line1 = :address_line1,
address_line2 = :address_line2,
city = :city,
state = :state,
zip_code = :zip_code,
last_modified_by = :last_modified_by,
last_modified_date = :last_modified_date,
bitactive = :bitactive
WHERE agency_id= ". $support_broker_id;
//Here is where only $city and $support_broker_id have values, the others don't show up
echo $address_line1 . "<p>";
echo $city . "<p>";
echo $zip_code . "<p>";
echo $support_broker_id . "<p>";
exit;
$preparedstmt = $conn->prepare($sqlupdate);
$preparedstmt->execute(
array(
':name'=>$name,
':address_line1'=>$address_line1,
':address_line2'=>$address_line2,
':city'=>$city,
':state'=>$state,
':zip_code'=>$zip_code,
':last_modified_by'=>$last_modified_by,
':last_modified_date'=>$last_modified_date,
':bitactive'=>$bitactive
)
);
header("Location: http://173.254.127.52/~avenuet7/supporttables.php?msg=1");
}
}
$row
is undefined. It should be $result
:
$result = $statement->fetch(PDO::FETCH_ASSOC); // you declared `$result` not `$row`
And why not use prepared statements all through out:
$user_exists = "SELECT * FROM lu_agency WHERE agency_id =". $support_broker_id; // still directly injecting?
Final look:
$support_broker_id = $_GET['id'];
$user_exists = "SELECT * FROM lu_agency WHERE agency_id = :support_broker_id ";
// not `$sql` use `$user_exists`!
$statement = $conn->prepare($user_exists);
$statement->bindParam(':support_broker_id', $support_broker_id);
$statement->execute();
$count = $statement->rowCount();
if($count > 0) {
$result = $statement->fetch(PDO::FETCH_ASSOC);
$sqlupdate = "
UPDATE lu_agency SET
name = :name,
address_line1 = :address_line1,
address_line2 = :address_line2,
city = :city,
state = :state,
zip_code = :zip_code,
last_modified_by = :last_modified_by,
last_modified_date = :last_modified_date,
bitactive = :bitactive
WHERE agency_id = :support_broker_id
";
$preparedstmt = $conn->prepare($sqlupdate);
$preparedstmt->execute(
array(
':name' => $result['name'],
':address_line1' => $result['address_line1'],
':address_line2' => $result['address_line2'],
':city' => $result['city'],
':state' => $result['state'],
':zip_code' => $result['zip_code'],
':last_modified_by' => $result['last_modified_by'],
':last_modified_date' => $result['last_modified_date'],
':bitactive' => $result['bitactive'],
':support_broker_id' => $support_broker_id,
));
header("Location: http://173.254.127.52/~avenuet7/supporttables.php?msg=1");
}
Sidenote: Always add this after making a connection:
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);