使用php if语句更新mysql表

I have a table with a int column named ssn and a column called male.

我有一个表名为ssn的int列和一个名为male的列。

Now i want to update the male column with either 1 or 0 depending on the second last digit being odd or even in the ssn column.

现在我想用1或0来更新公列,这取决于第二个最后一个数字是奇数还是在ssn列中。

$sql = "SELECT * FROM db";
$result = $DBH->prepare($sql); 
$result->execute(); 
foreach($result as $row) {
$male = $row['ssn'];
$male = substr($male, -2, 1);
    if ( $male & 1 ) {
                          $gender = 1;//odd
                         }else
                         {
                          $gender = 0;};

$results= $DBH->prepare("UPDATE loandb_enkatsvardb SET male = $gender ")or die(mysql_error());
$results->execute();

The error I recieve after loading for a long time is "Internal Server Error".

我在加载很长一段时间后收到的错误是“内部服务器错误”。

I also tried "insert ignore on duplicate" where it only saved "1's" in the male column regardless of what the ssn column contained.

我还尝试了“在重复时插入忽略”,它只在男性列中保存“1”,而不管ssn列包含什么。

Cheers

1 个解决方案

#1

There is no WHERE clause on your query, so each execution of that UPDATE statement is going to update all rows in the table. Your UPDATE should really be of the form WHERE unique_id = id_value.

查询中没有WHERE子句,因此每次执行该UPDATE语句都将更新表中的所有行。你的UPDATE应该是WHERE unique_id = id_value的形式。

But rather than bother with all that fetching and all the individual updates, you could get the same thing done MUCH more efficiently in one fell swoop with one statement:

但是,不要费心去取所有的提取和所有单独的更新,你可以通过一个声明一次性更高效地完成同样的工作:

UPDATE loandb_enkatsvardb SET male = SUBSTR(ssn,-2,1) MOD 2

#1