examplep MySQL Ignore-如何捕捉PHP文件中的错误 - xampp MySQL Ignore - how to catch errors in PHP file

xampp MySQL Ignore - how to catch errors in PHP file

我是使用事务的新手。我以前的问题是，如果遇到错误，多个插入应该中止，而使用ignore的事务可以解决它。我的新问题是，我必须知道插入是否成功，如果不成功，应该显示错误。

PROFILE table
+--+----+---------+
|ID|NAME|BAL_LIMIT|
+--+----+---------+
NUMBER table
+--+----------+------+
|ID|PROFILE_ID|NUMBER|
+--+----------+------+

--交易对账单

START TRANSACTION;
INSERT IGNORE INTO `PROFILE` (`ID`, `NAME`, `BAL_LIMIT`) VALUES(NULL, "Name", 0);
INSERT INTO `NUMBER` (`PROFILE_ID`, `NUMBER`) VALUES(LAST_INSERT_ID() , "09123456789");
COMMIT;

--要处理的错误--

#1062 - Duplicate entry '09123456789' for key 'NUMBER_UNIQUE'

--PHP文件包含--

    $vname = $_POST["iname"];
    $vbalancelimit = $_POST["ibalancelimit"];
    $vnumber = $_POST["inumber"];
$transaction = "START TRANSACTION;
INSERT IGNORE INTO `PROFILE` (`ID`, `NAME`, `BAL_LIMIT`) VALUES(NULL, '"$vname'", $vbalancelimit);
INSERT INTO `NUMBER` (`ID`, `PROFILE_ID`, `NUMBER`) VALUES(NULL, LAST_INSERT_ID(), '"$vnumber'");
COMMIT;";
$execute_transaction = mysqli_multi_query($con,$transaction) or die("Error: ".mysqli_error($connection));
if (!$execute_transaction) {
            echo mysqli_error($connection);
} else {    echo "success"; }

PHP文件总是显示成功，尽管有#1062错误，并且插入被忽略

解决了多查询事务语句问题。

最终PHP文件：

<?php 
            $vname = $_POST["iname"];
            $vbalancelimit = $_POST["ibalancelimit"];
            $vnumber = $_POST["inumber"];
            $transaction = "
                START TRANSACTION;
                INSERT IGNORE INTO `PROFILE` (`ID`, `NAME`, `BAL_LIMIT`) VALUES(NULL, '"$vname'", $vbalancelimit);
                INSERT INTO `NUMBER` (`ID`, `PROFILE_ID`, `NUMBER`) VALUES(NULL, LAST_INSERT_ID(), '"$vnumber'");
                COMMIT;";
            $aff_rows = 0;
            if(mysqli_multi_query($con,$transaction)){
                do{$aff_rows+=mysqli_affected_rows($con);
                }while(mysqli_more_results($con) && mysqli_next_result($con));
            }if($aff_rows==2){//SUCCESS
                echo "<script>swal('"$vname'", '"New number: $vnumber'", '"success'");</script>";
            }if($aff_rows==1){//WRONG NUMBER CORRECT NAME
                echo "<script>swal('"Duplicate entry of $vnumber'", '"Failed to add for: $vname'", '"error'");</script>";
            }if($aff_rows==0){//WRONG NAME CORRECT/WRONG NUMBER
                echo "<script>swal('"Duplicate entry of $vname'", '"Failed to add number: $vnumber'", '"error'");</script>";}
?>