Updating a second table PHP/Mysql

minobu

Hi, Is there a way to update a related MySql table? For example

I have two tables. Units and Grades

I update a Grades value column in the Grades table using dataTables editor. This works great but I also need to select all rows in the Units table where a unit is of the same Grade I just updated.

I know how to do this in native PHP/mysql but I'm not sure where to best implement this in dataTables or if there is already a built-in api for this fort of thing? Do I execute a sperate ajax/Json call to a PHP script that takes care of this when the edit form is submitted?

Thanks

colin

If you're using Editor, you can make that change on the server in an event, such as postEdit - see list of events here.

Colin

minobu

Thanks Colin, Yes im using Editor.

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

        //Do stuff here or call PHP function

        } )

Would this be the correct way to use this? $id is the id of the row that was updated and $values being the value updated? are these arrays?

Thanks

minobu

Ok figured it out after looking through docs, pretty cool how it works. If anyone else needs something like this ill leave my code below. Thanks very much, @colin for the direction.

Editor::inst( $db, 'lease_units', 'id' )
    ->fields(
        Field::inst( 'lease_id' ),
        Field::inst( 'lease' ),
        Field::inst( 'row_sec' ),
        Field::inst( 'row_num' ),
        Field::inst( 'row' ),
    )



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

Update statement using postCreate PHP event. $values is an array so you can access individual rows of the array like I have using $values['row'] to access the row field, I could also use $values['lease'] to get the lease field that was posted. There are a few different events you can use. postCreate is used above but there is also postEdit and a few others that colin has linked.

I find the docs helpful but sometimes get confused by pseudo-code in them. Luckily the support forum has tons of code examples from previous answers. Just need a search term.

colin

Excellent, glad all sorted,

Colin