[Okta Webinar] Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 234
  • Last Modified:

Insertion Query into MySQL

Hi,
Wonder if anyone can help.

I have a table I'd like to insert a row into using PHP. This table will be being used by a CMS, so at any point any no. of users could be inserting rows into it.

Once the code has done the insert, I'd like to get the recordID of the new row.
So I could: do a select on the table immediately following the insert to get the new recordID.

I can't 100% rely upon any auto-increment feature since someone else could have inserted a new row in between my inserting, then finding the new recordID using auto-increment.

Is there anything very handy in PHP like:

newRecordID = php_insert_row (insert parameters ....)

that returns the new record ID for me without relying upon auto incremenet and one that would mean I wouldn't have to write a cumbersome select query immediately post the insert?

This is PHP4 running wtih MySQL 3.x.

Thanks for any help,
Ben.











0
bcops
Asked:
bcops
1 Solution
 
hernst42Commented:
If that id-column has auto_increment enabled you can use
mysql_insert_id() to get that id. There is no need to do a select afterwards.

mysql_insert_id will give you the id that the current connection has inserted, you won't get the id of the insert from someone other.

See http://de3.php.net/manual/en/function.mysql-insert-id.php
0
 
nesnemisCommented:
Hi bcops,

to get the id use:
    $id = mysql_insert_id($sqlInsertStatement);

nesnemis
0
 
eeBlueShadowCommented:
From the MYSQL documentation pages:
http://dev.mysql.com/doc/mysql/en/Getting_unique_ID.html
--------
When a new AUTO_INCREMENT value has been generated, you can also obtain it by executing a SELECT LAST_INSERT_ID() statement mysql_query() and retrieving the value from the result set returned by the statement.

For LAST_INSERT_ID(), the most recently generated ID is maintained in the server on a per-connection basis. It will not be changed by another client. It will not even be changed if you update another AUTO_INCREMENT column with a non-magic value (that is, a value that is not NULL and not 0).
--------

So, follow your INSERT SQL statement with a "SELECT LAST_INSERT_ID()" and you'll be fine

_Blue
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now