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!

Answer 1

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.