Question 41672 in PHP Question posted in
Official manual can be found here.

Update table value based on string length or other to set a specific value – PHP

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.

Tags:

2

Answers


  1. 0

    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 = ?
    Login or Signup to reply.
  2. 0

    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();

    Or, you could do this:

    $id=$_POST['id'];     
$pid = $_POST['pid'];
$transport=$_POST['transport'];
$str = strtolower($transport);

if ( strpos($str, 'bike') !== false ) {
    $transportValue = 1;
} elseif ( strpos($str, 'plane') !== false ) {
    $transportValue = 2;
} else {
    $transportValue = 0; // if it's something else
}

// 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();

    Get really fancy

    <?php

class Foo {

    /** @var mysqli */
    protected $connection;
    /**
     * description
     * @param mysqli $connection pass in a mysqli connection
     */
    public function __construct(mysqli $connection)
    {
        $this->connection = $connection;
    }

    /**
     * run the process
     * @param array
     * @return void
     */
    public function run($args)
    {
        $id = $args['id'];     
        $pid = $args['pid'];
        $transport = $args['transport'];
        $this->update($id, $pid, $transport);
    }
    
    protected function update(int $id, int $pid, string $transport)
    {
        $transportValue = $this->mapPhraseToInteger($transport);
        $sql = "UPDATE survey SET pid=?, transport=? WHERE id=?";
        $stmt = $this->connection->prepare($sql);
        $stmt->bind_param("ssi", $pid, $transportValue, $id);
        $stmt->execute();
    }
    
    // Converts string that contains some word to an int value
    protected function mapPhraseToInteger(string $transport) : int
    {
        $words = [
            'bike' => 1,
            'plane' => 2,
            'jet' => 2, // map multiple words to one value
            'helicopter' => 3,
            'chopper' => 3,
            'submarine' => 9
        ];
        $str = strtolower($transport);
        foreach ( $words as $word => $value ) {
            if ( str_contains($str, $word) ) {
                return $value;
            }
        }
        // Not found so return some default value
        return 0;
    }
}
// Create a new Foo object and run it
// Assume we get these values from somewhere.
(new Foo(new mysqli('somehost', 'username', 'password', 'database')))->run($_POST);
    Login or Signup to reply.
Please signup or login to give your own answer.

Back To Top
Search