Gorgon_Union Gorgon_Union - 3 months ago 7
MySQL Question

PHP - getting last statements PK ID to use in second function Insert statement

I have two tables,

orders
and
customers
that are to have data inserted from one form within the same action. The
orders
table has a primary key =
ordersID
which also needs to be added into the
customers
table.

DB Relationship Diagram
Relationship Diagram

I can upload the data to the
orders
table without a problem, but the second query function to the customers table does nothing. I'm pretty sure it's due to the foreign key constraint I've set. I've done some research and realize I need to get the foreign key id, possibly using
$mysqli->insert_id
or
PDO::lastInsertId
, but I'm lost as to how to use it with my current functions.

index.php

$product = $_POST['product'];
$fName = $_POST['fName'];
$lName = $_POST['lName'];
$address = $_POST['address'];
$address2 = $_POST['address2'];
$city = $_POST['city'];
$state = $_POST['state'];
$zip = $_POST['zip'];
$country = $_POST['country'];
$phoneNumber = $_POST['phoneNumber'];
$email = $_POST['email'];
/* Functions */
order_Data ($db, $product, $fName, $lName, $email);
// Need to have orderID from ^ table used in order_custData sql statement
order_custData($db, $orderID, $fName, $lName, $address, $address2, $city, $state, $zip, $country, $phoneNumber, $email);


functions.php

<?php

/** Order Data Function
*/
function order_Data($db, $product, $fName, $lName, $email) {
try {
$sql = "INSERT INTO orders SET product = :product, fName = :fName, lName = :lName, email = :email";
$ps = $db->prepare($sql);
$ps->bindValue(':product', $product);
$ps->bindValue(':fName', $fName);
$ps->bindValue(':lName', $lName);
$ps->bindValue(':email', $email);
$ps->execute();
return $orderID = $db->lastInsertId();
} catch (PDOException $e) {
die("Sorry, There was a problem order table.");
}
}

/** Customer Purchase Information Function
* @param $orderID -- I need to insert the orderID from orders table?
*/
function order_custData($db, $orderID, $fName, $lName, $address, $address2, $city, $state, $zip, $country, $phoneNumber, $email) {
try {
$sql = "INSERT INTO customers SET orderID = :orderID, fName = :fName, lName = :lName, address = :address, address2 = :address2,city = :city, state = :state, zip = :zip, country = :country, phoneNumber = :phoneNumber, email = :email";
$ps = $db->prepare($sql);
$ps->bindValue(':orderID', $orderID); // Foreign key from orders table
$ps->bindValue(':fName', $fName);
$ps->bindValue(':lName', $lName);
$ps->bindValue(':address', $address);
$ps->bindValue(':address2', $address2);
$ps->bindValue(':city', $city);
$ps->bindValue(':state', $state);
$ps->bindValue(':zip', $zip);
$ps->bindValue(':country', $country);
$ps->bindValue(':phoneNumber', $phoneNumber);
$ps->bindValue(':email', $email);
return $ps->execute();
} catch (PDOException $e) {
die("Sorry, There was a problem with the customer table!");
}
}
?>


Now the
$orderID
and
:orderID
IN THE
order_custData
function are just there for me as a visual representation to figure out the problem. I wasn't try to execute the sql statement with it originally. However, anything I've tried seems to throw errors
underfined variable
or fatal calls to the prepare function of the first function.

Thank you for your time.

Answer

The utility function order_Data() already returns the ID:

…
return $orderID = $db->lastInsertId();

(Rewrite to return $db->lastInsertId();, though.)

Simply carry the return value by assigning to a variable:

$orderID = order_Data ($db, $product, $fName, $lName, $email);
order_custData($db, $orderID, $fN …);