CODEWITHSUNDEEP
phpmysqlpdo
happymacarts
happymacarts

Reputation: 2604

PDO with bound parameters sql query If statement in update

I am attempting to create a 'trigger' not in the sql sense but I want to update the date_added field when the status field is set to 100

$sql='UPDATE table
SET status=:status,
    date_added=[PSEUDO CODE :status=100 ? now() : null;]
WHERE id=:id';

    $stmt=$conn->prepare($sql);
    $stmt->bindParam(':id', $id, PDO::PARAM_STR);
    $stmt->bindParam(':status', $status, PDO::PARAM_STR);
    $stmt->bindParam(':sign_id', $sign_id, PDO::PARAM_STR);
    $stmt->execute();
  1. Would it be better to attempt this in the sql query(unsure how to perform this) or on the php page (think I could stumble through that one) prior to issuing the query?
  2. Are there any performance gains one way or the other?

Thanks in advance for any help

Upvotes: 0

Views: 330

Answers (2)

Bill Karwin
Bill Karwin

Reputation: 562631

This should work:

$sql='UPDATE table
SET status=:status,
    date_added=IF(:status=100, NOW(), NULL)
WHERE id=:id';
$stmt=$conn->prepare($sql);
$stmt->bindParam(':id', $id);
$stmt->bindParam(':status', $status);
$stmt->execute();

But using the same parameter name twice in one statement only works if you configure PDO to use emulated prepare. If you use native prepare, then you should make distinct parameter names even for the same value:

$sql='UPDATE table
SET status=:status,
    date_added=IF(:status_again=100, NOW(), NULL)
WHERE id=:id';
$stmt=$conn->prepare($sql);
$stmt->bindParam(':id', $id);
$stmt->bindParam(':status', $status);
$stmt->bindParam(':status_again', $status);
$stmt->execute();

Or else it'd be simpler to use positional parameters. You can also skip the bindParam() if you just pass an array of values to execute(). There's an example of the latter two changes together:

$sql='UPDATE table
SET status=?,
    date_added=IF(?=100, NOW(), NULL)
WHERE id=?';
$stmt=$conn->prepare($sql);
$stmt->execute([$status, $status, $id]);

Upvotes: 2

Explosion Pills
Explosion Pills

Reputation: 191779

date_added = :date

$date = $status == 100 ? date('Y-m-d H:i:s') : null;
$stmt->bindParam(":date", $date);

You can do this comparison in MySQL as well using IF. I don't think that one is particularly faster than the other, but it makes more sense to me to use PHP for the comparison.

Upvotes: 3

Related Questions