PHP & mySQL获取最后一个插入ID

Question

我是php/mysql交易的新手，我很难弄清楚这种情况。

我有两个表，一个是“狗”表，另一个是“日期”表。为了在“狗”表中插入一条记录，我必须首先在“表”日期中插入一个日期，并从该日期获得自动增量id。

问题是我试着读了几篇关于如何从你刚插入一条记录的表中获取最后一个插入id的文章，但我似乎不能让它工作。

$sql1="INSERT INTO FECHAS (fecha) VALUES (NOW())";
mysql_query($sql1);
echo $sql1;

$sql3="SELECT LAST_INSERT_ID()";
mysql_query($sql3);
echo $sql3;



  $sql2="INSERT INTO PERRO (nombre_perro,FECHAS_id_fecha) VALUES ('$nombre_perro_var', '$last_id')";
echo $sql2;
if (!$mysqli->query($sql2)) {
    echo 'Error: ', $mysqli->error;
}
$result2 = mysql_query($sql2); 

请原谅这段代码，我是新手，正在学习。

谢谢!

Answer 1

使用mysqli而不是mysql_

与mysqli连接的：

$connection = mysqli_connect($hostname, $username, $password, $database_name);

插入到表中的：

mysqli_query($connection, $sql);

正在检索上次插入的id:

$id = mysqli_insert_id($connection);

Answer 2

**Database**

CREATE TABLE MyGuests (
id INT(6) UNSIGNED AUTO_INCREMENT PRIMARY KEY,
firstname VARCHAR(30) NOT NULL,
lastname VARCHAR(30) NOT NULL,
email VARCHAR(50),
reg_date TIMESTAMP
) 



**Example (MySQLi Object-oriented)**
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$sql = "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('John', 'Doe', 'john@example.com')";

if ($conn->query($sql) === TRUE) {
    ***$last_id = $conn->insert_id;***
    echo "New record created successfully. Last inserted ID is: " . $last_id;
} else {
    echo "Error: " . $sql . "<br>" . $conn->error;
}

$conn->close();
?>

**Example (MySQLi Procedural)**
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDB";

// Create connection
$conn = mysqli_connect($servername, $username, $password, $dbname);
// Check connection
if (!$conn) {
    die("Connection failed: " . mysqli_connect_error());
}

$sql = "INSERT INTO MyGuests (firstname, lastname, email)
VALUES ('John', 'Doe', 'john@example.com')";

if (mysqli_query($conn, $sql)) {
    ***$last_id = mysqli_insert_id($conn);***
    echo "New record created successfully. Last inserted ID is: " . $last_id;
} else {
    echo "Error: " . $sql . "<br>" . mysqli_error($conn);
}

mysqli_close($conn);
?>

**Example (PDO)**
<?php
$servername = "localhost";
$username = "username";
$password = "password";
$dbname = "myDBPDO";

try {
    $conn = new PDO("mysql:host=$servername;dbname=$dbname", $username, $password);
    // set the PDO error mode to exception
    $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
    $sql = "INSERT INTO MyGuests (firstname, lastname, email)
    VALUES ('John', 'Doe', 'john@example.com')";
    // use exec() because no results are returned
    $conn->exec($sql);
    ***$last_id = $conn->lastInsertId();***
    echo "New record created successfully. Last inserted ID is: " . $last_id;
    }
catch(PDOException $e)
    {
    echo $sql . "<br>" . $e->getMessage();
    }

$conn = null;
?> 

Answer 3

一个过程化的解决方案(尽管它可能有一点错误--我在PHP方面很糟糕)……

<?php
include('path/to/connection/stateme.nts');

$query = "
INSERT INTO fecha (fecha) VALUES (NOW());
 ";

mysqli_query($db,$query);

$query = "
INSERT INTO perro (nombre_perro,id_fecha) VALUES (?,?);
";

$nombre_perro = 'rover';

$id_fecha = mysqli_insert_id($db);

$stmt = mysqli_prepare($db,$query);

mysqli_stmt_bind_param($stmt, 'si', $nombre_perro,$id_fecha);

mysqli_stmt_execute($stmt);

?>

您可以考虑将这两个查询绑定到一个事务中，以便在第二个查询由于某种原因而失败的情况下，第一个查询也会失败。