Ridge Robinson Ridge Robinson - 2 years ago 117
PHP Question

MySQL (not mysqli) Insert Last ID not working

I know there a many similar questions out there, but I have searched many times to be sure not to waste anyone's time, but I have been unable to find a solution. I'm hoping somebody may be able to spot something I am missing.

Our site is currently using MySQL (not MySQLi...I know it should be updated, but for now at least, this is what I have to work with).

I am updating two DB tables through a form that has been created. The first table, called 'customers', has some profile information and includes an auto_incremented customer id, and is updated by some of the information in my form. In the second table, 'customer_addresses', the rest of the information is updated towards, and one of the columns in the new table is called customer and has been indexed in the DB to link with the id column in 'customers' table. When I am submitting this form, I would like the 'customers_addresses' table to be updated with the new customer, but it is not showing upon submitting. Here is my code...(QUESTIONABLE PART IS THE mysql_insert_id() near the bottom)....

if($_POST) {
//Get variables from post array
$first_name = mysql_real_escape_string($_POST['first_name']);
$last_name = mysql_real_escape_string($_POST['last_name']);
$email = mysql_real_escape_string($_POST['email']);
$password = mysql_real_escape_string(md5($_POST['password']));
$address = mysql_real_escape_string($_POST['address']);
$address2 = mysql_real_escape_string($_POST['address2']);
$city = mysql_real_escape_string($_POST['city']);
$state = mysql_real_escape_string($_POST['state']);
$zipcode = mysql_real_escape_string($_POST['zipcode']);

//Create customer query
$query="INSERT INTO customers (first_name,last_name,email,password)
//Run query

//Create address query
$query="INSERT INTO customer_addresses (customer,address,address2,city,state,zipcode)
//Run query

header('Location: index.php');

I know I am connected to the DB correctly as the 'customers' table is updated correctly each time I submit my form AND I know the 'customer_addresses' table is connected correctly, because if I manually change in the PHP the "mysql_insert_id()", to be what the correct customer id is (say 35), then it works correctly, but somehow I need it to get this information from the first query to the customers table.

Thanks for any assistance.


I have also attempted to use the "LAST_INSERT_ID()" but to no avail as well.

Answer Source

mysql_insert_id() is a PHP function, not a MySQL function, and function calls aren't expanded inside strings.. The MySQL function is called LAST_INSERT_ID(), so it should be:

$query="INSERT INTO customer_addresses (customer,address,address2,city,state,zipcode)

Or you can call the PHP function and insert the result into the string.

$customer_id = mysql_insert_id();
$query="INSERT INTO customer_addresses (customer,address,address2,city,state,zipcode)
Recommended from our users: Dynamic Network Monitoring from WhatsUp Gold from IPSwitch. Free Download