MySQLi insert_id returns 0

Question

I've been trying to fix this now for the past 3 hours and I'm having no luck - my tables all have auto increment fields and the row is being inserted correctly, and the connection isn't being closed because the other rows afterwards are also inserted, but obtaining the insert ID of any of them results in 0

Here's the code I'm working with:

<?php require('../includes/quotesleads-database.php'); ?>
<?php
$stmt = $conn->prepare("INSERT INTO `customers` (`contactEmailAddress`, `contactPhoneNumber`, `fullName`, `createdAt`, `createdBy`) VALUES (?, ?, ?, ?, ?) ON DUPLICATE KEY UPDATE `contactPhoneNumber` = VALUES(`contactPhoneNumber`), `fullName` = VALUES(`fullName`)");
$stmt->bind_param('sssis', $email_address, $phone_number, $full_name, $timestamp, $requested_by);
$email_address = $_POST['email-address'];
$phone_number = $_POST['phone-number'];
$full_name = $_POST['full-name'];
$timestamp = time();
$requested_by = $_POST['requested-by'];
$stmt->execute();
$stmt->close();

$customer_id = $conn->insert_id;

$stmt = $conn->prepare("INSERT INTO `addresses` (`customerId`, `streetAddress`, `neighbourhood`, `town`, `county`, `postcode`) VALUES (?, ?, ?, ?, ?, ?)");
$stmt->bind_param('isssss', $customer_id, $street_address, $neighbourhood, $town, $county, $postcode);
$street_address = $_POST['street-address-one'];
$neighbourhood = $_POST['street-address-two'];
$town = $_POST['city'];
$county = $_POST['state'];
$postcode = $_POST['postcode'];
$stmt->execute();
$stmt->close();

$address_id = $conn->insert_id;

$sample_request_items = [];

$_POST['sample-one'] != "" && $sample_request_items[] = $_POST['sample-one'];
$_POST['sample-two'] != "" && $sample_request_items[] = $_POST['sample-two'];
$_POST['sample-three'] != "" && $sample_request_items[] = $_POST['sample-three'];
$_POST['sample-four'] != "" && $sample_request_items[] = $_POST['sample-four'];
$_POST['sample-five'] != "" && $sample_request_items[] = $_POST['sample-five'];
$_POST['sample-six'] != "" && $sample_request_items[] = $_POST['sample-six'];

$stmt = $conn->prepare("INSERT INTO `sample_requests` (`customerId`, `addressId`, `website`, `requestedBy`, `timeRequested`) VALUES (?, ?, ?, ?, ?)");
$stmt->bind_param('iisss', $customer_id, $address_id, $website, $requested_by, $timestamp);
$website = $_POST['website'];
$stmt->execute();
$stmt->close();

$sample_request_id = $conn->insert_id;

$stmt = $conn->prepare("INSERT INTO `sample_request_items` (`sampleRequestId`, `sampleName`) VALUES (?, ?)");
$stmt->bind_param('is', $sample_request_id, $sample_name);
foreach($sample_request_items as $sample_name) { $stmt->execute(); }
$stmt->close();

$conn->close();

Thanks!

@D.Dimitrov Yes mate, exactly the same happens - I've done it like this in the past as well and it's always worked so I'm literally so puzzled — Keydose, Nov 09 '18 at 14:49
@D.Dimitrov, [this](https://stackoverflow.com/questions/43184732/mysqli-insert-id-mysqli-insert-id-or-id-user-stmt-insert-id) seems to suggest there is no difference. — Nigel Ren, Nov 09 '18 at 15:03

score 0 · Answer 1 · answered Nov 09 '18 at 15:19

Looks similar to this:

Using PHP, MySQLi and Prepared Statement, how I return the id of the inserted row?

So your code should look like this:

<?php require('../includes/quotesleads-database.php'); ?>
<?php
$stmt = $conn->prepare("INSERT INTO `customers` (`contactEmailAddress`, `contactPhoneNumber`, `fullName`, `createdAt`, `createdBy`) VALUES (?, ?, ?, ?, ?) ON DUPLICATE KEY UPDATE `contactPhoneNumber` = VALUES(`contactPhoneNumber`), `fullName` = VALUES(`fullName`)");
$stmt->bind_param('sssis', $email_address, $phone_number, $full_name, $timestamp, $requested_by);
$email_address = $_POST['email-address'];
$phone_number = $_POST['phone-number'];
$full_name = $_POST['full-name'];
$timestamp = time();
$requested_by = $_POST['requested-by'];
$stmt->execute();
$customer_id = $stmt->insert_id;
$stmt->close();
[...]

It is important to mention that not the connection knows the insert-id but the result of the statement knows it.

MySQLi insert_id returns 0

1 Answers1