Solved

php,mysqli, and last_insert_id()

Posted on 2004-08-21
4
2,082 Views
Last Modified: 2008-01-09
using PHP 5, mysqli extension, and mysql 4.1.3:  according to the mysql documentation, last_insert_id returns the last autoincremented value from an insert OR update.  ...but, how would you get an update query to autoincrement?
When I set an autoincrement field to a number I created, insert_id() returns 0.

Q: I need to get an updated record's primary key without having to submit a subsequent SELECT statement.  Allowing two consecutive statements (UPDATE, then SELECT) might allow another UPDATE to occur (in a multiuser environment) before the Select statement could be called, returning the second record's primary key value instead.  ...and 'no', I don't know the PK value I just updated, because my statement was 'UPDATE PK SET PK = PK+1 WHERE 1 LIMIT 1'
Reason: I have a single field , single value  table I use to maintain a counter.
0
Comment
Question by:stevolicious
  • 2
4 Comments
 
LVL 3

Expert Comment

by:pat5star
ID: 11864627
MySQL guarantees that it will return the correct PK value of an insert even if there are simultaneous inserts occurring. The only thing you have to be sure of is that you use the same connection to the database for both the insert and then the SELECT LAST_INSERT_ID(). So if you obtain a connection, insert some data and then query for the last insert id before closing that connection you will always get the correct PK.
0
 

Author Comment

by:stevolicious
ID: 11865511
...but what if the last statement was an update that sets the PK field to PK+1 (not autoincremented, not an insert).  Is there an update statement that would autoincrement the PK?...and does last_insert_id() truly work with updates as mentioned in the documentation?
0
 
LVL 3

Accepted Solution

by:
pat5star earned 25 total points
ID: 11865554
That I don't know. I never change the PK field of any of my tables. Without knowing what (or why) you're doing it this way it's hard to offer advice. I would think that adding another field to your table that you use as the id field might be easier to work with. That way you can use the PK as the identifier of which row you are working on while you manipulate the value of your id field.
0
 
LVL 15

Assisted Solution

by:JakobA
JakobA earned 225 total points
ID: 11928425
potentially an UPDATE command can affect any number of rows, it does not make sense to have a last_affected_id() function. (and no, last_insert_id() do not give a meaningfull value after UPDATE, I think the reference is to the ON DUPLICATE KEY option that can turn an insert into an update if nessesarry)

so do a select first finding the id of the row to be updated, then do the update.

regards JakobA
0

Featured Post

Migrating Your Company's PCs

To keep pace with competitors, businesses must keep employees productive, and that means providing them with the latest technology. This document provides the tips and tricks you need to help you migrate an outdated PC fleet to new desktops, laptops, and tablets.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In this installment of my SQL tidbits, I will be looking at parsing Extensible Markup Language (XML) directly passed as string parameters to MySQL 5.1.5 or higher. These would be instances where LOAD_FILE (http://dev.mysql.com/doc/refm…
More Fun with XML and MySQL – Parsing Delimited String with a Single SQL Statement Are you ready for another of my SQL tidbits?  Hopefully so, as in this adventure, I will be covering a topic that comes up a lot which is parsing a comma (or other…
Nobody understands Phishing better than an anti-spam company. That’s why we are providing Phishing Awareness Training to our customers. According to a report by Verizon, only 3% of targeted users report malicious emails to management. With compan…
With Secure Portal Encryption, the recipient is sent a link to their email address directing them to the email laundry delivery page. From there, the recipient will be required to enter a user name and password to enter the page. Once the recipient …

785 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question