区分由于错误条件导致的失败 UPDATE 和由于值不变而失败的 UPDATE [英] Differentiate between failed UPDATE due to false conditions and failed UPDATE due to unchanged values

问题描述

我的问题是关于 MySQL UPDATE 语句它返回 zero '0' 如果 WHERE 子句中的更新条件不满足但也返回 zero '0' 如果 UPDATE 语句中声明的值也未更改，并且正在使用 mysqli_affected_rows($conn); 检查受影响的行，是否有办法区分这两种失败.>

这是一个示例表:

ID(auto_increment) |音乐名称 |价格 |流派|556785 |摇滚明星| 高分辨率照片| CLIPARTO30 美元 |说唱|896785 |权力的游戏|30 美元 |O.S.T |116785 |我会跳舞 |30 美元 |跳舞 |

这是一个示例代码:

$query="UPDATE `songs` SET `music_name`='$name', `price`='$price' WHERE `genre`=$genre";$result= mysqli_query($conn,$query);if(mysqli_affected_rows($conn)===0){$query="INSERT INTO `songs`(`music_name`,`price`,`genre`) VALUES('$name','$price','$genre')";$result=mysqli_query($conn,$query);}

现在这工作正常，但是当数据库表中已经存在的相同值被输入时 mysqli_affected_rows();通过导致 INSERT INTO 语句运行，将在那里返回 zero '0'，我们最终得到重复的行.

我怎样才能更好地进行这样的查询?无需在 UPDATE 之前添加更多查询来选择和检查行.还有另一种选择，也许使用带有条件的触发器来删除重复的行?但我认为有更好的方法.

目前表中除了 auto_increment 上的 ID 外没有唯一的列

解决方案

首先，如果值相同，MySQL 不会更新该行，因此受影响的行数将为 0.

此问题的解决方案:

$query="SELECT count(*) as cnt FROM `songs` WHERE `music_name`='$name', `price`='$price' AND `genre`=$genre";/****从查询中获取计数******/如果(计数> 0 ){//更新表格行}别的{//插入表格行}

my question is regarding MySQL UPDATE statement it returns zero '0' if the update conditions in the WHERE clause are not met but also returns zero '0' if the values stated in the UPDATE statement are also unchanged and am using mysqli_affected_rows($conn); to check for affected rows is there a way to differentiate between these two failures.

Here is a sample table:

ID(auto_increment) | music_name     | price | genre |
556785             | rock star      | $30   | rap   |
896785             | game of thrones| $30   | O.S.T |
116785             | i can dance    | $30   | dance |

Here is a sample code:

$query="UPDATE `songs` SET `music_name`='$name', `price`='$price' WHERE `genre`=$genre" ;
$result= mysqli_query($conn,$query);

if(mysqli_affected_rows($conn)===0)
{
  $query="INSERT INTO `songs` (`music_name`,`price`,`genre`) VALUES ('$name','$price','$genre')";
  $result=mysqli_query($conn,$query);
}

Now this works fine but when ever the same values that already exists in the database table are entered mysqli_affected_rows(); will return zero '0' there by causing the INSERT INTO statement to run and we end up with duplicated rows.

How can i make such a query better? without having to add more queries to select and check the rows first before UPDATE. Also another option maybe using triggers with conditions to delete duplicate rows? but i think there is a better way.

Currently there are no unique columns in the table apart from ID which is on auto_increment

解决方案

First of all , if the values are the same, MySQL will not update the row so the affected row count will be 0.

Solution for this issue :

$query="SELECT count(*) as cnt FROM `songs` WHERE `music_name`='$name', `price`='$price' AND `genre`=$genre" ;
/****get count from the query******/
if(count > 0 ) {
//update the table row
}
else{
//insert the table row
}

这篇关于区分由于错误条件导致的失败 UPDATE 和由于值不变而失败的 UPDATE的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！