Updating a second table PHP/Mysql

Updating a second table PHP/Mysql

minobuminobu Posts: 20Questions: 7Answers: 1

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

This question has an accepted answers - jump to answer

Answers

  • colincolin Posts: 9,780Questions: 0Answers: 1,619
    Answer ✓

    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

  • minobuminobu Posts: 20Questions: 7Answers: 1

    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

  • minobuminobu Posts: 20Questions: 7Answers: 1

    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.

  • colincolin Posts: 9,780Questions: 0Answers: 1,619

    Excellent, glad all sorted,

    Colin

Sign In or Register to comment.