Simple select query with Editor

Simple select query with Editor

minobuminobu Posts: 23Questions: 8Answers: 2

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.

This question has an accepted answers - jump to answer

Answers

  • minobuminobu Posts: 23Questions: 8Answers: 2

    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();
        })
    
  • minobuminobu Posts: 23Questions: 8Answers: 2
    Answer ✓

    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();
            })
    
This discussion has been closed.