Access to data value using Global Validator

cpshart

Link to test case:
Debugger code (debug.datatables.net):
Error messages shown:
Description of problem:

Hi,

I am struggling to create the correct syntax to get a MySQL Table Value when using a Global Validator. I am using the parent child set-up for the MySQL Tables dm_holdings (Parent) and dm_holdinglines (Child)

I need to prevent editing of the parent row quantity when there are child rows defined, as it inherits the quantity from the child rows (when they exist). At a higher level I am attempting first to prevent editing the parent if a child row exists, condition would need to be as follows

dm_holdings.id = dm_holdinglines.holdings_id
AND
userid matches logged in user

It is the following line that is not evaluating correctly, the syntax of $data.['id'] is incorrect, it needs to hold the value of dm-holdings.id. My question is what is the correct syntax of this command.

->and_where( 'holdings_id', $data['id'] ) // ->and_where( 'holdings_id', '6' ) hardcoded works

If I change the index to say $data['portfolio_id'], this also fails syntax

<b>Notice</b>:  Undefined index: portfolio_id in <b>/home/ukincome/public_html/Editor-PHP-1.9.4/controllers/ukif-stock_holdings.php</b> on line <b>93</b><br />

error from var_dump($data);

 action Editor<br />
<b>Notice</b>:  Undefined index: id in <b>/home/ukincome/public_html/Editor-PHP-1.9.4/controllers/ukif-stock_holdings.php</b> on line <b>93</b><br />
array(3) {
  ["data"]=>
  array(1) {
    ["row_6"]=>
    array(1) {
      ["dm_holdings"]=>
      array(5) {
        ["portfolio_id"]=>
        string(2) "21"
        ["stock_id"]=>
        string(3) "770"
        ["quantity"]=>
        string(10) "32444.0000"
        ["price"]=>
        string(3) "327"
        ["total"]=>
        string(9) "106091.88"
      }
    }
  }
  ["action"]=>
  string(4) "edit"
  ["userid"]=>
  string(1) "1"
}

extract of server script below

$search = '%';
$userid = $_POST['userid'];

// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'dm_holdings' )

    ->fields( 
        Field::inst( 'dm_holdings.id' ),
        Field::inst( 'dm_holdings.user_id' )
            ->setValue( $userid ),            
etc...
        Field::inst( 'dm_holdings.quantity' )
            ->validator( Validate::numeric() )
            ->setFormatter( Format::ifEmpty(null) ),
etc...
    )
    
    ->validator( function ( $editor, $action, $data ) use ($userid) { //1
        global $subscription_plan_id;
        global $count;


        if ( $action === Editor::ACTION_EDIT ) { //2
        
        echo 'action Editor';
           foreach ( $data['data'] as $pkey => $values ) { //3
           
                    $count = $editor
                            ->db()
                            ->query('select')
                            ->get('*')
                            ->table('dm_holdinglines')
                            ->where( function ( $q ) use ( $userid) {
                            $q->where( 'user_id', $userid);
                            } )
                            ->and_where( 'holdings_id', $data['id'] ) // this works, change '6' to  dm_holdings.id e.g. $data['id']
                            ->exec()
                            ->count();
...

extract of client

$(document).ready(function() {
    var siteEditor = new $.fn.dataTable.Editor({
        ajax: {
            url: "../../" + EDITOR_DIR + "/controllers/ukif-stock_holdings.php",        
            type: 'POST',
            data: function ( d ) {
                d.userid = $('#passuserid').val();
            }
        },
        table: "#dm_holdings",
        fields: [ {
                label: "portfolio:",
                name: "dm_holdings.portfolio_id",
                         type: "select2",
       placeholder: "Select a portfolio"
            }, {
                label: "symbol:",
                name: "dm_holdings.stock_id",
                         type: "select2",
       placeholder: "Select a stock"
            
                }, {
                label: "Quantity:",
                name: "dm_holdings.quantity",
            }, {
                label: "Price:",
                name: "dm_holdings.price",            
            }, {
                label: "Total:",
                name: "dm_holdings.total",
            }
        ]
    });

Let me know if you need access to my system.

Thanks in advance

Colin

allan

Hi Colin,

The error message is correct - there is no $data['id'] as you can see from the var_dump of the $data parameter. The $data parameter contains the raw data submitted by the client which could be multiple rows! Also the primary key value is in the parameter name for the $data['data'] object.

So what you need to do is loop over $data['data']:

foreach ( $data['data'] as $pkey => $values ) {

That is shown in the second example here.

You'll also need to use str_replace to remove the row_ prefix from the pkey value if you want to query the database with it.

Allan

cpshart

Hi Allan

Thanks for your advise, I will do some more background reading as suggested, before amending the code, I will get back to you with my results.

Best Regards

Colin

cpshart

Hi Allan

Thanks again, all is now working, I have posted some key code extracts below.

server file extract issuing an error on the modal if there are holding lines defined for the parent holdings

    ->validator( function ( $editor, $action, $data ) use ($userid) { //1
        global $count;

        if ( $action === Editor::ACTION_EDIT ) { //2
           foreach ( $data['data'] as $pkey => $values ) { //3

                    $count = $editor
                            ->db()
                            ->query('select')
                            ->get('*')
                            ->table('dm_holdinglines')
                            ->where( function ( $q ) use ( $userid) {
                            $q->where( 'user_id', $userid);
                            } )
                            ->and_where( 'holdings_id', $values['dm_holdings']['id'] )
                            ->exec()
                            ->count();
                           
                    if ($count > 0){ //4
                        return 'cannot edit holdings with ' . $count . ' holding lines defined';
                    } //4

           } //3

        } //2
    } ) //1

client file extract, passing the id to the server which is hidden, so not visible to users to edit on the modal

        table: "#dm_holdings",
        fields: [ {
                label: "id:",
                name: "dm_holdings.id",         
                type: "hidden"
            }, {

Best Regards

Colin

cpshart

Hi Allan

I am having a major issue with passing the dm_holdings.id being the primary key from the client as shown above, I can edit the table no problems, but I cannot create a New Row, as it is not allocating the next id (defined as AUTO INCREMENT in the MySQL Table)

client extract

    var siteEditor = new $.fn.dataTable.Editor({
        ajax: {
            url: "../../" + EDITOR_DIR + "/controllers/ukif-stock_holdings.php",
            type: 'POST',
            data: function ( d ) {
                d.userid = $('#passuserid').val();
            }
        },
        table: "#dm_holdings",
        fields: [ {
                label: "id:",
        name: "dm_holdings.id",        
        type: "hidden"
            }, {
                label: "portfolio:",
                name: "dm_holdings.portfolio_id",
                         type: "select2",
       placeholder: "Select a portfolio"
            }, {

For testing I have removed the type: "hidden" and the id field is empty when adding a new row to the table.

extract of server file

// Build our Editor instance and process the data coming from _POST
Editor::inst( $db, 'dm_holdings' )

    ->fields( 
        Field::inst( 'dm_holdings.id' ),
        Field::inst( 'dm_holdings.user_id' )
            ->setValue( $userid ),            

        Field::inst( 'dm_holdings.portfolio_id' )
            ->options( Options::inst()
                ->table( 'dm_portfolios' )
                ->value( 'id' )
                ->label( array('code', 'name') )
                ->where( function ( $q ) use ( $userid) {
                    $q
                    ->where( 'dm_portfolios.user_id', $userid )
                    ->and_where('dm_portfolios.reporting_status', 'yes');
                } )
            )
            ->validator( Validate::notEmpty( ValidateOptions::inst()
                ->message( 'Please select portfolio ..' )
            ) ),    
        Field::inst( 'dm_portfolios.code' ),

My question is how can I force the population of the dm_holdings.id value on adding a New Row to the next available id normally done automatically.

If I remove the passing of id in the client, i.e. the code below I can add a New record and it would auto increment the id to its correct value in the MySQL Table

{
                label: "id:",
        name: "dm_holdings.id",        
        type: "hidden"
            }, 

But I need to pass the id in order to check for holding lines and perform extra processing accordingly, most of which is now working !.

As before let me know if you need access to my system, I can provide the links to files etc.

Thanks in advance

Colin

cpshart

Hi Allan

Subject to more testing, I think I have fixed the problem, I needed to add ->set(false) to the server file as follows

Editor::inst( $db, 'dm_holdings' )

    ->fields( 
        Field::inst( 'dm_holdings.id' )->set(false),

Many Thanks

Colin