retreiving the latest record

[learnvb1]

Hi
i m using php and mysql..I am inserting the data into the table and straight after that i want to grab the primary key for use. how do i do that




$SQL = "INSERT INTO table (name,addressType,suburb,state,postcode,contactName,number_of_standart_stb,number_of_IQ_stb) VALUES ('".$_POST['name']."','".$_POST['addressType']."','".$_POST['Suburb']."','".$_POST['State']."','".$_POST['Postcode']."','".$_POST['contactName']."','".$_POST['standardSTB']."','".$_POST['iqstb']."')";



now after inserting this record i want to use the primary key( ID) ...how do i retreive that straight away?

[Nightwalker83]

You posted this in the code bank! Please read the forum link carefully before you click it next time.

Edit:

Which field are you using as a primary key? If it is name then the field would need to be as unique, if it is ID you need to first insert the id into the data (usually the first piece of data inserted into the database).

[RobDog888]

Thread Moved

[I_Love_My_Vans]

You need to use the mysql_insert_id (http://php.net/manual/en/function.mysql-insert-id.php) function.

"Retrieves the ID generated for an AUTO_INCREMENT column by the previous query (usually INSERT)."

[kows]

make sure you're not just inserting into your database before sanitising your user's input; you can use mysql_real_escape_string() (http://ca2.php.net/manual/en/function.mysql-real-escape-string.php) to do this.

Nightwalker: when you're inserting into a database, you don't need to provide the primary key as a field if auto_increment is enabled. the value inserted would have to be null for auto_increment to work, so his query is fine. he wanted to retrieve the ID of the record he just inserted -- ILMV's solution.

[learnvb1]

Thanks guys....i think i have got the solutions ...mysql_insert_id() function should work.....i will try it tomorrow....

Just by curiosity....say for an example ...i added a record and just after than another user added record...will my query give me my ID or the latest user's ID?

i hope you understood my question...like what happens when the record insertion takes place in parts of nanosecond.....will it still work perfectly and give me the correct ID?

[Nightwalker83]

You would be preforming the query of the second user so no. However, what you might be able do is store your search for use later on.

[learnvb1]

You would be preforming the query of the second user so no. However, what you might be do is store your search for use later on.

i only have to use that ID only once...but my confusion is that by mistake (taking the last inserted data in the database ) can the query return the ID which was inserted by another user

example

id =1 is inserted by me and at the same time id =2 was inserted by another user

when i use the mysql_insert_id function ...will it return ID =1 or ID =2 ?

[Nightwalker83]

Well it says on the page provided by ILMV that it returns the result for the previous query, so if you had user 1 enter data then user 2 enter data followed by the ID retrieve function the result would be the id for user 2. However, if you put the ID retrieve function after each insert you get both both user 1 and 2.

[kows]

... nightwalker, what you're saying is completely incorrect. it would be best that if you don't know how something works, you don't try to explain it.

mysql_insert_id() works for the last query given by the current client connection, not the last query sent to the database in general.

The last ID that was generated is maintained in the server on a per-connection basis. This means the value the function returns to a given client is the most recent AUTO_INCREMENT value generated by that client. The value cannot be affected by other clients, even if they generate AUTO_INCREMENT values of their own. This behavior ensures that you can retrieve your own ID without concern for the activity of other clients, and without the need for locks or transactions.

[penagate]

Well it says on the page provided by ILMV that it returns the result for the previous query, so if you had user 1 enter data then user 2 enter data followed by the ID retrieve function the result would be the id for user 2. However, if you put the ID retrieve function after each insert you get both both user 1 and 2.
Try spending two seconds reading the documentation before giving people advice which is totally wrong and misleading.

[bharanidharanit]

Hi,
I had also came across last_insert_id, and what's the difference between these two? so,
Will mysql_insert_id retrieves the last inserted id as per connections???

[bharanidharanit]

Hi,
If mysql_insert_id works based on client connections means, what will really happen for computers connected in the local network?

[kows]

client connection does not mean a physical internet connection. it means a given connection from the website to the database. every new browser window you have opened on one page will theoretically have a new connection to the database.

and, what is the difference between what two? this thread is about the function mysql_insert_id() and nothing else -- and that function is pretty well explained in this thread and also in the PHP documentation.

you're posting a lot of questions lately that are basically answered by reading through the thread you're posting in. and the threads are all a few weeks old.

[bharanidharanit]

I simply asked about this last_insert_id

[bharanidharanit]

you're posting a lot of questions lately that are basically answered by reading through the thread you're posting in. and the threads are all a few weeks old.
I am thinking of starting a new thread for this last_insert_id and so i continues in this thread.

[penagate]

I am thinking of starting a new thread

Please do so.