Geek Answers Handbook

Update only when value is not null mySQL

I am working on an update request where values ​​should only be updated when the value is not null or empty. Now it updates everything, regardless of value. Please help me with this.

$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"); 
}
+4
source share
3 answers
  • Use prepared expressions to avoid user input and avoid SQL syntax errors and SQL injections.

  • you can use case

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

Your current request translates to (which should really throw an error)

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

But use instead:

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

Change the request to this

$query= "UPDATE bundels SET
batchkosten =                ' ". $_POST['batchkosten'] . " ',
maandelijkse_kosten =        ' ". $_POST['maandelijkse_kosten'] . " ',
aanmeldkosten =              ' ". $_POST['aanmeldkosten'] . " ',
transactiekosten =           ' ". $_POST['transactiekosten'] . " ',
referral =                   ' ". $_POST['referral'] . " ',
actief =                     ' ". $_POST['actief'] . " '
WHERE bundel_id = ". $_POST['bundel_id'] . " ".
"and your_attribut is not null and your_attribut != ''";

Remember to change your_attribut.

0
source

script , :

$query = "Update bundels SET ";
$columns = array( "batchkosten", 
                  "maandelijkse_kosten", 
                  "aanmeldkosten",    
                  "transactiekosten", 
                  "referral", 
                  "actief");

foreach($columns as $column){
    if(isset($_POST[$column]) && !empty($_POST[$column])){
        $query .= $column . " = " $_POST[$column] . " ";            
    }
}

$query .= " WHERE bundel_id = " . $_POST['bundel_id'];
0

More articles:


All Articles