CODEWITHSUNDEEP
phpmysql
Broodje
Broodje

Reputation: 75

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

Upvotes: 5

Views: 3692

Answers (3)

juergen d
juergen d

Reputation: 204766

  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 = ?

But use instead:

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

Upvotes: 9

user2883814
user2883814

Reputation: 365

you can write script some like this maybe: 

$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'];

Upvotes: 1

Filali Belhadj Zakariae
Filali Belhadj Zakariae

Reputation: 76

Change the query 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 != ''";

Don't forget to change "your_attribut".

Upvotes: 0

Related Questions