Link to home
Start Free TrialLog in
Avatar of Soumen Roy
Soumen RoyFlag for India

asked on

Check for a change in value in a database row on jquery button click

Hello,
I am having a problem writing the logic of a piece of code.
A jquery button click on my website calls a php script (suppose script A) using $_post, which inserts values in a new row in a database table. One of the fields is a Boolean, and is inserted as false. A PHP service runs in a separate script (suppose script B) on the server which gets data from a CURL call and upon success updates the Boolean variable to true. Now, while the Boolean field remains false, that is, till script B changes the Boolean variable to true, script A cannot perform the further actions that it is supposed to do. Upon finding that the Boolean has been changed to true, it fetches certain value from a table and returns control to the jquery function that called it.
Following is the codes that I wrote:
______________________________________________________________________________________________________________
#Script A:
include_once("phpfunctions.php");

      $dcode = $_POST['dcode'];
      $fcode = $_POST['fcode'];

      date_default_timezone_set('Asia/Kolkata');
      $date = date('YmdHis', time());

      #Insert new row

      $qry = "INSERT INTO pending_requests(device_code, feature_code, request_timestamp, pending_request) VALUES('".$dcode."', '".$fcode."', '".$date."', 1)";
      $res = mysqli_query($conn, $qry);

      #Select new created row

      $qry2 = "SELECT * from pending_requests where request_timestamp='".$date."'";
      $res2 = mysqli_query($conn, $qry2);

      $row = mysqli_fetch_array($res2, MYSQLI_BOTH);

      ini_set('max_execution_time', 150);

      #Check if value has changed

      while ($row["pending_request"] == 1) {
            continue;
      }

      $qry3 = "SELECT * from user_activity where device_code='".$dcode."' and feature_code='".$fcode."'";
      $res3 = mysqli_query($conn, $qry3);
      while ($row2 = mysqli_fetch_array($res3, MYSQLI_BOTH)) {
            continue;
      }

      $sl_no = $row2["sl_no"];

      $qry4 = "UPDATE user_activity SET request_timestamp='".$date."' where sl_no='".$sl_no."'";
      $res4 = mysqli_query($conn, $qry4);

      return $row2["message"];
      }
}
______________________________________________________________________________________________________________
#Script B:
<?php
      include_once("/inc/phpfunctions.php");

      $dcode = $_POST['dcode'];
      $fcode = $_POST['fcode'];
      $msg = $_POST['msg'];

      date_default_timezone_set('Asia/Kolkata');
      $date = date('YmdHis', time());

      $qry = "SELECT * from pending_requests where device_code='".$dcode."' and feature_code='".$fcode."' and pending_request='1'";
      $res = mysqli_query($conn, $qry);
      $row = mysqli_fetch_array($res, MYSQLI_BOTH);
      $sl_no = $row["sl_no"];

      $qry2 = "INSERT INTO user_activity(device_code, feature_code, execute_timestamp, message) VALUES('".$dcode."', '".$fcode."', '".$date."', '".$msg."')";
      $res2 = mysqli_query($conn, $qry2);

      $qry3 = "UPDATE pending_requests set pending_request=0 where sl_no='".$sl_no."'";
      $res3 = mysqli_query($conn, $qry3);
      
?>
______________________________________________________________________________________________________________

The script A creates the new row, and inserts correct values. Script B runs as expected and updates the Boolean value. However, the while loop in script A checking for the Boolean keeps executing even after I find that the value has been updated in the database.

It eventually shows "Maximum execution time of 150 seconds exceeded"..

What exactly is going wrong here and how do I solve this problem?

I hope I could frame my problem good enough to make you understand. Any help is very much welcome.

Thanks in advance.
ASKER CERTIFIED SOLUTION
Avatar of mankowitz
mankowitz
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
Avatar of Soumen Roy

ASKER

@Mankowitz.. Could you please explain a bit more? Actually I want the execution of the script to proceed beyond that point, only when $row["pending_request"] becomes 0.
Where is $row being updated in this code
while ($row["pending_request"] == 1) {
            continue;
      }

Open in new window

If $row['pending_request'] == 1 to start with that is an infinite loop - because you are never changing the value of $row['pending_request'];

This looks like a perfect opportunity to use sockets. Open a connection to the server to send the value and register a callback that is triggered in the client when script B completes.
Only when $row["pending_request"] becomes 0.
Then you have to query the database to get the updated value.
HTTP protocols do not work well with long-running jobs.  Please read this article so you have the background to understand the rest of the comments  I'll come back to this a little later today and will try to suggest some alternatives that can get close to the effect you're trying to achieve.
https://www.experts-exchange.com/articles/11271/Understanding-Client-Server-Protocols-and-Web-Applications.html
SOLUTION
Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial