asked on

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

ASKER CERTIFIED SOLUTION

Ray Paseur

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

Dalexan

ASKER

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