php - 仅在值不为空时更新 mySQL

Question

我正在处理一个更新查询，其中的值应该只在值不为 null 或为空时更新。现在它更新所有内容而不管值。请帮我解决这个问题。

$query = "UPDATE bundels 
SET batchkosten = CASE WHEN  ". $_POST['batchkosten'] . " IS NOT NULL
                   THEN  ". $_POST['batchkosten'] . "
                   ELSE batchkosten
              END CASE,
              CASE WHEN   ". $_POST['maandelijkse_kosten'] . "  IS NOT NULL
                   THEN   ". $_POST['maandelijkse_kosten'] . " 
                   ELSE maandelijkse_kosten
              END CASE,
              CASE WHEN   ". $_POST['aanmeldkosten'] . "  IS NOT NULL
                   THEN   ". $_POST['aanmeldkosten'] . " 
                   ELSE aanmeldkosten
              END CASE,
              CASE WHEN   ". $_POST['transactiekosten'] . "  IS NOT NULL
                   THEN   ". $_POST['transactiekosten'] . " 
                   ELSE transactiekosten
              END CASE,
              CASE WHEN   ". $_POST['referral'] . "  IS NOT NULL
                   THEN   ". $_POST['referral'] . " 
                   ELSE referral
              END CASE,
              CASE WHEN   ". $_POST['actief'] . "  IS NOT NULL
                   THEN   ". $_POST['actief'] . " 
                   ELSE actief
              END CASE
              WHERE bundel_id = ". $_POST['bundel_id'] . "";
        $result = mysql_query($query, $db) or die ('FOUT: werkt niet'); 
            header ("Location: vergelijker_bewerken.php");
        } else {
            $bundels = mysql_query("SELECT bundels.psp_id, psp.psp_id, psp_naam, bundels.bundel_id, batchkosten, maandelijkse_kosten, aanmeldkosten, transactiekosten, referral, actief from bundels
                                    JOIN psp
                                ON psp.psp_id = bundels.psp_ID"); 
}

Answer 1

1. 使用准备好的语句转义用户输入并避免 SQL 语法错误和 SQL 注入(inject)。

2. 你可以使用一个case

   UPDATE bundels 
   SET batchkosten = case when ? is not null and length(?) > 0
                          then ?
                          else batchkosten
                     end,
   ...
   

您当前的查询转换为(实际上应该抛出错误)

UPDATE bundels 
SET batchkosten = CASE WHEN ? length(?) > 0 
                       THEN ?
                       ELSE batchkosten 
                  END 
WHERE bundel_id = ? 

而是使用:

SET batchkosten = CASE WHEN ? is not null and length(?) > 0