CODEWITHSUNDEEP
phpmysqli
Mulgard
Mulgard

Reputation: 10619

PHP mysqli: Insert row or return id if unique conflict

I have a table with weight values:

TABLE_WEIGHT
    WEIGHT_ID bigint(20) NOT NULL PRIMARY KEY AUTO_INCREMENT,
    WEIGHT float NOT NULL UNIQUE

And i insert values:

$query = "INSERT INTO TABLE_WEIGHT (WEIGHT) VALUES(100)";

if ($stmt = $this->mysqli->prepare($query)) : 
    if (!stmt->execute()) : 
        // some error
    else :
        // alright
    endif;
endif;

The first time i do this i will have no problem. If i do that the second time the value 100 already exists. Is there a way to now just get the id of the conflicted row without the need of a new select statement?

Upvotes: 0

Views: 381

Answers (1)

VolkerK
VolkerK

Reputation: 96199

You can do that via INSERT ...ON DUPLICATE KEY UPDATE and the less-known form of LAST_INSERT_ID(expr)

<?php
$pdo = new PDO('mysql:host=localhost;dbname=test;charset=utf8', 'localonly', 'localonly', array(
    PDO::ATTR_EMULATE_PREPARES=>false,
    PDO::MYSQL_ATTR_DIRECT_QUERY=>false,
    PDO::ATTR_ERRMODE=>PDO::ERRMODE_EXCEPTION
));
setup($pdo);

$stmt = $pdo->prepare('
    INSERT INTO
        soFoo
        (WEIGHT)
    VALUES
        (?)
    ON DUPLICATE KEY UPDATE
        WEIGHT_ID=LAST_INSERT_ID(WEIGHT_ID),
        dummy=NOT dummy
');

foreach( array(100,200,300,100,400) as $w ) {
    $stmt->execute( array($w) );
    echo $w, ' -> ', $pdo->lastInsertId(), "\r\n";
}


foreach( $pdo->query('SELECT WEIGHT_ID,WEIGHT FROM soFoo', PDO::FETCH_ASSOC) as $row) {
    echo join(', ', $row), "\r\n";
}


function setup($pdo) {
    $pdo->exec('
        CREATE TEMPORARY TABLE soFoo (
            WEIGHT_ID bigint(20) NOT NULL AUTO_INCREMENT,
            WEIGHT float NOT NULL UNIQUE,
            dummy   int  NOT NULL DEFAULT 0,
            PRIMARY KEY(WEIGHT_ID),
            UNIQUE KEY(WEIGHT)
        )
    ');
}

prints

100 -> 1
200 -> 2
300 -> 3
100 -> 1
400 -> 5
1, 100
2, 200
3, 300
5, 400

(note the missing WEIGHT_ID=4)

Upvotes: 1

Related Questions