MySQL: Is it possible to CASE a FIELD on an UPDATE?

Question

So I might just be overlooking something stupid simple, but essentially I am trying to CASE the FIELD for an UPDATE in MySQL through a PHP page. I have run a ton of IF statements as well but cant seem to get this to work.

<?php
include('config.php');
$username = $_POST['username'];
$charName = $_POST['charName'];
//ID MARKS
$idMarks = $_POST['IDmarks'];
//EXPERIENCE
$EXP = $_POST['EXP'];

$sql = "UPDATE characters 
            SET CASE 
                WHEN idMarks1 IS NULL THEN idMarks1 = '$idMarks' 
                WHEN idMarks2 IS NULL THEN idMarks2 = '$idMarks' 
                WHEN idMarks3 IS NULL THEN idMarks3 = '$idMarks' 
                WHEN idMarks4 IS NULL THEN idMarks4 = '$idMarks' 
                WHEN idMarks5 IS NULL THEN idMarks5 = '$idMarks' 
                WHEN idMarks6 IS NULL THEN idMarks6 = '$idMarks' 
                WHEN idMarks7 IS NULL THEN idMarks7 = '$idMarks' 
                WHEN idMarks8 IS NULL THEN idMarks8 = '$idMarks' 
                WHEN idMarks9 IS NULL THEN idMarks9 = '$idMarks' 
                WHEN idMarks10 IS NULL THEN idMarks10 = '$idMarks' 
                WHEN idMarks11 IS NULL THEN idMarks11 = '$idMarks' 
                WHEN idMarks12 IS NULL THEN idMarks12 = '$idMarks' 
                END

                totalEXP =  totalEXP + '$EXP', 
                remEXP =    remEXP + '$EXP'

            WHERE charName = '$charName' AND username = '$username';
        ";

if ($conn->query($sql) === TRUE) {
header("Location: ../lead.php");
} else {
    echo "Error updating record: " . $conn->error;
}
?>

The idea here is that the update will put the new value ($idMarks) in the first null value it finds and then roll on to the next EXP columns. I cant just use an array due to prior issues Ive had with MySQL, and the point is that the idMarks columns are kind of fluid, tracking wounds, wounds heal to scars or limps, tattoos are permanent, etc etc which is why they are adjustable on another page. Does anyone have any ideas on how to pull this off?

actually on test that works just fine, its rolling into a field thats an int but input in a text field so it gets parsed elsewhere — NixondisNRVA, Dec 20 '17 at 07:28
Fundamentally, this is a problem of poor design. Any time you find yourself with enumerated columns, alarm bells should start ringing. — Strawberry, Dec 20 '17 at 08:04
ok then how could I store multiple values in idMarks on a single row if i just dropped the other columns? — NixondisNRVA, Dec 20 '17 at 08:57

2oppin · Answer 1 · 2017-12-20T07:39:05.610

You have PHP, why to do quirks in SQL?

$error = false;
for($i=1; $i<12; $i++) {
    $sql = "UPDATE characters  SET idMarks$i = '$idMarks'"
        . " WHERE idMarks$i IS NULL AND charName = '$charName' AND username = '$username';";
    $sql2 = "UPDATE characters SET totalEXP =  totalEXP + '$EXP', remEXP =    remEXP + '$EXP'"
        . " WHERE charName = '$charName' AND username = '$username';";

    if ($conn->query($sql) !== TRUE) {
        $error = 'Failed with 1';
        break;
    } else if ($conn->query($sql2) !== TRUE) {
        $error = 'Failed with 2';
        break;
    }
}

 if (empty($error))
     header("Location: ../lead.php");

I'll skip the question of "Where from you have that awful DB scheme", but that's alone should make any questions like 'I want to make all in one request to "optimize performance"' unreasonable.

You SQL performance can't be good with scheme that have numbered columns. Normalization goes first, and only in rare cases you will need denormalization.

Not working as it sits but I definitely think youre on to something here! Like I said probably just overlooking something stupid simple. Thank you...and yes youre probably right on the DB but this is just a work in progress and my first stab at anything this complex. Fact is I tried using arrays at first for idMarks but I couldnt swing it for the life of me once it came time to upload. Either way, still learning...diamonds arent made in a day — NixondisNRVA, Dec 20 '17 at 07:47

MySQL: Is it possible to CASE a FIELD on an UPDATE?

1 Answers1