Query in preEdit event throws undefined errors

Caspian deConwy

Hi,

I want to select a specific value from the DB which is needed to update another field on editing the DB.
Unfortunately I've never worked with PDO so I'm struggling to get it working. Maybe someone can help me?

The fetch part is where I'm always getting errors like
Fatal error: Uncaught Error: Call to undefined method DataTables\Database\Driver\MysqlQuery::fetch()

My code looks like follows:

->on( 'preEdit', function ( $editor, $id, $values ) {
        $marke = $values['marke'];
        $new_artnum = "LNC" . strtoupper(substr($marke, 0, 3));
        $sql  = "SELECT MAX(`artnum`) from `preisliste_intern' WHERE `artnum` LIKE '$new_artnum%'";
        $stmt = $editor->db()->query($sql);
        while( $row = $stmt->fetch(PDO::FETCH_ASSOC) ) {
          $foo = ...;
        }
        $editor
            ->field( 'artnum' )
            ->setValue( 'LNC' . $new_artnum . $foo ) );
    } )

How does the fetch need to look like?

Thank you,
Caspian

rf1234

If you want to use Editor's db handler for your own SQL it gets a bit tricky.

Took me a while to figure it out. I mostly use my own PDO based db handler; in order to avoid locking of the database I had to use Editor's db handler on some occasions.

Here are some code examples using the Editor "raw" method which allows binding of the parameters to avoid SQL injections and the like. I think this is the best and most convenient way if you know SQL. In addition to the raw method Editor also offers other ways to make inserts and updates in case you don't know SQL but I don't like them too much ...

Select with one result row:

$result = $editor->db()->raw()
    ->bind( ':fk', $id )  
    ->exec( 'SELECT COUNT(*) AS contractCount 
               FROM contract  
              WHERE gov_manual_creditor_id = :fk' );
$row = $result->fetch(PDO::FETCH_ASSOC);
if ( (bool)$row["contractCount"] ) {
    return false;
}

Select with multiple result rows:

$statement = ('SELECT number FROM report_type  
                WHERE user_id = :user_id');  
$result = $editor->db()->raw()
    ->bind(':user_id', $userId)
    ->exec($statement);

$row = $result->fetchAll(PDO::FETCH_ASSOC);

foreach ($row as $values) {
    .... do something ...
}

Update:

$editor->db()->raw()
   ->bind( ':id',        $id        )
   ->bind( ':startDate', setFormatterDate($startDate) )
   ->exec( 'UPDATE contract   
               SET start_date = :startDate   
             WHERE id = :id   
               AND (start_date IS NULL   OR  
                    start_date > :startDate)' );

Delete:

$editor->db()->raw()
   ->bind( ':rfp_id', $rfpId ) 
   ->exec( 'DELETE FROM rfp_has_creditor 
             WHERE rfp_id = :rfp_id' );

Insert:

$editor->db()->raw()
   ->bind( ':user_id', $userId ) 
   ->bind( ':ctr_govdept_id', $govdeptId )
   ->exec( 'INSERT INTO user_has_selected_ctr_govdept 
                    (user_id, ctr_govdept_id)  
                            VALUES 
                    (:user_id, :ctr_govdept_id)' );

and something else if you want to be able to retrieve row count and last insert id ...
https://datatables.net/forums/discussion/40752

Finally Editor's proprietary insert method that will generate the SQL for you and as far as I know will also bind the parameters.

//Insert:   $db->insert( $table, $set );
//Update:   $db->push( $table, $set, $where ); ALL FIELDS MUST BE SPECIFIED - NOT GOOD  
$editor->db()->insert( 'ctr_upload', array(
    'user_id'       => $_SESSION['id'],
    'file_id'       => $id
) );

Caspian deConwy

Wow, works perfect, thank you.