Hello,

I have these two statements (Context: PHP PDO, Id = primary key):

INSERT INTO
foo
SET
Id = ?,
CountA = 1,
CountB = 0
ON DUPLICATE KEY UPDATE
CountA = CountA + 1

and

INSERT INTO
foo
SET
Id = ?,
CountA = 0,
CountB = 1
ON DUPLICATE KEY UPDATE
CountB = CountB + 1

I'd like to combine them into one using a second parameter. So far, I've
come up with this:

INSERT INTO
foo
SET
Id = :id,
CountA = IF( :type = 'CountA', 1, 0 ),
CountB = IF( :type = 'CountB', 1, 0 )
ON DUPLICATE KEY UPDATE
CountA = CountA + IF( :type = 'CountA', 1, 0 ),
CountB = CountB + IF( :type = 'CountB', 1, 0 )

But it doesn't look very elegant to me. Especially since this is just a
simplified version -- my real query has four counters. Is this a good
way to do this? Or is it better to have separate queries here, even
though they are very similar?

Greetings,
Thomas


--
Ce n'est pas parce qu'ils sont nombreux à avoir tort qu'ils ont raison!
(Coluche)

On Apr 3, 7:14*pm, Thomas Mlynarczyk <tho... (AT) mlynarczyk-webdesign (DOT) de>
wrote:
I'm notfamiliar with this syntax. Where is "IF( :type = ..."
documented?

Captain Paralytic schrieb:

It's used by PHP's PDO extension. ":type" is a placeholder (just like
"?") for prepared statements.

$pdo = new PDO( $connection, $user, $pass );
$statement =
$pdo->prepare(
"INSERT INTO foo SET Id = :id [...] IF ( :type [...]" );
$statement->execute( array( 'id' = 123, 'type' => 'CountB' ) );

Greetings,
Thomas

--
Ce n'est pas parce qu'ils sont nombreux à avoir tort qu'ils ont raison!
(Coluche)