Member Avatar for anthonyjpv

I want to be able to insert and update 2 different tables at the same time

heres the scenario:

if i insert a new record, the it will create a primary key for me as auto increment

then once that primary key is created,

I want to use that to update all foreign keys in another table

heres the code

  • 2 Contributors
  • 2 Replies
  • 108 Views
  • 8 Hours Discussion Span
  • Latest Post Latest Post by baig772

Dani AI

Generated

Common pattern: insert the parent row, get its auto‑increment id immediately, then stamp that id into child rows. The posted code shows that intent, but a few concrete issues will break it and make the UPDATE hit the wrong rows.

  • There is a stray semicolon after the while(...) that effectively nulls out the loop and leaves $row unset.
  • Selecting an order back with WHERE customer_id=... LIMIT 1 can return an older order. Relying on a SELECT makes the code racy under concurrent requests.
  • The UPDATE uses WHERE order_id = '$customer_id', which is almost certainly incorrect unless you intentionally stored the customer id in that column as a temporary marker.
  • No error checking and the code uses the old mysql_* API, which is deprecated.

As hinted, use the DB API's “last insert id” right after the INSERT, and update only rows that actually belong to the current cart/session. Wrap both statements in a transaction so either both succeed or both fail. Also use prepared statements to avoid injection and check affected rows or exceptions for errors.

Example (mysqli, minimal):

$mysqli->begin_transaction();

$stmt = $mysqli->prepare("INSERT INTO orders (customer_id, order_date, ship_to, ship_email, ship_phone, ship_address) VALUES (?, NOW(), ?, ?, ?, ?)");
$stmt->bind_param("issss", $customerId, $shipTo, $shipEmail, $shipPhone, $shipAddress);
$stmt->execute();

$orderId = $mysqli->insert_id;

$upd = $mysqli->prepare("UPDATE orderdetails SET order_id = ? WHERE cart_token = ?");
$upd->bind_param("is", $orderId, $cartToken);
$upd->execute();

$mysqli->commit();

Notes: use a stable cart identifier (session token or order_id = 0 placeholder) in orderdetails so the UPDATE targets only this user’s rows. Enable exception/error reporting during development (mysqli_report or PDO exceptions) to see failures. If staying on legacy code, at minimum remove the stray semicolon, fix the WHERE clause, and check mysql_query() return values.

Recommended Answers

All 2 Replies

Member Avatar for anthonyjpv 
<?php
require_once("includes/customer_session.php");
ob_start();
require_once("includes/connection.php");
confirm_logged_in();

$customer_id = $_SESSION['customer_id'];
$order_date = $_POST['order_date'];
$ship_to = mysql_prep($_POST['tb_ship_to']);
$ship_email = mysql_prep($_POST['tb_ship_email']);
$ship_phone = mysql_prep($_POST['tb_ship_phone']);
$ship_address = mysql_prep($_POST['tb_ship_address']);

$query = "INSERT INTO orders (customer_id, order_date, ship_to, ship_email, ship_phone, ship_address) 
		VALUES ('$customer_id', NOW(), '$ship_to', '$ship_email', '$ship_phone', '$ship_address')";
$result = mysql_query($query);


//unable to fetch the order_id from orders yet from here
$select_order_id = "SELECT order_id
					FROM orders
					WHERE customer_id='$customer_id'
					LIMIT 1";
$result_select = mysql_query($select_order_id);

while($row = mysql_fetch_array($result_select));
{
	$order_id = $row['order_id'];
}
				
$update_order_id_of_order_details = "UPDATE orderdetails
				SET order_id={$order_id}
				WHERE order_id='$customer_id'";
$result_update = mysql_query($update_order_id_of_order_details);
	
redirect_to("cart_view.php");
ob_flush();
?>
Member Avatar for baig772

try using

mysql_insert_id()

instead of where you are selecting from the table after inserting in it

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.