Laravel DB::table update filed

laravel update only changed fields
laravel eloquent wherein
laravel eloquent join
laravel update data
laravel update column from another column
laravel update column migration
laravel save to database
laravel eloquent exists

I have this query

DB::table('pro_orders_has_passengers')
        ->where('title_name','MR.')
        ->update([
            'title_name' => 'mr',
    ]);
    DB::table('pro_orders_has_passengers')
        ->where('title_name','MRS.')
        ->update([
            'title_name' => 'mrs',
    ]);
    DB::table('pro_orders_has_passengers')
        ->where('title_name','Miss')
        ->update([
            'title_name' => 'ms',
    ]);
    DB::table('pro_orders_has_passengers')
        ->where('title_name','Girl')
        ->update([
            'title_name' => 'girl',
    ]);
    DB::table('pro_orders_has_passengers')
        ->where('title_name','Boy')
        ->update([
            'title_name' => 'boy',
    ]);

I got the right result.But I want this result in one query. something like this

 $titlename = ['MR.','MRS.','Miss','Girl','Boy'];

    DB::table('pro_orders_has_passengers')
        ->where('title_name',$titlename)
        ->update([
            ........
    ]);

How can i do this? Thanks you response.

    $titlename = ['mr' => 'MR.','mrs' => 'MRS.','ms' => 'Miss','girl' => 'Girl', 'boy' => 'Boy'];

    $update_query = "UPDATE pro_orders_has_passengers SET title_name = CASE";
    foreach ($titlename  as $new_title => $title) {
       $update_query = $update_query . " WHEN title_name = '$title'  THEN  '$new_title'";
    }
    $update_query = $update_query . " END WHERE title_name IN ('".implode(array_values($titlename), '\',\'')."')";
    \DB::statement($update_query);

Try this way. It'll help you to do bulk update.

Final ouput will be

UPDATE pro_orders_has_passengers SET title_name = CASE WHEN title_name = 'MR.' THEN 'mr' WHEN title_name = 'MRS.' THEN 'mrs' WHEN title_name = 'Miss' THEN 'ms' WHEN title_name = 'Girl' THEN 'girl' WHEN title_name = 'Boy' THEN 'boy' WHERE END title_name IN ('MR.','MRS.','Miss','Girl','Boy')

How do i update only one column per row satisfy the conditions, How do i update only one column per row satisfy the conditions $user = Auth::​guard('admin')->user(); $user_id = $user->id; i.e. $user_id should be equal to id of the posts table i.e. user_id column or better use Eloquent This article consists of writing specifically made to share on how to update a field in database from form in a web application based on Laravel framework. The main purpose is to update the value of a single field in a database where the value itself is acquired from a form defined in a blade view template file.

I assume this way you can achieve what you are looking for

$titlename = ['MR.','MRS.','Miss','Girl','Boy'];
for($i=0; $i < count($titlename);$i++) {
DB::table('pro_orders_has_passengers')
            ->where('title_name',$titlename[$i])
            ->update(['title_name' => strtolower(str_replace('.', '' , $titlename[$i]))]);
}

Or with a foreach loop, for improved code readability:

$titlenames = ['MR.','MRS.','Miss','Girl','Boy'];
foreach($titlenames as $titlename) {
DB::table('pro_orders_has_passengers')
            ->where('title_name',$titlename)
            ->update(['title_name' => strtolower(str_replace('.', '' , $titlename))]);
}

Query Builder - Laravel, The new migration will be placed in your database/migrations directory. methods you may use the Laravel schema builder to expressively create and modify tables. When creating the table, you may use any of the schema builder's column  Laravel makes connecting with databases and running queries extremely simple. The database configuration file is app/config/database.php. In this file you may define all of your database connections, as well as specify which connection should be used by default. Examples for all of the supported database systems are provided in this file.

Try This one. For more refer following links

1.I want to use CASE statement to update some records in sql server 2005

2.Running A General Statement : https://laravel.com/docs/5.7/database

DB::statement(
      UPDATE pro_orders_has_passengers
      SET title_name = CASE  
                    WHEN title_name = 'Mr.' THEN 'mr' 
                    WHEN title_name = 'Mrs.' THEN 'mrs' 
                    //-------Others cases-----//
                    ELSE LASTNAME
                END 
      WHERE title_name IN ('Mr.', 'Mrs.', .....)
);

Eloquent ORM - Laravel, Ordering, Grouping, Limit & Offset; Conditional Clauses; Inserts; Updates. Updating JSON You may use the table method on the DB facade to begin a query. The table To retrieve a single row by its id column value, use the find method: Laravel's database query builder provides a convenient, fluent interface to creating and running database queries. It can be used to perform most database operations in your application and works on all supported database systems. The Laravel query builder uses PDO parameter binding to protect your application against SQL injection attacks.

Eloquent: Getting Started - Laravel, Laravel - Update Records - We can update the records using the DB facade with update method. The syntax of update method is as shown in the following table. class StudUpdateController extends Controller { public function index() { $​users = DB::select('select * from name,$id]); echo "Record updated successfully. The new migration will be placed in your database/migrations directory. Each migration file name contains a timestamp which allows Laravel to determine the order of the migrations. The --table and --create options may also be used to indicate the name of the table and whether the migration will be creating a new table. These options pre-fill

Database: Migrations - Laravel, simple create and update; fillable/guarded attributes; findOrNew; firstOrNew/​firstOrCreate We've already seen that without Eloquent, to insert a new record into the age to our $fillable array, have a look inside your database "dogs" table. $dog = \App\Dogs::find(1); $dog->name; // "Joe" $dog = \App\Dogs::find(​100);  Laravel’s Eloquent ORM has a method called fill which accepts an array and will update the database with new values for only the fields passed in. The nice thing about the fill method is that it automatically preserves values that are already stored in the database if you don’t want to update every field when doing a PUT request.

Database: Query Builder - Laravel, The Eloquent ORM included with Laravel provides a beautiful, simple ActiveRecord implementation for working with your database. Each database table has a corresponding "Model" which is used to interact with that table. Models allow you to query for data in your tables, as well as insert new records into the table.

Comments
  • You can try REPLACE. 'title_name' => \DB::raw("REPLACE(REPLACE(REPLACE(REPLACE(REPLACE(title_name, 'MR.', 'mr'), 'MRS.', 'mrs'), 'Miss', 'ms'),'Girl', 'girl'), 'Boy','boy')")
  • Just updated, try again and paste ther exact error message if have any issue.
  • SQLSTATE[42000]: Syntax error or access violation: 1064 You have an error in your SQL syntax; check the manual th at corresponds to your MariaDB server version for the right syntax to use near 'WHERE title_name IN ('MR.','MRS.' ,'Miss','Girl','Boy') END' at line 1 (SQL: UPDATE pro_orders_has_passengers SET title_name = CASE WHEN title_name = 'MR.' THEN 'mr' WHEN title_name = 'MRS.' THEN 'mrs' WHEN title_name = 'Miss' THEN 'ms' WHEN title_name = 'Girl' THEN 'girl' WHEN title_name = 'Boy' THEN 'boy' WHERE title_name IN ('MR.','MRS.','Miss','Girl','Boy') END)
  • Im' sorry,I got Undefined offset: 5
  • l'm sorry,If use strtolower can't remove dot after mr. mrs. ,but thanks sir
  • check the updated answer it will remove dot from string