Update mysql record data

Ahmed Bucheeri commented on Nov 9, 2020

I am using the below to update mysql data record. The update is happening but I got the following error message when i execute it

- \koolreport\core\src\datasources\MySQLDataSource.php:388 Stack trace: #0 
- \koolreport\core\src\KoolReport.php(315): koolreport\datasources\MySQLDataSource->start() #1

$this->src('simloc')->query(
                  DB::table('device_rates')
                  ->where ('id',6)
                  ->update(['rate'=>36, 'totalbase'=>1])
                );

Ahmed Bucheeri commented on Nov 10, 2020

Any update ??

Sebastian Morales commented on Nov 10, 2020

Ahmed, query method should be used with Select clause only. If you have no need of Select here how about using only the QueryBuilder on itself like this:

                  DB::table('device_rates')
                  ->where ('id',6)
                  ->update(['rate'=>36, 'totalbase'=>1]);

Correct me if my approach is wrong in your case. Cheers,

Ahmed Bucheeri commented on Nov 10, 2020

You are correct no need for select, but how i use the dataSources ( $this->src('simloc') ) Shall I use it like this

$this->src('simloc')
            ->DB::table('device_rates')
              ->where ('id',6)
              ->update(['rate'=>36, 'totalbase'=>1]);

Sebastian Morales commented on Nov 10, 2020

For using query builder with datasource pls have a look here:

https://www.koolreport.com/docs/querybuilder/overview/#retrieving-results-retrieving-all-rows-from-a-table

Ahmed Bucheeri commented on Nov 10, 2020

Thanks But I did not understand can you pls advise how the code would be with datasource

Sebastian Morales commented on Nov 10, 2020

        $this->src('automaker')->query(
            DB::table('users')->where('name', 'John')->toMySQL()
        )
        // Equivalent: "SELECT * FROM users WHERE `name`='John'"
        ->pipe($this->dataStore('users'));

Modify it according to your databases and tables. For more details please check the query builder docs link.

Ahmed Bucheeri commented on Nov 10, 2020

Thanks But I am looking to use UPDATE instead of SELECT as I did mentioned earlier

David Winterburn commented on Nov 10, 2020

Why must you put update command inside ->query method? Is there any reason can't you separate it like this:

                  DB::table('device_rates')
                  ->where ('id',6)
                  ->update(['rate'=>36, 'totalbase'=>1]);
            
        $this->src('simloc')->query(
            DB::table('table1')->where('field1', $value1)->toMySQL()//use DB select query you want here
        )
        ->pipe($this->dataStore('datastore1'));

As Sebastian said earlier, query method only works with Select query.

Ahmed Bucheeri commented on Nov 10, 2020

I want to do it as be below, but how i tell the code to use datasource $this->src('simloc')

    DB::table('device_rates')
              ->where ('id',6)
              ->update(['rate'=>36, 'totalbase'=>1]);

KoolReport's Forum

Update mysql record data #1702

Build Your Excellent Data Report