在IF语句中更新数据库

I am trying to update my database inside an IF statement but it doesn't seem to be working. email_sent doesn't change to 1. Is my statement correct?

我试图在IF语句中更新我的数据库，但它似乎没有工作。 email_sent不会更改为1.我的陈述是否正确？

$result2 = mysql_query ("SELECT * FROM stock_control WHERE id = '$id' ");

while ($row1 = mysql_fetch_array($result2))
{
$item=$row1['item'];
$location=$row1['location'];
$quantity=$row1['quantity'];
$threshold=$row1['threshold'];
$emailSent=$row1['email_sent'];
}


if ($quantity <= $threshold && $emailSent == 0) {
mysql_query("UPDATE stock_control SET quantity=quantity - '$n_quantity', 
email_sent = '1' WHERE id = '$id' ");
} else {
mysql_query("UPDATE stock_control SET quantity=quantity - '$n_quantity' WHERE 
id = '$id' ");
}

3 个解决方案

#1

You are closing your loop too fast for the while. You are just getting the last value of the loop:

你暂时关闭你的循环太快了。您只是获取循环的最后一个值：

$result2 = mysql_query ("SELECT * FROM stock_control WHERE id = '$id' ");

while ($row1 = mysql_fetch_array($result2))
{
   $item=$row1['item'];
   $location=$row1['location'];
   $quantity=$row1['quantity'];
   $threshold=$row1['threshold'];
   $emailSent=$row1['email_sent'];

   if ($quantity <= $threshold && $emailSent == 0) {
      mysql_query("UPDATE stock_control SET quantity=quantity - '$n_quantity', 
      email_sent = '1' WHERE id = '$id' ");
   } else {
      mysql_query("UPDATE stock_control SET quantity=quantity - '$n_quantity' WHERE 
      id = '$id' ");
   }
}

#2

Check on the$n_quantity variable, it does not seem to be properly defined. Did you mean $quantity instead?

检查$ n_quantity变量，似乎没有正确定义。你的意思是数量而不是？

You may not need the while loop, since you are dealing with only one row of the table, the row with the given id. Otherwise, if you had more rows, the while loop as given would have been prematurely closed.

您可能不需要while循环，因为您只处理表的一行，即具有给定id的行。否则，如果您有更多行，则给定的while循环将过早关闭。

SELECT * could select too many columns, which might be disadvantageous. Also, the use of msyql_query is deprecated, you will need to use mysqli_query or PDO. So the following might be helpful, assuming that $con is your database connection:

SELECT *可以选择太多列，这可能是不利的。此外，不推荐使用msyql_query，您需要使用mysqli_query或PDO。因此，假设$ con是您的数据库连接，以下内容可能会有所帮助：

$result2 = mysqli_query($con,"SELECT item, location, quantity, threshold, email_sent from stock_control where id = '$id'");

list($item,$location,$quantity,$threshold,$emailSent) = mysqli_fetch_array($result2);

if ($quantity <= $threshold && $emailSent == 0) {
      mysqli_query($con,"UPDATE stock_control SET quantity=quantity - '$quantity', 
      email_sent = '1' WHERE id = '$id' ");
   } else {
      mysqli_query($con,"UPDATE stock_control SET quantity=quantity - '$quantity' WHERE id = '$id' ");
   }

#3

The resolution was time related to the query and therefore I needed to close the connection and open a new connection after my HTML to query the DB.

分辨率是与查询相关的时间，因此我需要关闭连接并在HTML之后打开一个新连接来查询数据库。

Many thanks to all for your comments as they were all very useful and will help me in the future.

非常感谢所有人的评论，因为它们都非常有用，并将在未来帮助我。

#1