仅在值不为空时更新 mySQL [英] Only update when value is not null mySQL

问题描述

我正在处理一个更新查询，其中的值应该只在值不为空或不为空时更新.现在，无论值如何，它都会更新所有内容.请帮我解决这个问题.

I am working on an update query where the values should only update when the value is not null or empty. Now it updates everything regardless the value. Please help me out with this one.

$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"); 
}

推荐答案

1. 使用 Prepared Statements 来转义用户输入并避免 SQL 语法错误和 SQL 注入.

2. 你可以使用case

1. Use Prepared Statements to escape user input and avoid SQL syntax errors and SQL injections.

2. You can use a case

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

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

Your current query translates to (which should throw an error actually)

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 

这篇关于仅在值不为空时更新 mySQL的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！