Insertion Query into MySQL

Posted on 2004-11-15
Last Modified: 2009-12-16
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,

Question by:bcops
    LVL 48

    Accepted Solution

    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.

    LVL 3

    Expert Comment

    Hi bcops,

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

    LVL 10

    Expert Comment

    From the MYSQL documentation pages:
    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


    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Introduction Many web sites contain image galleries; a common design for these galleries includes a page with a collection of thumbnail images.  You can click on each of the thumbnail images to see the larger version of the image.  This is easily i…
    Generating table dynamically is the most common issue faced by php developers.... So it seems there is a need of an article that explains the basic concept of generating tables dynamically. It just requires a basic knowledge of html and little maths…
    Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
    The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…

    737 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

    Need Help in Real-Time?

    Connect with top rated Experts

    20 Experts available now in Live!

    Get 1:1 Help Now