我有两个表，我想从第一个表中提取ID（如果不存在insert，则提取ID），并使用该ID在第二个表中查找另一个值（如果找不到insert）。但由于我不了解mysql查询的工作原理，我无法了解如何。。。当前查询看起来像；我认为第一部分是有效的（查找现有条目，如果不存在则插入），但对于somereason，我无法连接到代码的"路径"部分。
请放些光。。。
$sqlcheckforexisting = "SELECT *
FROM firsttable
WHERE firsttable.data = 'DATA' ";
$sqlselect = "SELECT firsttable.ID
FROM firsttable
WHERE firsttable.data = 'DATA'";
$sqlinsert = "INSERT INTO firsttable
(data)
VALUES
('DATA')";
if(mysqli_num_rows(mysqli_query($link,$sqlcheckforexisting)) == 1) {
$ID = mysqli_query($link,$sqlselect );
if(!$ID) {
echo 'error selecting the id'. mysqli_error($link);
include 'error.html.php';
exit();
}
}
if(mysqli_num_rows(mysqli_query($link,$sqlcheckforexisting)) == 0) {
mysqli_query($link,$sqlinsert );
$ID = mysqli_query($link,$sqlselect);
if(!$ID) {
echo 'error selecting the n id'. mysqli_error($link);
include 'error.html.php';
exit();
}
}
$sqlcheckpath = "SELECT *
FROM path
WHERE path.id = $ID
AND path.path = 'path' ";
$sqlselectpath = "SELECT firsttable.ID
FROM path
WHERE firsttable.data = 'DATA'";
$sqlinsertpath = "INSERT INTO path
(firsttableID, path)
VALUES
('$ID', 'path')";
if(mysqli_num_rows(mysqli_query($link, $sqlcheckpath)) == 1) {
$pathID = mysqli_query($link, $sqlselectpath );
if(!$pathID) {
echo 'error selecting the id'. mysqli_error($link);
include 'error.html.php';
exit();
}
}
if(mysqli_num_rows(mysqli_query($link, $sqlcheckpath)) == 0) {
mysqli_query($link,$sqlinsertpath );
$pathID = mysqli_query($link, $sqlselectpath);
if(!$pathID) {
echo 'error selecting the n id'. mysqli_error($link);
include 'error.html.php';
exit();
}
}
我还没有测试过它，所以我的标准免责声明可能需要进行一些调整。
$db = mysqli_connect($host, $user, $password, $database);
$sql = "SELECT id FROM firsttable WHERE data = 'DATA';";
$result = mysqli_query($db, $sql);
if (($row = mysqli_fetch_assoc($result)) !== NULL) {
// The row existed in firsttable.
$id = $row['id'];
}
else {
$sql = "INSERT INTO firsttable (data) VALUES ('DATA');";
mysqli_query($db, $sql);
$id = mysqli_insert_id($db);
}
// Okay, now we have the id of the row in firsttable. We can use it to perform
// operations in the path table.
老实说，我不确定你想对路径表做什么。看起来您正试图从第一个表（firsttable.ID，firsttable.data）中提取字段，如果没有JOIN，这是无法实现的。如果您只是想在第二个表中查找与第一个表具有相应id的字段，您可以使用：
$sql = "SELECT id, path /* , other fields... */ FROM path WHERE id = ?;";
$query = mysqli_stmt_init($db);
if (mysqli_prepare($query, $sql)) {
mysqli_stmt_bind_param($query, 'i', $id); // 's' if $id is a string
mysqli_stmt_execute($query);
if ($result = mysqli_stmt_get_result($query)) {
if (($row = mysqli_fetch_assoc($result)) !== NULL) {
// $row now contains the fields from the path table that
// corresponds to the $id fetched from firsttable.
}
else {
// There is no row in the path table that corresponds to the $id
// from firsttable.
}
}
}