Menu
Community /Pin to ProfileBookmark
@NogDogDec 30.2015 — #How adverse would you be to using PDO instead of MySQLi? I think it would be easier to deal with a variable number of arguments when binding parameters, allowing you to create the list of fields to be set on the fly and avoiding all those IF statements in the query itself. @NogDogDec 31.2015 — #Untested, but here's what I'm thinking of:
How to make this code better
I mean simplifying this PHP code that contains some MySQL script.
[code=php] $query = “UPDATE users SET street = IF(?=”, street, ?), city = IF(?=”, city, ?), province = IF(?=”, province, ?), postal = IF(?=”, postal, ?), country = IF(?=”, country, ?), cpnum = IF(?=”, cpnum, ?), phone2 = IF(?=”, phone2, ?), email = IF(?=”, email, ?), status = IF(?=”, status, ?), salt = IF(?=”, salt, ?), password = IF(?=”, password, ?), info_updated = NOW() WHERE username = ?”;
$stmt = $tubcConnection->prepare($query);
$stmt->bind_param(‘ssssssiisssssssssssssss’, $credentials[‘street’], $credentials[‘street’],
$credentials[‘city’], $credentials[‘city’],
$credentials[‘province’], $credentials[‘province’],
$credentials[‘postal’], $credentials[‘postal’],
$credentials[‘country’], $credentials[‘country’],
$credentials[‘phone’], $credentials[‘phone’],
$credentials[‘phone2’], $credentials[‘phone2′],
$credentials[’email’], $credentials[’email’],
$credentials[‘status’], $credentials[‘status’],
$secured_pass[0], $secured_pass[0], $secured_pass[1],
$secured_pass[1], $user
);
Sign in
to post a comment4 Comments(s) ↴
0
@NogDogDec 30.2015 — #0
@NogDogDec 31.2015 — #Untested, but here's what I'm thinking of:[code=php]
$fields = array(
':street' => $credentials['street'],
':city' => $credentials['city'],
':province' => $credentials['province'],
':postal' => $credentials['postal'],
':country' => $credentials['country'],
':cpnum' => $credentials['phone'],
':phone2' => $credentials['phone2'],
':email' => $credentials['email'],
':status' => $credentials['status'],
':salt' => $secured_pass[0],
':password' => $secured_pass[1]
);
foreach($fields as $key => $value) {
if(trim($value) === '') {
unset($fields[$key]);
}
}
if(count($fields) == 0) {
// nothing to do, so some sort of error here?
}
else {
$pdo = new PDO($dsn, $dbUser, $dbPass); // probably in a config file?
$sql = "UPDATE users SET";
foreach($fields as $key => $value) {
$sql .= "n".substr($key, 1)." = $key";
}
$sql .= "nWHERE username = :username";
$fields[':username'] = $user;
$stmt = $pdo->prepare($sql);
if($stmt == false) {
throw new Exception(print_r($pdo->errorInfo().PHP_EOL.$sql, 1));
}
if($stmt->execute($fields) == false) {
throw new Exception(print_r($stmt->errorInfo().PHP_EOL.$sql, 1));
}
}
[/code]