Update table value based on string length or other to set a specific value-CodePudding

I want to update a table value to either 1 of 2 values. The selected value has 2 possible choices I ride a bike or I fly an airplane. If the entered value is I ride a bike then the database value should be set as 1 , if it's I fly an airplane then the value should be set at 2. This way When I display/view the table, either an image of a bike (called 1.png) or a plane (called 2.png) will be displayed , based on what the value of transport is set as.

// get the passed variables from the web form
      
$id=$_POST['id'];     
$pid = $_POST['pid'];
$transport=$_POST['transport'];


// update data in mysql database
$sql = "UPDATE survey SET pid=?, transport=? WHERE id=?";
$stmt= $con->prepare($sql);
$stmt->bind_param("ssi", $pid, $transport, $id);
$stmt->execute();

The above code currently works but displayed in the table is the text of ride bike or fly airplane I prefer the simple image So I was thinking something like using strlen, ride bike has 15 characters,or airplane has 18

$sql = "UPDATE survey SET pid=?,if (strlen(['transport']) == 18){set '2';}else{set '1';} ,WHERE id=?";

but it doesn't work and I have no idea because this is just a hobby.

CodePudding user response：

You could do this in PHP.

$id=$_POST['id'];     
$pid = $_POST['pid'];
$transport=$_POST['transport'];
switch(strtolower($transport)) {
    case 'i ride a bike':
        $transportValue = 1;
        break;
    case 'i fly a plane':
        $transportValue = 2;
        break;
    default:
        $transportValue = 0; // if it's something else
        break;
}

// update data in mysql database
$sql = "UPDATE survey SET pid=?, transport=? WHERE id=?";
$stmt = $con->prepare($sql);
$stmt->bind_param("ssi", $pid, $transportValue, $id);
$stmt->execute();

CodePudding user response：

You have to put the IF in the value expression. SQL is not a procedural language.

You're also missing the column to assign to.

UPDATE survey
SET pid = ?,
    transport = IF(LENGTH(?) = 18, '2', '1')
    WHERE id = ?

But hard-coding lengths like this seems error-prone -- what if you had two values with the same length. Compare with the actual string.

UPDATE survey
SET pid = ?,
    transport = CASE ?
        WHEN 'I fly an airplane' THEN '2'
        ELSE '1'
    END
    WHERE id = ?

This also generalizes better when you have more than 2 choices, you can just add more WHEN clauses.

You can also use the FIELD() function:

UPDATE survey
SET pid = ?,
    transport = FIELD(?, 'I fly an airplane', 'I ride a bike')
    WHERE id = ?