How to re query database after input submitted?

How to re query database after input submitted?

th3t1ckth3t1ck Posts: 228Questions: 37Answers: 1

I am querying the database for the last case number, incrementing it and displaying it in a readonly editor field. Once the data is submitted the database is not queried again so I can set the next case number. Is their a way to re-query the databse upon submitting the case number?

Not sure if this makes sense or not. Please let me know if you need more info. I can setup a login for you if need be but I would need to send that to you privately.

This question has accepted answers - jump to:

Answers

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

    I think I get it :) It sounds like you can issue the next query in submitSuccess - this would ensure that only valid submissions cause a requery. If this is only when you create new records, you can test action (the fourth parameter) for create,

    Colin

  • th3t1ckth3t1ck Posts: 228Questions: 37Answers: 1
    edited August 2020

    Yes Colin, I believe you understood my attempt at explaining the problem.
    But after inspecting my code I'm beginning to understand what is going on and I don't think the submitSuccess will help I'm afraid.
    As the page loads I am querying the database for the last case number and am incrementing it. This happens in some php code before the table is generated so only a refresh of the page will do what I need which is to get the last case number so I can increment it and create the next case number to display in editor.
    I am going to create a function to query the database and increment the case number. I will call the function from the editor readonly field and have it displayed as the default value.
    I'm not sure if there is a better way to do this. If so please share. I really appreciate the time you guys spend helping us and sharing your knowledge.
    The function will look something like this...

    function getCaseNumber () {
        
    // Get current year
    $currentYear = date("Y");
    
    // Open database and get start dates
    include('db_connect.php');
    
    // query database and get the last case number
    $query="SELECT case_number from cases ORDER BY case_number desc";
    $result = mysqli_query($dbConn, $query);
    
    $row=mysqli_fetch_assoc($result);
    $caseNum=$row['case_number'];
    
    // check if last case number begins with the current year
    $num = strpos( $caseNum, $currentYear );
    if( $num === false ) {
    // last case number does not begin with current year, create new case number
        $caseNum = $currentYear."00001";
    }else{
    // last case number begins with current year, increment case number
        $caseNum++;
    }
    
    return $caseNum;
    // free the result and connection
    mysqli_free_result($result);
    mysqli_close($dbConn);
    }
    

    and called from here...

    <script>
    $(document).ready(function() {
        var editor = new $.fn.dataTable.Editor( {
            ajax: 'cases-con.php',
            table: '#cases',
            template: '#customForm',
            fields: [ { 
                    label: 'Case Number:',
                    type: 'readonly',
                    name: 'cases.case_number',
                    fieldInfo: 'NOTE: This field is not editable',
                    def: "; 
                getCaseNumber();
                echo $caseNum;
    print"
                }, {
                    ...
    etc.
    
  • colincolin Posts: 15,112Questions: 1Answers: 2,583
    Answer ✓

    Yep, that sounds like it'll work.

    Colin

  • th3t1ckth3t1ck Posts: 228Questions: 37Answers: 1

    Well that didn't work either. The page would need to be refreshed again for this to work. Same problem I had originally. Any ideas on how I could get the function to work? Once I enter a new case number and click the new (create) button the button shows a spinner and just sits there spinning away.

  • th3t1ckth3t1ck Posts: 228Questions: 37Answers: 1

    I'm wondering if I could call the getCaseNumber function with a postSubmit?

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

    My idea at the start was submitSuccess - was there a reason for not using that one?

    Colin

  • th3t1ckth3t1ck Posts: 228Questions: 37Answers: 1

    Well I did try that but it didn't seem to work. Perhaps a mistake on my end. Would that reload the page and thus get the last case number for the next case? Do you have an example I could look at?

  • colincolin Posts: 15,112Questions: 1Answers: 2,583

    No, it wouldn't reload the page. submitSuccess is just an event that you can listen for - and in the code in that function, you could make that ajax call to get the last case number from your server. This example shows a different event, but the concept would be the same.

    Colin

  • th3t1ckth3t1ck Posts: 228Questions: 37Answers: 1

    Ok. I'll give it another try.

  • th3t1ckth3t1ck Posts: 228Questions: 37Answers: 1

    So when I try the following I get the spinner on any of the buttons I press (create, edit, remove). Create works the first time but if I try it again without refreshing the page I get the spinner.

            editor.on( 'submitSuccess', function ( e, json, data, action ) {
                if( action === 'create' ) {
    //              table.ajax.reload();
    //              destroyObjects( [ case_number ] );
                    getCaseNumber();      // <-- call on function here
                    ajaxReloadTbls( [ 'cases' ] );
                    alert('Got it!');
                }
            });
    

    However I am seeing a problem that I will have. If multiple users have the form open they would all get the same case number because that number potentially isn't written to the database yet and thus has not been incremented. The case number is unique and I'm validating that before submittal but more than one user will not be able to submit that case number and would have to fill out the form all over again.

    I'm going to take a look at what I can do with the database such as making the case number a primary key. Then I just have to create the very first case number each year.
    What do you think? Would this be a better approve in your opinion?

  • th3t1ckth3t1ck Posts: 228Questions: 37Answers: 1

    So far this is working on create and remove...

    editor.on( 'submitSuccess', function ( e, json, data, action ) {
                if( action === 'create' || action === 'remove' ) {
    //              reload the page for the next case number
                    document.location.reload(true);
                }
            });
    

    Now I want to get the case number when the form is submitted instead of before hand and pop up a success confirmation. Any nudges in the right direction would be appreciated. Guess I'll have to go read up on ajax.

  • colincolin Posts: 15,112Questions: 1Answers: 2,583
    Answer ✓

    Yep, as you say, you'll need to get it post-submit, otherwise another user could grab it.

    Colin

  • th3t1ckth3t1ck Posts: 228Questions: 37Answers: 1

    I was able to get submitSuccess and postSubmit working. Thank you for the help again Colin.

This discussion has been closed.