CODEWITHSUNDEEP
phpmysql
Vitj
Vitj

Reputation: 53

MySQL INSERT --- get the ID (auto_increment) IF one value is UNIQUE

Is there a better or faster way to return the ID?

The column customer is unique 

$inserted_id = null;    

if( !$mysqli->query("INSERT INTO users (id,customer) VALUES(null,'foo')" ){  

    // Is it possible to avoid this 2nd query?
    $result = $mysqli->query("SELECT id FROM users WHERE customer='foo'");

    $inserted_id = $result->fetch_assoc()['id'];

} else {

    $inserted_id = $mysqli->insert_id;

}

Upvotes: 3

Views: 363

Answers (2)

Samir Selia
Samir Selia

Reputation: 7065

You can modify the insert query to UPDATE auto incremented column when a duplicate record is attempted to INSERT.

INSERT INTO users (id,customer)
VALUES (null,'foo')
ON DUPLICATE KEY UPDATE 
    id = LAST_INSERT_ID(id)

id = LAST_INSERT_ID(id) will return the value of the AUTOINCREMENT column for the last INSERT and set the value for mysqli_insert_id. This will make last insert id available during all inserts.

Have modified your code:

$inserted_id = null;    

if($mysqli->query("INSERT INTO users (id,customer) VALUES(null,'foo') ON DUPLICATE KEY UPDATE id = LAST_INSERT_ID(id)" )
{
    $inserted_id = $mysqli->insert_id; // Will return last insert ID in case of successful inserts as well as failed inserts due to duplicate key
}

Upvotes: 1

Andrew Coder
Andrew Coder

Reputation: 1058

Use $last_id = $mysqli->insert_id(); after your insert. That get's the last generated auto increment. Your code as it stands will not be accurate. Updated for Object Oriented perspective.

Upvotes: 4

Related Questions