How to re query database after input submitted?

th3t1ck

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.

colin

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

th3t1ck

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.

colin

Yep, that sounds like it'll work.

Colin

th3t1ck

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.

th3t1ck

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

colin

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

Colin

th3t1ck

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?

colin

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

th3t1ck

Ok. I'll give it another try.

th3t1ck

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?

th3t1ck

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.

colin

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

Colin

th3t1ck

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