时间戳

首页 > 解决方案 > MySQLi insert_id 返回 0

php - MySQLi insert_id 返回 0

问题描述

在过去的 3 个小时里，我一直在尝试解决这个问题，但我没有运气 - 我的表都有自动增量字段，并且该行被正确插入，并且连接没有被关闭，因为之后的其他行也被插入，但获取其中任何一个的插入 ID 会导致 0

这是我正在使用的代码：

<?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();

谢谢！

标签： phpmysqliprepared-statementmysql-insert-id

解决方案

看起来类似于：

使用 PHP、MySQLi 和 Prepared Statement，我如何返回插入行的 id？

所以你的代码应该是这样的：

<?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();
[...]

重要的是要提到不是连接知道插入ID，而是语句的结果知道它。

推荐阅读