PHP 优化支票

Question

我正在尝试优化我的这张支票。我需要检查名为 lines 的表，看看是否有任何行具有匹配的 Earned 和 Maxearned 值(只有位置为 1、2、3、4 的行)。如果他们这样做，我需要从该行中获取 Earned，将其写入另一个名为 bank 的表中，然后从名为 lines 的表中删除该行。这是我的:

$sql3 = "SELECT * FROM `lines` WHERE Position <= 4 AND Linenum = '$linenum' AND Earned = Maxearned";
$result3 = mysql_query($sql3);
if (mysql_num_rows($result3) != 0)
{   
while ($row3 = mysql_fetch_array($result3)) 
{
$users[] = $row3['User'];
    }
    foreach ($users as $user)
    {
    $sql6 = "SELECT * FROM `lines` WHERE Position <= 4 AND Linenum = '$linenum' AND Earned = Maxearned AND User = '$user'";
    $result4 = mysql_query($sql6);
    while ($row4 = mysql_fetch_array($result4)) 
    {
        $earned = $row4['Earned'];
    }
    $today = date("Y-m-d");
    $method = "Queue money";
    $type = "Earned";
    $status = "Completed";
    $sql4 = "INSERT INTO bank (User,Amount,Method,Transdate,Type,Status) VALUES ('$user','$earned','$method','$today','$type','$status')";
    $sql5 = "DELETE FROM `lines` WHERE Position <= 4 AND Linenum = '$linenum' AND Earned = Maxearned AND User = '$user'";
    }
    $sql7 = "UPDATE `lines` SET Position = Position - 1 WHERE Linenum = '$linenum'";
    }

我试图避免必须运行不同的查询($sql6 和之后的一段时间)来获取 Earned 列的值。有没有办法做到这一点？我已经尝试了一切，这几乎是我想到的最好的。

Answer 1

你可以这样做:

    mysql_query("SET AUTOCOMMIT=0");
    mysql_query("START TRANSACTION");

    $sql3 = "SELECT * FROM `lines` WHERE Position <= 4 AND Linenum = '$linenum' AND Earned = Maxearned";
    $result3 = mysql_query($sql3);
    if (mysql_num_rows($result3) != 0)
    {
        while ($row3 = mysql_fetch_array($result3))
        {
            $users[] = $row3['User'];
        }
        $users_to_compare = "(" . rtrim(implode(",", $users),',') . ")";
        $sql4 = "SELECT * FROM `lines` WHERE Position <= 4 AND Linenum = '$linenum' AND Earned = Maxearned AND User IN $users_to_compare";

        $result4 = mysql_query($sql4);
        while ($row4 = mysql_fetch_array($result4))
        {
            $earned = $row4['Earned'];
            $today = date("Y-m-d");
            $method = "Queue money";
            $type = "Earned";
            $status = "Completed";
            $sql5 = "INSERT INTO bank (User,Amount,Method,Transdate,Type,Status) VALUES ('{$row4['User']}','$earned','$method','$today','$type','$status')"; 
            $result5 = mysql_query($sql5);               
        }
        $sql6 = "DELETE FROM `lines` WHERE Position <= 4 AND Linenum = '$linenum' AND Earned = Maxearned AND User IN $users_to_compare";
        $result6 = mysql_query($sql6); 
        $sql7 = "UPDATE `lines` SET Position = Position - 1 WHERE Linenum = '$linenum'";
        $result7 = mysql_query($sql7); 

        if ($result5 && $result5 && $result7) {
            mysql_query("COMMIT");
        } else {        
            mysql_query("ROLLBACK");
        }
    }

更进一步，您还可以使用Batch INSERT 来插入查询

注意:不要忘记 mysql_* 版本已弃用，您应该使用 mysqli_*