php - PDO 语句未设置值

Question

我正在尝试运行 PDO 更新语句，但没有任何字段正在更新。这是我的 PDO 查询。我已经完成并试图找到值被更改的位置，但发现没有分配任何值。当值被转义时，我发现了问题(你会看到我的评论放在那里)。我知道这可能是我忽略的东西，但我还没有弄清楚。

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 未定义。它应该是 $result:

$result = $statement->fetch(PDO::FETCH_ASSOC); // you declared `$result` not `$row`

为什么不完全使用准备好的语句:

$user_exists = "SELECT * FROM lu_agency WHERE agency_id =". $support_broker_id; // still directly injecting?

最后的样子:

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

旁注:总是在建立连接后添加:

$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);