Query returns old data

I have a mysql query that counts how many orders have been made on my website: SELECT COUNT(`orderid`) FROM `orders` WHERE `completed` = 1

It works fine, but only when you refresh the page. If someone makes an order from another browser, i want the order to appear without having to refresh/reload the page.

I think i almost solved the problem, except the query returns old values. The query returns the amount of orders just fine, but if new orders are added it does not return the new amount of orders, just the old one. I suspect this is due to the databases isolation level being REPEATABLE-READ. Is there a way to work around this?

Answer

If you don’t want to reload the page and get acualized data you will have to use javascript with ajax calls.

You have to include a js script in your html that fetch a php page every x seconds to get the actual amount of orders and replace the value in the browser. This php page job will be only to display this number.

Ajax calls are executed after the browser get the page from the server, they are executed by the browser. As soon as PHP server send the page, it care no more, the only way to interact with it is by the client browser, in this exemple with an Ajax call to an url on the server that will repond only with the total amount of order. So in background of the browser it will require actualized datas to the PHP server.

---

To be honest JSON is probably the way you should go to be able to send total amount of order + all kind of other actualized infos to include in your page.

It would give you for exemple something like that {totalOrder = 142, lastConnection = "Jean-Philippe", LastOrderDate= "2020-11-18"} if you encode this array on the PHP server side:

$result = [ "totalOrder " => 142, 
            "lastConnection " => "Jean-Philippe", 
            "LastOrderDate"=> "2020-11-18"];
header('Content-Type: application/json');
echo json_encode($result);

Then you can easily replace content in your HTML page in javascript with this JSON response from your PHP server.

See detailed exemple here :https://makitweb.com/return-json-response-ajax-using-jquery-php/

---

Have a nice day.