Simple select query with Editor

minobu

Link to test case:
Debugger code (debug.datatables.net):
Error messages shown:
Description of problem:

Hi,

In my previous question i asked about doing an update query on postCreate, this works great.

    ->on('postCreate', function ($editor,$id, $values, $row ) {



        //update query
        $editor->db()
            ->query('update', 'lease_units')
            ->set( 'lease_units.row_num', $values['row'])
            ->where('id', $id )
            ->exec();
    })

How would I go about doing a select query and having the update use a value from the result? For this example, I would only need to get a single record. Something like below.

    ->on('postCreate', function ($editor,$id, $values, $row ) {

        //Select query

             $editor->db()
            ->query('select', 'lease_rows')
            ->get( 'lease_rows.number', $values['id'])

             //how do i get the returned value and set as a variable such as $number

            ->exec();


        //update query
        $editor->db()
            ->query('update', 'lease_units')
            ->set( 'lease_units.row_num', $number)
            ->where('id', $id )
            ->exec();
    })

Can I do something like this or do I need to use a different method? Thanks.

minobu

I have worked out the following and the query result is correct. I just cant figure out how to get the result out of the array. if i print_r the array into the error log it shows:

[14-Sep-2020 22:36:35 Australia/Sydney] Array
(
    [number] => 1
)

I have tested this and it is working as expected. I just can't seem to set a variable as the result. I get an error on my editor forms submit. The new row is added to the table and all fields correctly populated however the row_num field is empty in the table.

    ->on('postCreate', function ($editor,$id, $values, $row ) {

        $rownumber = $editor
            ->db()
            ->query( 'select' )
            ->get( 'number' )
            ->table( 'lease_rows' )
            ->where( 'id', $values['row'] )
            ->exec()
            ->fetchAll();

        $therow = $rownumber['number'];

        error_log(print_r($rownumber,true));

        //update query
        $editor->db()
            ->query('update', 'lease_units')
            ->set( 'lease_units.row_num', $therow)
            ->where('id', $id )
            ->exec();
    })

minobu

Nevermind, figured it out. as its the first row of the array I needed to select the row with [0] and then the key with ['number'] as shown below. I suppose if I needed to do an update over multiple rows in MySQL I could use a for loop to go through the array, that will be the next challenge.

      ->on('postCreate', function ($editor,$id, $values, $row ) {


            //select query
            $rownumber = $editor
                ->db()
                ->query( 'select' )
                ->get( 'number' )
                ->table( 'lease_rows' )
                ->where( 'id', $values['row'] )
                ->exec()
                ->fetchAll();




             $the_number = $rownumber[0]['number'];




            //update query
               $editor->db()
               ->query('update', 'lease_units')
                ->set( 'lease_units.row_num', $the_number)
                ->where('id', $id )
                ->exec();
        })