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

<?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();
?>

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.