PDO使用可选值进行更新

Question

I am trying to build a query string for updating some user info. My problem is password is optional (they update something, but not their password), client is optional and phone is optional.

I started with something like this

<?php
$sql = "UPDATE users SET username = :username, name = :name, email = :email ";

if (isset($request->password)) $sql .= ',password = :password';

if (isset($request->client)) $sql .= ',client = :client';

if (isset($request->phone)) $sql .= ',phone = :phone'

$q = $db->prepare($sql);
try
{
    $q->execute(array(
        "username"      => $request->username,
        "password"      => (isset($request->password)) ? md5($request->password) : '', //--not working yet, I know
        "name"          => $request->name,
        "client"        => (isset($request->client)) ? $request->client : '',
        "email"         => $request->email,
        "phone"         => (isset($request->phone)) ? $request->phone : ''
    ));
.....
?>

I am thinking there has to be a better way to do this with PDO. Also, I still have not incorporate into the query yet the WHERE id = :idOfTheUserIamEditing

Answer 1

Your code looks fine to me, there isn't so many other ways to accomplish what you are trying to achieve. One thing I suggest is to build your array of parameters as you check the optional fields, this saves you from doing a "ton of if statement":

$sql = "UPDATE users SET username = :username, name = :name, email = :email ";

$params = array();
//mandatory
$params[':username'] = $request->username;
$params[':name'] = $request->name;
$params[':email'] = $request->email;

//optional
if (isset($request->password)){
    $sql .= ',password = :password';
    $params[':password'] = $request->password;
}

if (isset($request->client)){
    $sql .= ',client = :client';
    $params[':client'] = $request->client;
}

if (isset($request->phone)){
    $sql .= ',phone = :phone';
    $params[':phone'] = $request->phone;
} 


try
{
    $q = $db->prepare($sql);
    $q->execute(array($params);
    ...