Question regarding adding a new item in a datatable?

Question regarding adding a new item in a datatable?

webpointzwebpointz Posts: 126Questions: 30Answers: 4

When I go to create a new item on a datatable, after the window comes up and upon adding the item, my code needs to take the ID that it will have for creating the item and use that to do an insert into another table but how do I get the id for the new item that is an auto-increment integer?

This question has accepted answers - jump to:

«1

Answers

  • tangerinetangerine Posts: 3,110Questions: 25Answers: 364

    MySQL has a "LAST_INSERT_ID" function, which will return the last inserted id following an INSERT.
    Presumably you would adapt the Editor's insert statement acordingly, although I'm not using Editor myself so can't be more specific.

  • webpointzwebpointz Posts: 126Questions: 30Answers: 4

    I understand the MySQL "Last_INSERT_ID" function, however, in my case, adding a new item to the datatable is performing an INSERT to one table, then, I need whatever the ID was from the INSERT to use it for an INSERT into another table.

    So, I guess I'm asking where and when does it perform the first insert and if there is a %_POST variable or something else I can use that datatables stores??

  • webpointzwebpointz Posts: 126Questions: 30Answers: 4

    Here's the issue in steps:

    1. New Item button is clicked
    2. Editor dialog opens
    3. User adds information
    4. Clicks "add item"

    When the user adds a row to the table for the above, the INSERT assigns it an auto-incremented ID.

    I then need to take the information the user supplied (i get that using the $_POST variables BUT, I also need to insert the ID that was generated by the initial INSERT into the first table.

  • webpointzwebpointz Posts: 126Questions: 30Answers: 4

    Thanks crush123 but i'm stumped on the code to use in PHP to get the most recently created id?

  • allanallan Posts: 54,688Questions: 1Answers: 8,553 Site admin

    The postEdit event in the 1.5 PHP libraries is probably your best option here. See the events documentation. There is a matching postCreate event for row creation.

    Allan

  • webpointzwebpointz Posts: 126Questions: 30Answers: 4
    edited October 2015

    Thanks Allan,

    I just need to understand these events a little more, sorry for the questions.

    I'm basically wanting to log whenever a user adds or removes stock after they have created an item or updated stock to a table.

    So if I have the following editor fields:

    Editor::inst( $db, 'seh_kititems' )
        ->fields(
            Field::inst( 'seh_kititems.referenceid' ),
            Field::inst( 'seh_kititems.description' )
                ->validator( 'Validate::notEmpty' ),
            Field::inst( 'seh_kititemtypes.name' ),
            Field::inst( 'seh_kititems.type' )
                        ->options( 'seh_kititemtypes', 'id', 'name' )
                        ->validator( 'Validate::notEmpty' ),
                Field::inst( 'seh_kititems.quantity' ),
            Field::inst( 'seh_kititems.warning' )
        )
    
        ->on( 'postCreate', function ( $editor, $id, $values, $row ) {
            logChange( $editor->db(), 'create', $id, $values );
        } )
        ->on( 'postEdit', function ( $editor, $id, $values, $row ) {
            logChange( $editor->db(), 'edit', $id, $values );
        } )
        ->on( 'postRemove', function ( $editor, $id, $values ) {
            logChange( $editor->db(), 'delete', $id, $values );
        } )
    
        ->leftJoin( 'seh_kititemtypes', 'seh_kititemtypes.id', '=', 'seh_kititems.type' )
        ->process( $_POST )
        ->json();
    

    What does my function need to look like? I just need a little help understanding what it is I'm supposed to pass.

    function logChange ( $db, $action, $id, $values ) {
        $db->insert( 'seh_itemstock', array(
            'user'   => $_SESSION['userid'],
            'action' => $action,
            'values' => json_encode( $values ),
            'row'    => $id,
            'insertdate'   => date('c')
        );
    }
    

    For instance, here are the columns in my logging table called "seh_itemstock":

    id = (auto incrementing field)
    itemid = (id generated by datatables on insert "seh_kititems.id")
    stockcount = seh_kititems.quantity
    insertdate = datetime of insert
    userid = $_SESSION['userid']
    itemname = seh_kititems.description
    
  • allanallan Posts: 54,688Questions: 1Answers: 8,553 Site admin

    It looks like your logChange function is already doing everything it needs. You pass in a database link, the action that is being performed and the row id it is being performed on, plus finally the values of the row (there are inserted into the database as JSON using your function above).

    The function is then inserted into a table called seh_itemstock on every action. Is that what you want?

    Allan

  • webpointzwebpointz Posts: 126Questions: 30Answers: 4

    Yes, thanks Allan, but I'm unclear what I put into the logChange function.

    For instance, do I have to list every field in the "seh_itemstock" table and how would it look with my fields?

  • webpointzwebpointz Posts: 126Questions: 30Answers: 4

    I'm also getting a JSON error when the page loads.

    Checking the console developer tools the error is:

    Parse error: syntax error, unexpected ';' in /php/table.seh_kititems.php on line 39

    This is referring to the ";" in the function after the ")":

    function logChange ( $db, $action, $id, $values ) {
        $db->insert( 'seh_itemstock', array(
            'userid'   => $_SESSION['userid'],
            'action' => $action,
            'values' => json_encode( $values ),
            'row'    => $id,
            'insertdate'   => date('c')
        );
    }
    
  • tangerinetangerine Posts: 3,110Questions: 25Answers: 364

    You have closed the array bracket but not the "$db->insert(".

  • allanallan Posts: 54,688Questions: 1Answers: 8,553 Site admin

    That's from an error on my page the function was copied from. I've corrected it locally and it will be deployed soon.

    Regarding what you point in the function :

    do I have to list every field in the "seh_itemstock" table

    No - this: json_encode( $values ), will store all of the value from the row in a single field. If you want to store them individually, then yes, they would need to be listened individually.

    Allan

  • webpointzwebpointz Posts: 126Questions: 30Answers: 4

    Thanks Allan

    Could you give me an example of how i would insert each value in the function?
    I'm lost.

  • webpointzwebpointz Posts: 126Questions: 30Answers: 4

    I'm getting an error now with the following:

    Fatal error: Call to undefined method DataTables\Editor::on() in php/table.seh_kititems.php on line 51

    If this requires an update to the latest release, I need a way to get "id" inserted another way.

  • allanallan Posts: 54,688Questions: 1Answers: 8,553 Site admin

    Yes, this requires Editor 1.5 or newer. Sounds like you are on 1.4.

    In which case, your only option is to parse the data sent to the server by Editor and insert that into the database as you need.

    Allan

  • webpointzwebpointz Posts: 126Questions: 30Answers: 4

    Thanks Allan, but are there any examples of the syntax I would use to get the ID datatables created on the first insert?

  • allanallan Posts: 54,688Questions: 1Answers: 8,553 Site admin
    Answer ✓

    No sorry. However, you could use the information that Editor sends back to the client:

    $editor = Editor::inst( ... )
      ->fields(); // etc
    
    $data = $editor->process( $_POST )->data();
    
    // Do stuff with the data ($data['row']['DT_RowId'] for example)
    
    echo json_encode( $data );
    

    Allan

  • webpointzwebpointz Posts: 126Questions: 30Answers: 4

    Thanks Allan, that's what I was looking for. :)

  • webpointzwebpointz Posts: 126Questions: 30Answers: 4

    Hi Allan,

    I just need to know where in the PHP page I process this information to get the inserted ID on create or edit?

    It gives me an error saying:

    Notice: Undefined variable: editor in "/php/table.seh_kititems.php" on line 20.

    Fatal error: Call to a member function process() on a non-object in /php/table.seh_kititems.php on line 20.

    Here is my code:

    <?php
    
    session_start();
    
    /*
     * Editor server script for DB table table_products
     * Created by http://editor.datatables.net/generator
     */
    
    
    // DataTables PHP library and database connection
    include( "lib/DataTables.php" );
    
    if ( isset( $_POST['action'] )) {
    
        //retrieve value(s) from editor and format as a comma separated string
    
        $data = $editor->process( $_POST )->data();
     
        // Do stuff with the data ($data['row']['DT_RowId'] for example)
     
        echo json_encode( $data );
    
    }   
    
    // Alias Editor classes so they are easy to use
    use
        DataTables\Editor,
        DataTables\Editor\Field,
        DataTables\Editor\Format,
        DataTables\Editor\Join,
        DataTables\Editor\Validate;
    
    
    // Build our Editor instance and process the data coming from _POST
    $editor = Editor::inst( $db, 'seh_kititems' )
        ->fields(
    
            Field::inst( 'seh_kititems.referenceid' ),
            Field::inst( 'seh_kititems.description' )
                ->validator( 'Validate::notEmpty' ),
            Field::inst( 'seh_kititemtypes.name' ),
            Field::inst( 'seh_kititems.type' )
                        ->options( 'seh_kititemtypes', 'id', 'name' )
                        ->validator( 'Validate::notEmpty' ),
                Field::inst( 'seh_kititems.quantity' ),
            Field::inst( 'seh_kititems.warning' )
        )
        
        ->leftJoin( 'seh_kititemtypes', 'seh_kititemtypes.id', '=', 'seh_kititems.type' )
        ->process( $_POST )
        ->json();
    
  • allanallan Posts: 54,688Questions: 1Answers: 8,553 Site admin

    You need to break the chain like I did in my example above.

    I don't really want to keep just giving code examples since that isn't a good way to learn, but you would do:

    $editor = Editor::inst( $db, 'seh_kititems' )
        ->fields(
     
            Field::inst( 'seh_kititems.referenceid' ),
            Field::inst( 'seh_kititems.description' )
                ->validator( 'Validate::notEmpty' ),
            Field::inst( 'seh_kititemtypes.name' ),
            Field::inst( 'seh_kititems.type' )
                        ->options( 'seh_kititemtypes', 'id', 'name' )
                        ->validator( 'Validate::notEmpty' ),
                Field::inst( 'seh_kititems.quantity' ),
            Field::inst( 'seh_kititems.warning' )
        )
         
        ->leftJoin( 'seh_kititemtypes', 'seh_kititemtypes.id', '=', 'seh_kititems.type' );
    
    $data = $editor
        ->process( $_POST )
        ->data();
    
    ... something
    
    echo json_encode( $data );
    

    Allan

  • webpointzwebpointz Posts: 126Questions: 30Answers: 4

    I tried that but when the page loads, I get the following error:

    Parse error: syntax error, unexpected '$data' (T_VARIABLE) in /php/table.seh_kititems.php on line 63

    <?php
    
    session_start();
    
    /*
     * Editor server script for DB table table_products
     * Created by http://editor.datatables.net/generator
     */
    
    
    // DataTables PHP library and database connection
    include( "lib/DataTables.php" );
    
    if ( isset( $_POST['action'] )) {
    
        if ( isset($_POST['edit']) ) {
            $thisProduct = '0';
            $addStockCount = trim($_POST['data']['addstock']);
        } else {
            $thisProduct = '0';
            $addStockCount = trim($_POST['data']['seh_kititems']['quantity']);
        }
    
    include('../edi-includes/db-config.php');
    
    // Create connection
    $conn = new mysqli($servername, $dbusername, $dbpassword, $dbname);
    // Check connection
    if ($conn->connect_error) {
         die("Connection failed: " . $conn->connect_error);
    }
    
    $db->insert( 'seh_itemstock', [ 'itemid' => $thisProduct, 'itemname' => $_POST['data']['seh_kititems']['description'], 'insertdate' => date('Y-m-d H:i:s'), 'userid' => $_SESSION['userid'], 'stockcount' => $addStockCount ] );
      
    }   
    
    // Alias Editor classes so they are easy to use
    use
        DataTables\Editor,
        DataTables\Editor\Field,
        DataTables\Editor\Format,
        DataTables\Editor\Join,
        DataTables\Editor\Validate;
    
    
    // Build our Editor instance and process the data coming from _POST
    $editor = Editor::inst( $db, 'seh_kititems' )
        ->fields(
    
            Field::inst( 'seh_kititems.referenceid' ),
            Field::inst( 'seh_kititems.description' )
                ->validator( 'Validate::notEmpty' ),
            Field::inst( 'seh_kititemtypes.name' ),
            Field::inst( 'seh_kititems.type' )
                        ->options( 'seh_kititemtypes', 'id', 'name' )
                        ->validator( 'Validate::notEmpty' ),
                Field::inst( 'seh_kititems.quantity' ),
            Field::inst( 'seh_kititems.warning' )
        )
        
        ->leftJoin( 'seh_kititemtypes', 'seh_kititemtypes.id', '=', 'seh_kititems.type' )
    
        $data = $editor
            ->process( $_POST )
            ->data();
     
        echo json_encode( $data );
    
  • tangerinetangerine Posts: 3,110Questions: 25Answers: 364
        ->leftJoin( 'seh_kititemtypes', 'seh_kititemtypes.id', '=', 'seh_kititems.type' )
    

    You are missing a semi-colon. If you made a priority of checking your code thoroughly you would save other people's time. Alternatively Allan offers paid support.

  • webpointzwebpointz Posts: 126Questions: 30Answers: 4

    Thanks Tangerine,

    Apologies...I didn't think I needed a semi-colon there. I've built an entire inventory order system using DataTables and it's an incredible product, but my client recently requested a new piece into the system and I'm unfamiliar with the Editor and how it gets the INSERT ID.

  • webpointzwebpointz Posts: 126Questions: 30Answers: 4

    Im almost there...sorry to be a pain.

    The JSon is returning the following:

    {"row":{"DT_RowId":"row_23","seh_kititems":{"referenceid":"fgjg","description":"hjfgj","type":"4","quantity":"8","warning":"0"},"seh_kititemtypes":{"name":"Oximeter"}}}
    

    In the JS file, I created the following:

        editor.on( 'postCreate', function ( e, json, data ) {
          var newid = json.row.DT_RowId; 
     
          }
    

    My question now is in my PHP, how do I get the value for the "row":{"DT_RowId":"row_23"?

    I tried the following:

    $NewId = trim($_POST['data']['newid']);
    

    but I'm obviously not getting it. This is the last puzzle I need to solve to just get the INSERT ID.

  • webpointzwebpointz Posts: 126Questions: 30Answers: 4

    When I run the code, in the "Web Console" under the "Response" box, I get the following JSON :

    ˅ JSON
    ˅ row: Object
      DT_RowId: "row_38"
      ˃ seh_kititems: Object
      ˃ seh_kittypes: Object
    
    

    So, How do I get at the DT_RowId?

  • allanallan Posts: 54,688Questions: 1Answers: 8,553 Site admin

    $data['row']['DT_RowId']. You'll need to strip the row_ part of course.

    Allan

  • webpointzwebpointz Posts: 126Questions: 30Answers: 4

    Thanks Allan, but I'm trying to get that value however I don't know where to get it.

    When I try to access it after the submission of the Editor box, it says either "data" is undefined or if I try accessing it via the $_POST variables in PHP.

    I'm still a novice with some of this so I apologize for the questions.

    I'm just trying to figure out where exactly I check this variable.

    Thanks

  • allanallan Posts: 54,688Questions: 1Answers: 8,553 Site admin

    Immediately after:

        $data = $editor
            ->process( $_POST )
            ->data();
    

    You should be able to use $data['row']['DT_RowId'] to access the row id.

    That will work with the 1.4- libraries. It will not work with 1.5+ where events are the way to do it.

    Allan

  • webpointzwebpointz Posts: 126Questions: 30Answers: 4

    Thanks...so after that and before the "echo json_encode( $data );"?

    Can I just set a PHP variable there or how would I do this?

  • allanallan Posts: 54,688Questions: 1Answers: 8,553 Site admin
    Answer ✓

    yes - and the same way you would set any PHP variable. $myVar = whatever;

This discussion has been closed.