PDO声明未设置值

Question

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");
    }

}

Answer 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);