check/prevent inserting duplicate records

cpshart

The server file checking for duplicates on insertion of a new stock row is not working likely due to the value of the variable $count or the if condition is not being interpreted correctly

Extract of server file
public_html/Editor-PHP-1.9.0/controllers/stock_holdings_basice.php

    ->validator( function($val, $data) {
        global $db, $stock_id, $portfolio_id, $count;
        $stock_id = $data['dm_holdings']['stock_id'];
        $portfolio_id = $data['dm_holdings']['portfolio_id'];
        
        $res = $db ->sql('SELECT COUNT(*) as count FROM dm_holdings WHERE stock_id = '.$data['dm_holdings']['stock_id'].' AND user_id = '.$_POST['userid'].' AND portfolio_id = '.$data['dm_holdings']['portfolio_id'].'');
        $count = $res->fetch();
        echo "count is:$count"; 
           
         if($count > '0') {
           return "Duplicate stock and portfolio, please select another '.$stock_id.' '.$portfolio_id.'";
           }
          return true;
                 }
),
        

On selecting a stock already defined RIO.LSE (3 times) on the system, the client successfully sends the stock_id to the server, JSON response below

[{"symbol":"RIO.LSE","price":"4820"}]

When using the editor, on selecting symbol RIO.LSE it satisfies the condition above and returns "Duplicate stock .. message.

However when selecting another stock, say LAND.LSE (not defined on holdings table) where the value of $count should be 0, it still passes the if condition and returns "Duplicate stock ..

when adding the line to the server file to report the value of $count I get an error shown below

        echo "count is:$count"; 

The error below is shown

<br />
<b>Notice</b>:  Array to string conversion in <b>/home/ukincome/public_html/Editor-PHP-1.9.0/controllers/stock_holdings_basice.php</b> on line <b>61</b><br />
count is:Array{"fieldErrors":[{"name":"dm_holdings.stock_id","status":"Duplicate stock and portfolio, please select another '.2557.' '.16.'"}],"data":[],"debug":[{"query":"SELECT COUNT(*) as count FROM dm_holdings WHERE stock_id = 2557 AND user_id = 2 AND portfolio_id = 16","bindings":[]}]}    

The other issue I am getting is the console error, reported as soon as you select the Editor New Button, any ideas on fixing this would be appreciated.

VM22396:2 Uncaught SyntaxError: Unexpected end of JSON input
    at JSON.parse (<anonymous>)
    at Object.success ((index):595)
    at fire (jquery-3.3.1.js:3268)
    at Object.fireWith [as resolveWith] (jquery-3.3.1.js:3398)
    at done (jquery-3.3.1.js:9305)
    at XMLHttpRequest.<anonymous> (jquery-3.3.1.js:9548)

I can provide access and details to my system by PM

Many Thanks for any help

Colin

rf1234

“The other issue“ is probably the real issue. By the way you can use an integrity constraint in your database to make sure duplicates don't get inserted. You can replace the db error message with something more appropriate for your users client side. Just search the forum. You should find a couple of posts on this, at least one from myself.

cpshart

@rf1234, Thanks for the quick response, I will have a look at the database integrity constraint option, I have searched for the JSON.parse error on the forum and although there are some similar posts I have been unable to fix the problem.

Agreed, the user message is not the final message only a test message, as the system is in development.

Do you know how to control the duplication from the server file as an alternative solution, how you echo the value of the $count variable to the editor ?

I will look into the database option in the meantime.

Many Thanks Colin

cpshart

The JSON.parse error is as are as a result of empty JSON data being returned from the server until there is a selection of stock_id from the editor dropdown.

    editor.dependent( 'dm_holdings.stock_id', function ( val, data, callback ) {
$.ajax( {
    url: '../../Editor-PHP-1.9.0/controllers/ajax_stock_transactions.php',
    datatype: 'html',
    // pass stock_id value to server php script
    data: { "stock_id": val },
    success: function (data) {  
        if ( data.length > 0 ) {
        myJSON = JSON.parse(data);
    editor.field("dm_holdings.price").set(myJSON[0].price);
        }
    },  
}); 
});     

The error occurs twice and then once the user selects New and the stock the correct JSON file is returned from the server.

I need to prevent this section of code being invoked for the first 2 calls or until stock_id has a valid value, I will see if I can create a condition to prevent its early execution. This part of the code is supposed to achieve this

        if ( data.length > 0 ) {

but does not work

Colin

cpshart

I have removed a significant section of javascript code on the client PHP file relating to the usage of the select2 functionality, this has eradicated the JSON error above, but I will try and reintroduce the code progressively to identify what part is responsible for the error and report back.

cpshart

@rf1234 I have set up the constraint as you suggested as follows

ALTER TABLE `dm_holdings` ADD UNIQUE `unique_index`(`user_id`, `portfolio_id`, `stock_id`);

and the editor traps any duplicates with an error at the base of the dialogue (although not very user friendly, I will look to see if the message is configurable)

The message is shown below

An SQL error occurred: SQLSTATE[23000]: Integrity constraint violation: 1062 Duplicate entry '2-16-206' for key 'unique_index'

Thanks again, Colin

rf1234

(although not very user friendly, I will look to see if the message is configurable)

You could do it like this for example (from my own coding):

editor
    .on('postSubmit', function (e, json, data, action) {
        if (json.error) {
            if ( json.error.indexOf('1062 Duplicate entry') >= 0 ) {
                json.error = lang === 'de' ? 
                    "Leider darf ein Genehmiger nur einmal genehmigen. \n\
                     Bitte wählen Sie einen anderen Genehmiger!" : 
                    "Sorry, one approver may only approve once. \n\
                     Please choose a different approver!"; 
            }
            if ( json.error.indexOf('1452 Cannot add or update a child row') >= 0 ) {
                json.error = lang === 'de' ? 
                    "Keine Dateneingabe erlaubt, nur Auswahl!" : 
                    "Data entry not permitted, only selection!";
            }
        }
    })

cpshart

@rf1234, thank you for posting I will try the above code later it would be better if the message was configurable, but also good to know that I can use the database constraints option if needed.

I will also try and reintroduce the select2 code and isolate which part is causing the JSON error

Uncaught SyntaxError: Unexpected end of JSON input

I will let you know how I get on.

Thanks again Colin

rf1234

it would be better if the message was configurable, but also good to know that I can use the database constraints option if needed.

That would be a solution with a validator server side - like the one you had already developed.
There is a built-in unique validator as well: https://editor.datatables.net/manual/php/validation

... but of course it has its limitations:
https://datatables.net/forums/discussion/60692

cpshart

rf1234

Many Thanks for your help, that is really useful to know, it may suffice for my requirements in the short term.

Best Regards

Colin