Manipulate sql database values before being submitted to the client

Manipulate sql database values before being submitted to the client

itramitram Posts: 30Questions: 11Answers: 0

Is it possible to manipulate the whole database raws on the php server side (basically using raw sql commands), before loading the data and submit them to the client from the php controller?

This question has an accepted answers - jump to answer

Answers

  • rf1234rf1234 Posts: 2,524Questions: 79Answers: 369
    Answer ✓

    Sure, anything is possible:
    - getFormatters with SQL (see example below)
    - postGet event handlers with SQL: Just pass $data by reference ("&$data") and do whatever you like to manipulate it

    Like this for example (just for illustration purposes)

    ->on( 'postGet', function ( $e, &$data, $id ) use ( $db ) { 
        $data = array_unique($data, SORT_REGULAR);
        $stmt = ('SELECT DISTINCT govdept_id 
                    FROM govdept_has_user  
                   WHERE user_id = :user_id
                     AND role IN ("Principal", "Administrator")');  
        $result = $db ->raw()
                      ->bind(':user_id',$_SESSION['id'])
                      ->exec($stmt);
        $row = $result->fetchAll(PDO::FETCH_ASSOC);
        $govdeptIdArray = [];
        foreach ($row as $val) {
           $govdeptIdArray[] = $val["govdept_id"];
        }
    
        foreach ($data AS $key => $val) {
            //for non-deleted users we need to check whether the updater is 
            //not a gov user. if so unset the record!
            $stmt = ('SELECT COUNT(*) AS is_creditor_user
                        FROM creditor_has_user 
                       WHERE user_id  = :user_id');  
            $result = $db ->raw()
                          ->bind(':user_id',    $val["log"]["user_id"])
                          ->exec($stmt);
            $isCred = $result->fetch(PDO::FETCH_ASSOC);
            if ( (bool)$isCred["is_creditor_user"] ) {
                unset($data[$key]);
            } elseif ( ! in_array($val['dept_id'], $govdeptIdArray) ) {
                unset($data[$key]);
            } elseif ( mb_strpos( $val["changer"], "gelöscht" ) === false &&
                       mb_strpos( $val["changer"], "deleted" )  === false    ) {
        //for non-deleted users we need to check whether they have an authorization for
        //the respective department at all due to the auto-increment problem with Inno DB!!
        //id's can be duplicates in the log: see: 
        //https://stackoverflow.com/questions/18692068/will-mysql-reuse-deleted-ids-when-auto-increment-is-applied
                $stmt = ('SELECT COUNT(*) AS user_has_dept
                            FROM govdept_has_user
                           WHERE user_id    = :user_id
                             AND govdept_id = :govdept_id');  
                $result = $db ->raw()
                              ->bind(':user_id',    $val["log"]["user_id"])
                              ->bind(':govdept_id', $val["dept_id"])
                              ->exec($stmt);
                $usrDept = $result->fetch(PDO::FETCH_ASSOC);
                if ( ! (bool)$usrDept["user_has_dept"] ) {
                    unset($data[$key]);
                }
            }
        }
    
        $data = array_values($data);
        array_multisort( array_column($data, "contract_id"), SORT_ASC,
                        array_column($data, "update_time"), SORT_ASC,
                        $data );
    })
    
    Field::inst( 'ctr_govdept.id AS ctr_govdept.has_contracts' )->set( false )
        ->getFormatter( function($val, $data, $opts) use ($db){
            $result = $db->raw()
                ->bind( ':fk', $val )  
                ->exec( 'SELECT COUNT(*) AS ctrCount 
                           FROM ctr_has_ctr_govdept  
                          WHERE ctr_govdept_id = :fk' );
            $row = $result->fetch(PDO::FETCH_ASSOC);
            if ( (bool)$row["ctrCount"] ) {
                return 1;
            }
            return 0;
        }), 
    
  • itramitram Posts: 30Questions: 11Answers: 0

    Thanks for pointing me to the solution.
    I did as you suggested but on 'preGet', which seems to work fine.
    Just for illustration:

        ->on('preGet', function ($editor, $id) {
              $editor->db()->raw()
                ->bind(':colStatus', 'test')
                ->exec('UPDATE `dbTable` SET `colStatus`= :colStatus WHERE `id`= 3');
        })
    
  • rf1234rf1234 Posts: 2,524Questions: 79Answers: 369

    Sure, any event is ok for this. I use "validatedEdit" and "validatedCreate" a lot: At that point you know the UPDATE or INSERT done by Editor will be fine and you can make certain required updates beforehand, e.g. more complex link table inserts and the like.

Sign In or Register to comment.