php - 如何解决MySQL中的 "Cannot add or update a child row"？

Question

我编写了一个 PHP 脚本来与 MySQL 数据库交互。它已经接近完成，但我收到与外键约束有关的错误。

我有一个独立的函数，应该完成 MySQL 事务:

function    doTransaction($dbConnection,$ticketId,$units,$totalToPass,$newTransId,  $moneyValue,$datetime,$paymentId){

$flag = true;

$query4 = "INSERT INTO ticketlines (ticket,line,units,price,taxid,attributes) VALUES(\"".$ticketId."\", 0,".$units.",".abs($totalToPass).",'000',0x3c3f786d6c2076657273696f6e3d22312e302220656e636f64696e673d225554462d3822207374616e64616c6f6e653d226e6f223f3e3c21444f43545950452070726f706572746965732053595354454d2022687474703a2f2f6a6176612e73756e2e636f6d2f6474642f70726f706572746965732e647464223e3c70726f706572746965733e3c636f6d6d656e743e756e6943656e7461206f504f533c2f636f6d6d656e743e3c656e747279206b65793d2270726f647563742e74617863617465676f72796964223e3030303c2f656e7472793e3c656e747279206b65793d2270726f647563742e6e616d65223e202d3530253c2f656e7472793e3c2f70726f706572746965733e)";
$query3 = "INSERT INTO tickets (id,tickettype,ticketid,person,status) VALUES(\"".$ticketId."\",0,".$newTransId.",1,0);";
$query2 = "INSERT INTO receipts (id,money,datenew,attributes) VALUES(\"".$ticketId."\",".$moneyValue.",".date ("Y-m-d H:i:s",  strtotime($datetime)).",0x3c3f786d6c2076657273696f6e3d5c22312e305c2220656e636f64696e673d5c225554462d385c22207374616e64616c6f6e653d5c226e6f5c223f3e3c21444f43545950452070726f706572746965732053595354454d205c22687474703a2f2f6a6176612e73756e2e636f6d2f6474642f70726f706572746965732e6474645c223ea3c70726f706572746965733e3c636f6d6d656e743e756e6943656e7461206f504f533c2f636f6d6d656e743e3c2f70726f706572746965733e);";
$query1 = "INSERT INTO payments (id,receipt,payment,total,returnmsg)  VALUES(\"".$paymentId."\",\"".$ticketId."\",'Administrator  Adjustment',".$totalToPass.",'OK');";
$result = mysqli_query($dbConnection, $query1);

if (!$result) {
    $flag = false;
    echo "Error details: " . mysqli_error($dbConnection) . ".";
}

$result = mysqli_query($dbConnection, $query2);

if (!$result) {
    $flag = false;
    echo "Error details: " . mysqli_error($dbConnection) . ".";
}

if ($flag) {
    mysqli_commit($dbConnection);
    echo "<p>Adjustment Made</p><script>alert('Adjustment was made     successfully!!!')</script>";
 } else {
    mysqli_rollback($dbConnection);
    echo "<p>Adjustment Failed</p><script>alert('Adjustment was    unsuccessful!!!')</script>";;
}

mysqli_close($dbConnection);
}

运行此代码时返回以下错误:

Error details: Cannot add or update a child row: a foreign key   
constraint fails (`bricabrac`.`payments`, CONSTRAINT  
`PAYMENTS_FK_RECEIPT` FOREIGN KEY (`RECEIPT`) REFERENCES `receipts`
(`ID`)).Error details: You have an error in your SQL syntax; check the  
manual that corresponds to your MySQL server version for the right
syntax to use near '2015-02-20  
00:00:00,0x3c3f786d6c2076657273696f6e3d5c22312e305c2220656e636f64696e' 
at line 1.

我相信在 $query2 中插入日期时间值可能存在轻微的语法问题，我真正不遵循的是外键问题。我曾尝试更改查询的顺序，但没有成功。

Answer 1

我认为问题很简单，attributes 数据字段周围没有单引号，它可能看起来像数字字段，但它必须是文本字段，因此需要引用.

试试这个，这样在没有双引号的转义的情况下更容易阅读。

$datenew = date ("Y-m-d H:i:s", strtotime($datetime));
$query2 = "INSERT INTO receipts (id,money,datenew,attributes) 
             VALUES('$ticketId',
                    $moneyValue,
                    '$datenew',
                   '0x3c3f786d6c2076657273696f6e3d5c22312e305c2220656e636f64696e673d5c225554462d385c22207374616e64616c6f6e653d5c226e6f5c223f3e3c21444f43545950452070726f706572746965732053595354454d205c22687474703a2f2f6a6176612e73756e2e636f6d2f6474642f70726f706572746965732e6474645c223ea3c70726f706572746965733e3c636f6d6d656e743e756e6943656e7461206f504f533c2f636f6d6d656e743e3c2f70726f706572746965733e')";

$absTotPr = abs($totalToPass);
$query4 = "INSERT INTO ticketlines (ticket,line,units,price,taxid,attributes) 
            VALUES('$ticketId',
                    0,
                    $units,
                    $absTotPr,
                    '000',
                    '0x3c3f786d6c2076657273696f6e3d22312e302220656e636f64696e673d225554462d3822207374616e64616c6f6e653d226e6f223f3e3c21444f43545950452070726f706572746965732053595354454d2022687474703a2f2f6a6176612e73756e2e636f6d2f6474642f70726f706572746965732e647464223e3c70726f706572746965733e3c636f6d6d656e743e756e6943656e7461206f504f533c2f636f6d6d656e743e3c656e747279206b65793d2270726f647563742e74617863617465676f72796964223e3030303c2f656e7472793e3c656e747279206b65793d2270726f647563742e6e616d65223e202d3530253c2f656e7472793e3c2f70726f706572746965733e')";