mysql - 如何从其他表中检索 mysql 中另一个表中的复合键的 ID

Question

我有 4 个表，tblstaff、tblgrade、tblperformance 和 tblcategory。在 tblperformance 中，有 3 个键。 perID 是主键。 StaffNo 和 catID 是复合键。

我在tblcategory和staff中使用catID作为自动增量，No，tblstaff是我自己填写的。当我运行代码时，我的sql语句没有问题。

如何使用sql中的插入语句从tblstaff和tblcategory检索staffNo和catID以插入到tblperformance中？

<?php
$staffNo=$_POST['staffNo'];
$staffName=$_POST['staffName'];

$grade=$_POST['grade'];
$gradePosition=$_POST['gradePosition'];
$gradeDepartment=$_POST['gradeDepartment'];

$catTechnical=$_POST['catTechnical'];
$catOtherTechnical=$_POST['catOtherTechnical'];
$catTechnicalDescription=$_POST['catTechnicalDescription'];
$catOtherTechnicalDescription=$_POST['catOtherTechnicalDescription'];
$catWeightage=$_POST['catWeightage'];

$perReqScore=$_POST['perReqScore'];
$perActScore=$_POST['perActScore'];
$perAction=$_POST['perAction'];
$perOtherAction=$_POST['perOtherAction'];
$perTrainingIlsas=$_POST['perTrainingIlsas'];
$perTrainingPublic=$_POST['perTrainingPublic'];

$sql1="INSERT INTO tblstaff(staffNo, staffName)VALUES('$staffNo', '$staffName')";
$result1=mysql_query($sql1);

$sql2="INSERT INTO tblgrade(grade, gradePosition, gradeDepartment)VALUES('$grade', '$gradePosition', '$gradeDepartment')";
$result2=mysql_query($sql2);

$sql3="INSERT INTO tblcategory(catTechnical, catOtherTechnical, catTechnicalDescription, catOtherTechnicalDescription,catWeightage)
VALUES('$catTechnical', '$catOtherTechnical', '$catTechnicalDescription', '$catOtherTechnicalDescription', '$catWeightage')";
$result3=mysql_query($sql3);

$sql4="INSERT INTO tblperformance(perReqScore, perActScore, perAction, perOtherAction, perTrainingIlsas, perTrainingPublic)
VALUES('$perReqScore','$perActScore', '$perAction', '$perOtherAction', '$perTrainingIlsas', '$perTrainingPublic')";
$result4=mysql_query($sql4);

if(($result1 || $result2) || ($result3 || $result4) == TRUE)
{
echo "<script>alert('Data inserted successfully')</script>";
}

else 
{
echo "ERROR";
}
?>

Answer 1

要检索数据，根据您的情况:

$query = "SELECT * FROM tblcategory ORDER BY catID DESC LIMIT 1";
$result = mysql_query($query);

while ($row = mysql_fetch_array($result)) {
    $latestCatID = $row["0"];
}

其中 SELECT * FROM tblcategory ORDER BY catID DESC LIMIT 1 为您提供插入的最后一条记录。

对于staffNo，您可以只使用$staffNo

最后，插入到 tblperformance 中的内容将如下所示:

$sql4="INSERT INTO tblperformance(catID, staffNo, perReqScore, perActScore, perAction, perOtherAction, perTrainingIlsas, perTrainingPublic)
VALUES('$latestCatID', '$staffNo', '$perReqScore','$perActScore', '$perAction', '$perOtherAction', '$perTrainingIlsas', '$perTrainingPublic')";
$result4=mysql_query($sql4);

请注意 '$latestCatID' 和 '$staffNo'，如果您是，则不需要单引号 ''使用整数数据类型。