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, Sep 22 '19 at 04:11
after using $conn->lastInsertId(), i am getting error `Fatal error: Uncaught Error: Call to undefined method mysqli::lastInsertId()` — Nawaraj, Sep 22 '19 at 04: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, Sep 22 '19 at 04: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, Sep 22 '19 at 04:34
@TimMorton ok, this is also a good solution, I will try that — Nawaraj, Sep 22 '19 at 04:39

score 1 · Accepted Answer · answered Sep 22 '19 at 04:41

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;
}

Meet Soni · Answer 2 · 2019-09-22T04:56:54.330

0

Use lastInsertId Instead Of SELECT Statement

$last_id = $conn->lastInsertId;

Hope this will help you..!!

edited Sep 22 '19 at 04:56

answered Sep 22 '19 at 04:21

Meet Soni

130
7

I am getting error `Fatal error: Uncaught Error: Call to undefined method mysqli::lastInsertId()` – Nawaraj Sep 22 '19 at 04:27
@Meet_Soni I am using PHP prepared statement – Nawaraj Sep 22 '19 at 04:33
@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. – Meet Soni Sep 22 '19 at 04:39
it does not block the execution but also does not catch the last id – Nawaraj Sep 22 '19 at 04:42
Remove this SELECT LAST_INSERT_ID() and put this if($stmt->execute()){ echo $conn->insert_id; } and check same. – Meet Soni Sep 22 '19 at 04:49

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

2 Answers2