Ajax call facing an awkward situation

Question

Please bare me for long question...

I have to add one database record to one master table let say test table. After adding record to this table I want its last inserted id and then want to add approximate 1000 entries into transaction table (also sending EMAILS to these 1000 records) let say test_transaction table.

But this second operation, adding record into transaction table, takes more second to add records to database. I am using customer MVC and my code looks like...

public function addtotest()
{
    $strSql = "INSERT INTO test ...";
    $intId = $this->db->execute($strSql);

    foreach($arrTransaction $transact)
    {
        $strSql = "INSERT INTO test_transaction ...";
        $this->db->execute($strSql);
    }

    echo $intId;
}

So I want to put test_transaction table's entries into another action like below

public function testtrancation()
{
    $id = $_REQUEST['id']; //It's understood that I am doing proper validation here
    foreach($arrTransaction $transact)
    {
        $strSql = "INSERT INTO test_transaction ...";
        $this->db->execute($strSql);
    }
}

So main question, I am calling addtotest action from an AJAX and when it will give id in response I want to call another AJAX which will add approximate 1000 records to database.

BUT I do not want to disturb users' experience by letting them to wait for another few minutes for 1000 records.

1. So I am reloading page after record has been added by addtotest `AJAX' call.
2. Calling another AJAX call in success of addtotest.

3. And after calling testtransaction AJAX call, I am reloading my page.

   $.ajax(
   {
       url: SERVER_PATH + 'addtotest',
       type: 'post',
       async: true,
       data: { required_data: required_date },
       success:function(data)
       {
           alert('Record added successfully.');
   
           //I DO NOT WANT USERS TO WAIT FOR THIS AJAX CALL
           $.ajax(
           {
               url: SERVER_PATH + 'testtransaction',
               type: 'post',
               async: true,
               data: { intId: data.id },
               success:function(data)
               {
               //DO NOTHING
           },
           });
           //I DO NOT WANT USERS TO WAIT FOR THIS AJAX CALL
   
           location.reload();
       },
   });    
   

But as page get reloaded my AJAX call is also get vanished without completing AJAX request.

So could be done in this case? I you want more details then please let me know.

I do not want users to wait for another 1000 entries (plus sending EMAILS to these 1000 records) being made into database.

Answer 1

Just Create a Database Trigger then. So When Record Gets inserted in First Table it inserts into transaction table or SO.

Exampl -

    CREATE TRIGGER trg_Table1_INSERT
 ON dbo.Table1 AFTER INSERT 
AS BEGIN
   INSERT INTO dbo.Table2(SerialNo, Name)
  SELECT SerialNo, Name
  FROM Inserted

   INSERT INTO dbo.Table3(SomeOtherCol)
  SELECT SomeOtherCol
  FROM Inserted
END

Answer 2

This is simple!

After single insert you can call to separate background process:

public function addtotest()
{
    $strSql = "INSERT INTO test ...";
    $intId = $this->db->execute($strSql);
    exec('php /your/php/cli/script.php param1 param2 paramN 1>> /dev/null 2>> /dev/null &');
    echo $intId;
}

With "&" process starts in the background, so you can continue and do not have to wait until the script is finished.

Or you can add this job into "deferred tasks" and run this tasks with cron.

Answer 3

Don't use AJAX for that. You need CRONJOB with basic pagination.

In one php file extract ID and after that list sequence per 500 request for each sequence. And with header() redirect on the next 500 until you reach end. That will not affect on user experience and ypu will have good performances.

Answer 4

You can insert all the rows with one sql query.

INSERT INTO test_transaction (col1, col2)
VALUES (value11, value12),
(value21, value22),
(value31, value32),
...
(value10001, value10002);