How to use both SELECT and INSERT query on single php prepared statement?

Question

I am trying to insert a record and get the id of that record using a prepared statement. For that, I write a query "INSERT INTO ". $natureFlight ."(typeFlight, dateTimeSubmit, emb, acType, reg, companyName, callSign, dateFlight) VALUES (?,?,?,?,?,?,?,?); SELECT LAST_INSERT_ID();" which works fine in MySQL console but doesn't work using a prepared statement. If I remove the SELECT LAST_INSERT_ID(); from the query then it can insert record using a prepared statement.

I search for the solution and I found this but does not work for me.

Here is a code

$natureFlight = $_POST['selectedNatureFlight'];
$typeFlight = $_POST['selectedTypeFlight'];
$dateTimeSubmit = $_POST['dateTime'];
$emb = $_POST['emb'];
$acType = $_POST['acType'];
$reg = $_POST['reg'];
$companyName = $_POST['companyName'];
$callSign = $_POST['callSign'];
$dateFlight = $_POST['dateFlight'];

$insertRecord = "INSERT INTO ". $natureFlight ."(`typeFlight`, `dateTimeSubmit`, `emb`, `acType`, `reg`, `companyName`, `callSign`, `dateFlight`) VALUES (?,?,?,?,?,?,?,?); SELECT LAST_INSERT_ID();";
$stmt = $conn->prepare($insertRecord);
$stmt->bind_param("ssssssss", $typeFlight, $dateTimeSubmit, $emb, $acType, $reg, $companyName, $callSign, $dateFlight);
if($stmt->execute()){
    $stmt->bind_result($id);
    while($stmt->fetch()) {
         echo $id;
        }           
} else{
    $res['error'] = true;
    $res['message'] = $stmt->error;
}

After I run this I get an error

Fatal error: Uncaught Error: Call to a member function bind_param() on bool in
D:\xampp\htdocs\test\proc\flightPDF\src\body\user\records.php:40 Stack trace: #0 {main} thrown in 
D:\xampp\htdocs\test\proc\flightPDF\src\body\user\records.php on line 40

Line 40 contain $stmt->bind_param("ssssssss", $typeFlight, $dateTimeSubmit, $emb, $acType, $reg, $companyName, $callSign, $dateFlight);

If you insert record in PHP, you can get last insert id with $conn->lastInsertId() atfer execute. Don't need query last id query string. Please remove it in your query string. — Au Nguyen
– Au Nguyen, Commented Sep 22, 2019 at 4:11
after using $conn->lastInsertId(), i am getting error Fatal error: Uncaught Error: Call to undefined method mysqli::lastInsertId() — Nawaraj
– Nawaraj, Commented Sep 22, 2019 at 4:24
Don’t know about how reliable last insert id is in MySQL, but it is buggy in other databases. Best solution I’ve found is to select by the same thing you inserted, ordered by id desc. It’s not foolproof; you have to consider what are the chances another insert with the same information could occur in between your insert and select... (yeah, I’m not too worried) — Tim Morton
– Tim Morton, Commented Sep 22, 2019 at 4:34
Please provide script code for create your $conn. Do you use mysqli or PDO to connect mysql? PDO: $conn->lastInsertId() , mysqli: $conn->insert_id — Au Nguyen
– Au Nguyen, Commented Sep 22, 2019 at 4:34

Nick · Accepted Answer · 2019-09-22 04:41:26Z

1

You can't prepare multiple statements. That is why your prepare is failing and $stmt is a boolean (false). To fix it, remove the SELECT LAST_INSERT_ID() from your INSERT query and change the code to this, using insert_id to get the last insert id:

$insertRecord = "INSERT INTO ". $natureFlight ."(`typeFlight`, `dateTimeSubmit`, `emb`, `acType`, `reg`, `companyName`, `callSign`, `dateFlight`) VALUES (?,?,?,?,?,?,?,?)";
$stmt = $conn->prepare($insertRecord);
$stmt->bind_param("ssssssss", $typeFlight, $dateTimeSubmit, $emb, $acType, $reg, $companyName, $callSign, $dateFlight);
if (!$stmt) {
    $res['error'] = true;
    $res['message'] = $conn->error;
}
elseif ($stmt->execute()) {
    echo $conn->insert_id;
} 
else {
    $res['error'] = true;
    $res['message'] = $stmt->error;
}

answered Sep 22, 2019 at 4:41

Nick

147k23 gold badges67 silver badges106 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Meet Soni · Accepted Answer · 2019-09-22 04:56:54Z

0

Use lastInsertId Instead Of SELECT Statement

$last_id = $conn->lastInsertId;

Hope this will help you..!!

edited Sep 22, 2019 at 4:56

answered Sep 22, 2019 at 4:21

Meet Soni

1307 bronze badges

5 Comments

Nawaraj Over a year ago

I am getting error Fatal error: Uncaught Error: Call to undefined method mysqli::lastInsertId()

Nawaraj Over a year ago

@Meet_Soni I am using PHP prepared statement

Meet Soni Over a year ago

@Nawaraj if you getting this kind of error then use $last_id = mysqli_insert_id($conn); and let me know about same. May be it works.

Nawaraj Over a year ago

it does not block the execution but also does not catch the last id

Meet Soni Over a year ago

Remove this SELECT LAST_INSERT_ID() and put this if($stmt->execute()){ echo $conn->insert_id; } and check same.

Collectives™ on Stack Overflow

How to use both SELECT and INSERT query on single php prepared statement?

2 Answers 2

Comments

5 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

5 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related