CODEWITHSUNDEEP
phpmysqlpdoprepared-statement
user756659
user756659

Reputation: 3512

pdo update statement - save and update value in same statement?

I am storing the users last and current IP address / datetime in the database. On successful login of a user I am running these two prepared statements.

Out of curiosity is there any way to perform this in one statement?

Would something like lastIP = currIP, currIP = ?, dtLastLogin = dtCurrLogin, dtCurrLogin = UTC_TIMESTAMP() be allowed due to its order?

// update user's last ip and last login date in db
$stmt = $db->prepare("UPDATE accounts SET lastIP = currIP, dtLastLogin = dtCurrLogin WHERE account_id = ?");
$stmt->execute(array($account_id));

// update user's current ip and current login date in db
$stmt = $db->prepare("UPDATE accounts SET currIP = ?, dtCurrLogin = UTC_TIMESTAMP() WHERE account_id = ?");
$stmt->execute(array($_SERVER['REMOTE_ADDR'], $account_id));

Upvotes: 0

Views: 111

Answers (1)

xdazz
xdazz

Reputation: 160833

Yes, with mysql, you could do this in one sql.

$stmt = $db->prepare("UPDATE accounts SET lastIP = currIP, currIP = ?, dtLastLogin = dtCurrLogin , dtCurrLogin = UTC_TIMESTAMP() WHERE account_id = ?");
$stmt->execute(array($_SERVER['REMOTE_ADDR'], $account_id));

But note the order is important.

Upvotes: 1

Related Questions