How to make update query with parameters and CASE statement in Laravel 4

Question

I'm trying to create mysql UPDATE query that will take parameters. In addition I want to append to the end of the field if the field isnt empty. For that I'm using CASE statement. Here's my query in doctrine (from silex):

$query = "UPDATE table SET filed = (
CASE WHEN field = ''
THEN :param1
ELSE concat(field, :param1)
END)
WHERE id=:param2";
$app['db']['test_db']->executeUpdate($sql, array('param1' => $user_text, 'param2' => $selected_id));

Now I want to convert it to fluent or raw query so I can use it in Laravel 4.

Here's my code:

$param1 = "String..."; // user string
$param2 = 45; // id
DB:connection('test_db')->statement("UPDATE table SET field =(
case
    WHEN field=''
    THEN $param1 
    ELSE concat(field, $param1)
END)
WHERE id=$param2");

When I execute this query in Laravel I see

Syntax error or access violation: 1064

Any help will be appreciated.

Antonio Carlos Ribeiro · Accepted Answer · 2013-08-26 17:55:30Z

8

Try running it using raw():

DB::statement(
    DB::raw(
                "UPDATE table SET field =
                    (
                        CASE
                            WHEN field=''
                            THEN ?
                            ELSE concat(field, ?)
                        END
                    )
                    WHERE id=?"
    ),

    array($param1, $param1, $param2)
);

edited Aug 26, 2013 at 17:55

answered Aug 26, 2013 at 17:12

Antonio Carlos Ribeiro

87.9k22 gold badges220 silver badges205 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

BlueNile Over a year ago

This solution doesn't work when $param1 text is long and have new lines etc. It doesnt escape it.

BlueNile Over a year ago

Thank you, this one worked. I think I tried every other combination with raw statement. I even tried to get PDO instance and do it from PDO level but that would be counter productive.

Collectives™ on Stack Overflow

How to make update query with parameters and CASE statement in Laravel 4

1 Answer 1

2 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related