Question regarding adding a new item in a datatable?

webpointz

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?

tangerine

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.

webpointz

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??

webpointz

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.

crush123

does this help ?

http://datatables.net/forums/discussion/30572/retrieve-value-of-newly-created-row#latest

webpointz

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

allan

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

webpointz

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

allan

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

webpointz

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?

webpointz

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')
    );
}

tangerine

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

allan

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

webpointz

Thanks Allan

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

webpointz

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.

allan

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

webpointz

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

allan

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

webpointz

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

webpointz

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();

allan

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

webpointz

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 );

tangerine

    ->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.

webpointz

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.

webpointz

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.

webpointz

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?

allan

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

Allan

webpointz

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

allan

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

webpointz

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?

allan

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