php - 多个 MySql 插入和更新未按预期运行

Question

我正在尝试将信息插入表中并在一个字符串中更新另一个表(在同一数据库中)中的两行。我的印象是，您可以通过在每次尝试后添加一个分号来非常简单地完成此操作，并且它会在同一字符串中单独对它们进行操作。

我想不出任何其他方法来达到在“游戏”表中插入数据并同时更新“玩家”表中两个唯一行中的数据的预期效果。

这是我的代码...

<?php

include_once('database-details.php');

$winner         = mysqli_real_escape_string($db, $_REQUEST['winner']);
$loser              = mysqli_real_escape_string($db, $_REQUEST['loser']);
$balled         = mysqli_real_escape_string($db, $_REQUEST['balled']);
$fixtures           = 'games';
$table              = 'players';

$sql = "INSERT INTO `$fixtures` "
    . "(`id`, `winner`, `loser`, `balled`) VALUES "
    . "(NULL, '$winner', '$loser', '$balled') ;"
    . "UPDATE `$table` SET apps = apps + 1, wins = wins + 1, balled = balled + $balled WHERE id = $winner ;"
    . "UPDATE `$table` SET apps = apps + 1, losses = losses + 1, balled = balled - $balled WHERE id = $loser";

if(!$result = $db->query($sql)){
    die('There was an error running the query [' . $db->error . ']');
}

$response = (object)array('status' => 'error');

if($result){
    $response = (object)array(
        'status'    => 'ok',
        'id'        => $db->insert_id,
    );
}

header('Content-type: application/json');
echo json_encode($response);

?>

脚注...使用ajax提交表单，然后在前端更新内容。我可以确认提交工作正常，当我测试了所有三个单独提交的请求中只有一个并且每个都工作正常时。只有当我将它们组合在一起时它才会中断。

预先感谢您的帮助。

Answer 1

您是否必须在同一个查询中执行更新查询？

也没有意义

$response = (object)array('status' => 'error');

if($result){

因为你已经在上面那行停止了脚本的执行

if(!$result = $db->query($sql)){

也许试试:

<?php

include_once('database-details.php');

$winner         = mysqli_real_escape_string($db, $_REQUEST['winner']);
$loser              = mysqli_real_escape_string($db, $_REQUEST['loser']);
$balled         = mysqli_real_escape_string($db, $_REQUEST['balled']);
$fixtures           = 'games';
$table              = 'players';

$sql = "INSERT INTO `$fixtures` "
    . "(`id`, `winner`, `loser`, `balled`) VALUES "
    . "(NULL, '$winner', '$loser', '$balled') ;"

if(!$result = $db->query($sql)){
    die('There was an error running the query [' . $db->error . ']');
else{
    $insertid = $db->insert_id
    $sql = "UPDATE `$table` SET apps = apps + 1, wins = wins + 1, balled = balled + $balled WHERE id = $winner ;"
    if(!$result = $db->query($sql)){
        die('There was an error running the query [' . $db->error . ']');
    }
    $sql = "UPDATE `$table` SET apps = apps + 1, losses = losses + 1, balled = balled - $balled WHERE id = $loser";
    if(!$result = $db->query($sql)){
        die('There was an error running the query [' . $db->error . ']');
    }
    $response = (object)array(
        'status'    => 'ok',
        'id'        => $insertid ,
    );
}

header('Content-type: application/json');
echo json_encode($response);

?>

如果您在一个查询中运行多个 sql 语句，则根据您的 $db 类，您的 insert_id 值可能不是您所期望的值。