Inserting record and returning id of just inserted

What is the best practice for inserting a record from a php form submit and returning the id of the record inserted back to the form for use in other processes. The below code is what I'm currently using, just trying to determine whats the best practice. Another method would be to code the insert within a php begin-commit transaction block in the web form rather than use this stored proc.

PROCEDURE OrderCRM.`Sp_addneworderheader`(
    INOUT v_orderid INT ,
    v_cust_id INT,
    v_bill_addr_id INT,
    v_ship_addr_id INT,)
BEGIN
   START TRANSACTION;
   INSERT INTO order_header(
      cust_id,
      ord_bill_adr_id,
      ord_ship_adr_id)
 VALUES(
      v_cust_id,
      v_bill_addr_id,
      v_ship_addr_id);

SET v_orderid = LAST_INSERT_ID();

select IFNULL(ord_id,0) INTO v_orderid 
FROM order_header 
WHERE ord_id=v_orderid;
   
COMMIT;
END;

Open in new window

DalexanAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Ray PaseurCommented:
Please have a look at this article, and make a search of the page for "INSERTED" (case-sensitive).  It will take you to a statement that immediately follow the line of code to retrieve the insert-id.  Several examples are present in the article.
http://www.experts-exchange.com/Programming/Languages/Scripting/PHP/A_11177-PHP-MySQL-Deprecated-as-of-PHP-5-5-0.html

HTH, and please post back if you still have questions, ~Ray
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DalexanAuthor Commented:
Thanks for the light reading ;)
This is what I was looking for
// LOADING OUR DATA INTO THE TABLE
foreach ($test_names_arrays as $person)
{
    // ESCAPE THE DATA FOR SAFE USE IN A QUERY
    $safe_fn  = $mysqli->real_escape_string($person['fname']);
    $safe_ln  = $mysqli->real_escape_string($person['lname']);

    // CONSTRUCT THE QUERY USING THE ESCAPED VARIABLES
    $sql = "INSERT INTO my_table ( fname, lname ) VALUES ( '$safe_fn', '$safe_ln' )";

    // RUN THE QUERY TO INSERT THE ROW
    $res = $mysqli->query($sql);

    // IF mysqli::query() RETURNS FALSE, LOG AND SHOW THE ERROR
    if (!$res)
    {
        $err
        = "QUERY FAIL: "
        . $sql
        . ' ERRNO: '
        . $mysqli->errno
        . ' ERROR: '
        . $mysqli->error
        ;
        trigger_error($err, E_USER_ERROR);
    }

    // GET THE AUTO_INCREMENT ID OF THE RECORD JUST INSERTED
    $id  = $mysqli->insert_id;
    echo "MySQLI INSERTED A ROW CONTAINING <b>$safe_fn $safe_ln</b> WITH AUTO_INCREMENT ID = $id" . PHP_EOL;
}

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.