pgill pgill - 17 days ago 6
MySQL Question

How to retrive last inserted auto generated id for the same table on different php-html page

I am creating a sign up form using HTML5/CSS3/PHP/MySql that will have following columns:

Firstname, Lastname, Email, Password ( to be filled on Web form-1)

Username, country, Age, SecurityQues, SecurityAns, Mobile (to be filled on web-form 2).

there is only one table named user_record for all the fields and it has a user_id that is auto generated and auto increment when the web form 1 is executed, and it is also the primary key for the table.

I am using insert query for web form 1 and update query for web form 2. but the second form's data cannot be submitted until it has UserId generated in the web form 1.

I tried to retrieve it using lastInsertId(); but it is not working as it returns the id only when the insertion is done on the same page so it works on form 1 but not on form 2.

there is only one table being used on two different web pages. on one insertion is being done and user_id is being generated.
On the another update query will be performed to update the remaining fields of the row created in the first form. for that form 2 needs primary key which is not available.

Can any one help me and tell me how to retrieve last UserId on the form 2 so that the data can be filled on the same row that user just created on form 1.

following is the code snippet for both form 1 and form 2.

Form 1 PHP Snippet:

<?php

$servername = "localhost";
$username = "root";
$password = "";
$db = "myDB";

$conn = new PDO("mysql:host=$servername;dbname=$db", $username, $password);


$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

if(isset($_POST['sign_up-btn'])){
session_start();

$first_name = ($_POST['first_name']);
$last_name = ($_POST['last_name']);
$email_id = ($_POST['email_id']);
$password = ($_POST['password']);
$password2 = ($_POST['password2']);

if($password == $password2){
$password = md5($password);

$sql = "INSERT INTO user_record(first_name, last_name, email, password) VALUES('$first_name', '$last_name', '$email_id', '$password')";
$conn->exec($sql);
$_SESSION['message'] = "You are almost done";
$_SESSION['first_name'] = $first_name;
header("location: pro.php");

}else{
$_SESSION['message'] = "Password do not match";
}
}
$conn = null;
?>


PHP Script of form 2 is:

<?php

$servername = "localhost";
$username = "root";
$password = "";
$db = "myDB";

$conn = new PDO("mysql:host=$servername;dbname=$db", $username, $password);


$conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

if(isset($_POST['sub_btn'])){
session_start();

$user_name = ($_POST['user_name']);
$sel_cont = ($_POST['sel_cont']);
$age = ($_POST['age']);
$sec_que = ($_POST['sec_que']);
$sec_ans = ($_POST['sec_ans']);
$mob_num = ($_POST['mob_num']);
$con_code = ($_POST['con_code']);

$id = $conn->lastInsertId();


$sql = "UPDATE user_record SET user_name = '$user_name', country = '$sel_cont',
age = '$age', security_ques = '$sec_que', security_ans = '$sec_ans',
mobile_num = '$mob_num', c_code = '$con_code' WHERE user_id = '$id' ";

$conn->exec($sql);
$_SESSION['message'] = "Welcome";
$_SESSION['user_name'] = $user_name;
header("location: home.php");

}

$conn = null;
?>


Plz tell me how to update the same row with the data of second form.

Answer

Obviously, the second form can't be submited if does not exists first record inserted.

I supose that second forms is already on the client browser.

You must to use session capabilities, on first form you must to set the lastInsertId() on a session variable.

Later, on form2 submit, the php can retrive the session variable before the UPDATE. If the session variable containing the lastInsertID is not set, means that the client has not submited first form.