我正在尝试优化我拥有的这个检查。我需要检查名为行的表,看看是否有任何行具有匹配的"赚取"和"最大收益"值(仅具有位置为 1,2,3,4 的行)。如果他们这样做,我需要从该行中获取 Earne,将其写入名为 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'";
}
我试图避免运行不同的查询($sql 6 和之后的一段时间)来获取 Earn 列的值。有没有办法做到这一点?我已经尝试了一切,这几乎是我想出的最好的。
你可以做这样的事情:
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_*