Creating a database query using dsql

Is it possible to make a query using any of the methods of dsql() ?

something like:

$dq=$this->api->db->dsql()->execute('insert into table xx (field1,field2) values ('a','b')');

Thanks

DSQL is now refactored as a Separate Library: https://git.io/dsql

$this->api->db->dsql()->table('table')->set('field1','a')->set('field2','b')->do_insert();

Naturally you can also use

$this->api->db->dsql()->table('table')->set($associative_array)->do_insert();

Romans already showed an example of an insert. You can select an array of data like this (note the debug() is optional and will output the resulting sql statement at top of the page)

   $db=$this->api->db->dsql()->table('table1 x')
             ->field('x.col1')
         ->field('sum(x.col2) col2') 
  //         ->debug()
         ->join('table2 y','x.id=y.some_id')
         ->where('x.col3','Y')
         ->where('x.col4',$this->auth->api->get('id'))
         ->group('x.col2')
                     ->order('x.col1')
         ->do_getAll();

and then loop through the result set to do something

   foreach ($db as $row) {
      if (is_array($row)) {
         //do something here with row['col1'], row['col2']            
      }
    }

just select a single value

   $db=$this->api->db->dsql()->table('table1 x')
             ->field('x.col1')
         ->where('x.col2,'constant')
         ->do_getOne();

You can update records

           $this->api->db->dsql()->table('table1 x')
             ->where('id',$somevariable)
             ->set('col1',$somevalue)
             ->debug()
             ->do_update();

and delete records

            $this->api->db->dsql()->table('table1 x')
             ->where('id',$somevariable)
             ->do_delete();

Where possible, it's best to use models like below to persist your data but the nice thing about ATK4 is it doesnt get in the way so if you need to, you can execute SQL anywhere in your page to achieve a simple result.

       $s=$p->add('Model_Story')->loadData($story);
       $s->set('points', $value);
       $s->update();