CODEWITHSUNDEEP
phpmysqldatabaseprimary-key
user447172
user447172

Reputation: 85

how to prevent from getting wrong ID

database= mysql language=php

I am coding program to work like this

PC1 =computer1

step

1.PC1 insert data ,new ID from Auto-increment.

2.PC1 select last ID

everything work fine but..

The problem when your code is used by many computers at the same mili-sec. For example

  1. PC1insert data,Auto-increment new ID

2.PC2 insert data ,Auto-increment new ID

3.PC1 select last ID <-------Wrong

4PC2 select last ID

How to config database or modify php code to prevent this , thankyou.

Upvotes: 1

Views: 331

Answers (4)

Mark Byers
Mark Byers

Reputation: 838716

You should use mysql_insert_id (or the equivalent call for the API you are using) to get the ID of the last inserted row.

I imagine that now you are doing this:

SELECT MAX(id) FROM yourtable

This won't work unless you use transactions. But using the function mysql_insert_id is the better way.

In the documenation there is also a caution and some notes that you should read:

Caution: mysql_insert_id() will convert the return type of the native MySQL C API function mysql_insert_id() to a type of long (named int in PHP). If your AUTO_INCREMENT column has a column type of BIGINT (64 bits) the conversion may result in an incorrect value. Instead, use the internal MySQL SQL function LAST_INSERT_ID() in an SQL query.

Note: Because mysql_insert_id() acts on the last performed query, be sure to call mysql_insert_id() immediately after the query that generates the value.

Note: The value of the MySQL SQL function LAST_INSERT_ID() always contains the most recently generated AUTO_INCREMENT value, and is not reset between queries.

Upvotes: 3

Pekka
Pekka

Reputation: 449613

mySQL has the LAST_INSERT_ID() function for that.

The PHP equivalent (if you use the classical mysql functions) is mysql_insert_id() with the exception that this function should be called immediately after the INSERT query because it acts on the last query made.

These functions work on a per-connection basis, it will not be influenced by records inserted by other clients.

Upvotes: 2

BoltClock
BoltClock

Reputation: 724182

MySQL will return the correct last-inserted ID right after any INSERT statement per client (computer) connection, so you won't have to worry about that; the server won't mix them up. From the documentation:

Each client will receive the last inserted ID for the last statement that client executed.

As long as you call mysql_insert_id() in your PHP code to retrieve the insert ID, there's nothing to worry about.

Upvotes: 2

3urdoch
3urdoch

Reputation: 7332

in mysql if you call

SELECT LAST_INSERT_ID();

you will get the last auto generated id fro the current connection, not for the whole server. So if another record is created in another connection it wont effect the result returned by LAST_INSERT_ID().

Upvotes: 2

Related Questions