Repeat MySql query until result is found (with delay)

I am building a PHP webshop, where on the thank-you-page of the shopping process the results of the payment status/cancellation might not yet be sent from the payment service provider.

So, when retrieving that status from SHOP_PAYMENTS, the updated record might not be there yet.

The query is simple:

$check_status = "SELECT * FROM SHOP_PAYMENTS WHERE order_id = '$orderID' AND status <> ''";

When no result on status (we have the order_id), I’d like to repeat it with a delay: say every 2 seconds with a max of 3 times repeat.

And, of course, when any status is found, break and give the results.

I know SELECT REPEAT() exists (and tried it), but it just fires it 3 times in a row.

Is there any other way to accomplish this?

Answers:

Thank you for visiting the Q&A section on Magenaut. Please note that all the answers may not help you solve the issue immediately. So please treat them as advisements. If you found the post helpful (or not), leave a comment & I’ll get back to you as soon as possible.

Method 1

One approach is to use a do ... while() statement to iterate over and execute the query, delaying the next iteration by using sleep() or for greater accuracy usleep().

However, it would be more advantageous to have a message queue send and determine the status from the payment gateway, prior to moving forward with a response to the client.

The below example assumes that mysqli is configured with MYSQLI_REPORT_STRICT, instead of checking for true/false

Additionally, status must not be nullable, otherwise a condition of status IS NOT NULL AND status != "" is needed to check for null or an empty string.

$i = 0;
do {
    $stmt = $mysqli->prepare("SELECT `status` FROM `SHOP_PAYMENTS` WHERE `order_id` = ? AND `status` != ''");
    $stmt->bind_param('i', $id);
    $id = $orderID; //bind the order_id param to the value of $orderID 
    $stmt->execute();
    $stmt->bind_result($status);
    $stmt->fetch();
    $stmt->close(); //important - mysqli may not unlock the results unless the prepared statement is closed and recreated
    if (!$status) {
        //delay next iteration by two seconds if a status is not found
        sleep(2);
    }
//stop checking if there is a status or iterations exceeds 2
} while (!$status && $i++ < 2);
if ($status) {
   //... do something
}

PDO + Sqlite Example demonstrating the repeated query with delay
https://3v4l.org/IFc2i


All methods was sourced from stackoverflow.com or stackexchange.com, is licensed under cc by-sa 2.5, cc by-sa 3.0 and cc by-sa 4.0

0 0 votes
Article Rating
Subscribe
Notify of
guest

0 Comments
Inline Feedbacks
View all comments
0
Would love your thoughts, please comment.x
()
x