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?
 
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
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.