[RESOLVED] Update based on Last insert ID

[Tezread]

OK- it's like this. Im trying to update a table based on the last id that was created in part 1 of a form.

here is part 1...

<?php
session_start();
require_once('common_functions.php'); 
$_SESSION['form1_data']=$_POST;

// build main query from form part 1
$sql = "INSERT INTO qualityuser SET qualityuser_id =  NULL ";
foreach ($_SESSION['form1_data'] as $col =>$val ){
	if($col <> 'submit'){
	$sql .=", $col = '$val' ";
	}

}
mysql_query($sql, $connection) or die(mysql_error()); 

//get last insert id if you want to put it in other tables as a reference
$sql ="select LAST_INSERT_ID() as id from qualityuser";
$result = mysql_query($sql, $connection) or die(mysql_error()); 
$row=mysql_fetch_array($result);
$id = $row['id'];

?>

this works fine

now in part 2 of the form im trying to update the record that was inserted in part 1 but im having no joy:

<?php
session_start();
require_once('common_functions.php'); 
$_SESSION['form2_data']=$_POST;

// build main query from form part 2 based on last inserted id from part 1
	$getlastid ="select LAST_INSERT_ID() as id from qualityuser";
	$result = mysql_query($getlastid, $connection) or die(mysql_error());

$sql = "UPDATE qualityuser WHERE qualityuser_id = '$result' ";
foreach ($_SESSION['form2_data'] as $col =>$val ){
	if($col <> 'submit'){
	$sql .=", $col = '$val' ";
	}

}
mysql_query($sql, $connection) or die(mysql_error()); 

echo $result;
?>

the error message reads You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near 'WHERE qualityuser_id = 'Resource id #5'' at line 1

can anyone help me get round this issue- thx in advance

[ClarkF1]

Well you haven't used mysql_fetch_array anywhere to actually retrieve the data from the result

The answer's in the script for the first part.

[Tezread]

Thankyou for your help. I could see that I was missing:

//get last insert id if you want to put it in other tables as a reference 
$sql ="select LAST_INSERT_ID() as id from qualityuser"; 
$result = mysql_query($sql, $connection) or die(mysql_error()); 
$row=mysql_fetch_array($result); 
$id = $row['id']; 

But I got really stuck trying to do the update part the same way as i did the INSERT for each....

I have simplified the approach and it works but it is not very efficient.....

//get last insert id so we can update qualityuser based on the last inserted id
 if (isset($_POST['submit'])) { 
include "connect.php";
$sql = mysql_query("SELECT MAX(qualityuser_id) AS id FROM qualityuser"); 
$id = mysql_result($sql, 0, 'id'); 

echo "Most recent question_ID is $id!"; 
$answer[1] = $_POST['time']; 
$answer[2] = $_POST['demand']; 
$answer[3] = $_POST['external_support']; 
$answer[4] = $_POST['training']; 
$answer[5] = $_POST['depth']; 
$answer[6] = $_POST['partuse']; 
$answer[7] = $_POST['cost']; 
$answer[8] = $_POST['orgwhole']; 
$answer[9] = $_POST['external_validated']; 

// build main query from form part 2 ///////
$sql = "UPDATE qualityuser SET time = '$answer[1]',
demand = '$demand',
external_support = '$answer[2]',
training = '$answer[3]',
complexity = '$answer[4]',
depth = '$answer[5]',
partuse = '$answer[6]',
cost = '$answer[7]',
orgwhole = '$answer[8]',
external_validated = '$answer[9]' 

WHERE qualityuser_id = '$id'"; 
$result = mysql_query($sql) or exit(mysql_error()); 

}