Editor: Set value of field depending on values in database

jschulz

I am using Datatables Editor to manage library books. The books have an inventory number that is structured as follows: X.Y (e.g. 1.2). If a new book is added to the database and its ISBN does not already exist, X is incremented, otherwise Y. A new book with ISBN ABC will have number 1.1, if ISBN ABC is added a second-time ist has number 1.2 and ISBN BCD becomes 2.1 if first added, and so on.
For the inventory number, I created two columns in the database, for the first and second integer, Inv and SubInv. I show them in one column in Datatables with Column rendering.
If I now add a book with the editor, I would like to automatically set the inventory number to the right values (the inventory field is not shown in the "Create new entry tab)". My problem is, that I do not know how to do this. My idea was to use the setFormatter Function, but it does not work and I have no idea how to solve the problem.

My php:

$editor = Editor::inst( $db, 'books', 'BookID' );
$editor
    ->fields(
        Field::inst( 'BookID' ),
        Field::inst( 'Inv' )
             ->setFormatter( function ( $val, $data, $opts ) {
                 if (empty($data['Inv'])){
                    $val = set_inv_number($data['ISBN']);
                 }
                 return $val;
            } )
        ,
        Field::inst( 'InvSub' ),
        Field::inst( 'Status' ),
        Field::inst( 'Lender' ),
        Field::inst( 'ISBN' )
            ->validator( Validate::notEmpty( ValidateOptions::inst()
                ->message( 'An ISBN is required' ) 
            )),
        Field::inst( 'Title' )
            ->validator( Validate::notEmpty( ValidateOptions::inst()
                ->message( 'A title is required' ) 
            )),
        Field::inst( 'Author' ), 
        Field::inst( 'Publisher' ),
        Field::inst( 'Edition' ),    
        Field::inst( 'Year' )
            ->validator( Validate::notEmpty( ValidateOptions::inst()
                ->message( 'A year is required' ) 
            ) ),
        Field::inst( 'Category' ),
        Field::inst( 'Subcategory' )
        )
    ->process( $_POST )
    ->json();

the set_inv-number function (short version, the whole would be too long and I think that is not my main problem)

include( "sql_connect.php" );
    function set_inv_number($isbn){
        global $connection;

        $inv_number;

        $sql = "SELECT * FROM `books` WHERE ISBN = $isbn ";
        $result = $connection->query($sql);
        if ($result->num_rows > 0) {
            if (!($stmt = $connection->prepare( "SELECT MAX(InvSub) as max FROM books WHERE ISBN = $isbn"))) {
                echo "Prepare failed: (" . $connection->errno . ") " . $connection->error;
            }
            $stmt->execute();

            $result = $stmt->get_result();

            $row = $result->fetch_array(MYSQLI_ASSOC);
            $inv_number = $row['max'] + 1;
            $stmt->close();

        } 
                else

                .......


        return $inv_number;
    }

I'm new to coding and I would appreciate any help! Thank you

rf1234

mhhh ... the set formatter looks fine to me. Have you used a debugger to check it out?

allan

Personally I'd be tempted to do this in a trigger, rather than worrying about it at all in the PHP. For example at the moment the inventory number (if it worked) would increment on every edit as well as row inserts!

An ON INSERT trigger could do your SELECT MAX(...) for the matching rows and update the newly inserted row which Editor will then read back.

That said - why is the above not working? Not sure! I think you'd need to do some debugging as "rf1234 said.

Allan

jschulz

Thanks to both of you for your answers! A sql trigger is a good idea, I haven't thought about it. I'm going to implement one because it's maybe the nicer approach.

For the Formatter problem I found that I forgot to specify the Inv field in the js. So the formatter works fine now.

the js:

$(document).ready(function() {
    editor = new $.fn.dataTable.Editor( {
        ajax: "php/php_books.php",
        table: "#books",
        bProcessing: true,
        bServerSide: true,
        fields: [ {
                label: "Inv",  // was missing
                name: "Inv"
            },{
                label: "SubInv", 
                name: "SubInv"
            },{
           .....