PDO job does not set values
I'm trying to run a PDO update statement, but none of the fields are updated. Here is my PDO request. I went through and tried to find where the values ββwere changed and found that where nothing is assigned. I found the problem correctly when values ββare escaped (you will see my comment posted there). I know this is probably what I am missing, but I haven't been able to figure it 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");
}
}
+3
source to share
1 answer
$row
- undefined. It should be $result
:
$result = $statement->fetch(PDO::FETCH_ASSOC); // you declared `$result` not `$row`
And why not use ready-made statements all over:
$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 connecting:
$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
+1
source to share