I would like to know how to display my database (mysql) in real time without sending too many queries to my database to not overload it. I know that it is necessary to use Ajax for that but I don't know how to use it correctly.
For now I use JQuery with the load function to do this, can you give me your opinion and tell me how to do if this isn't the best solution(i know it's not), thank you!
Resume of my question:
How to display a mysql database in real time using php and ajax ? For example if I add a new order in my database i want to display it without refreshing my webpage.
Here is my code:
<!DOCTYPE html>
<html>
<head>
<meta name="viewport" content="width=device-width, initial-scale=1.0">
<meta charset="utf-8">
<meta http-equiv="X-UA-Compatible" content="IE=edge">
<title>MGT Orders</title>
<link rel="stylesheet" href="https://unpkg.com/tachyons@4.10.0/css/tachyons.min.css"/>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.3.1/jquery.min.js"></script>
<style type="text/css">
section {
display: grid;
grid-template-rows: auto auto auto;
}
.container {
display: flex;
flex-wrap: wrap;
align-items: center;
}
.container > div {
flex: 100%;
}
@media all and (-ms-high-contrast: none), (-ms-high-contrast: active) {
/* IE10+ CSS styles go here */
.container > div {
width: 100%;
}
}
@supports (-ms-accelerator:true) {
/* IE10+ CSS styles go here */
.container > div {
width: 100%;
}
}
@keyframes marquee {
0% { transform: translate(0, 0); }
100% { transform: translate(0, -50%); }
}
</style>
</head>
<body class="bg-light-gray">
<section>
<article>
<div class="container">
<div class="tc fl f2-ns f3 pv2 b">Orders <span class="green">Created</span></div>
<div class="tc fl f4-ns pv2" id="ordersCreated"></div>
</div>
<div class="container bt b--black">
<div class="tc fl f2-ns f3 pt2 b ">Orders To <span class="yellow">Pick</span> </div>
<div class="tc fl f4-ns f5 ">Orders to <span class="orange">Ship</span> today in orange</div>
<div class="tc fl f4-ns f5 pb2 red">Late Orders in red</div>
<div class="tc fl f4-ns pv2 " id="ordersToPick"> </div>
</div>
<div class="container bt b--black">
<div class="tc fl f2-ns f3 pv2 b">Orders <span class="blue">Invoiced</span></div>
<div class="tc fl f4-ns pv2 " id="ordersInvoiced"> </div>
</div>
</article>
</section>
<script type="text/javascript">
setInterval('load_orders()', 500);
function load_orders() {
$('#ordersCreated').load('queryOrdersCreated.php');
$('#ordersToPick').load('queryOrdersToPick.php');
//$('#ordersToShip').load('queryOrdersToShip.php');
$('#ordersInvoiced').load('queryOrdersInvoiced.php');
if ($("body").height() > screen.height) {
$("article").css("animation", "marquee 50s linear infinite" );
}
else {
$("article").removeAttr('style');
}
if ($(document).scrollTop() > 0 ) {
$("article").removeAttr('style');
}
}
</script>
</body>
</html>
Php page for my connection to the database: (connect_db.php)
<?php
$db_name = 'warehouseproject';
$db_host = 'localhost';
$db_user = 'root';
$db_pass = '';
$conn = mysqli_connect($db_host, $db_user, $db_pass, $db_name);
if (!$conn) {
die ('Failed to connect to MySQL: ' . mysqli_connect_error());
}
?>
One of php my page with some queries (queryOrdersToPick.php):
<?php
//header("Refresh:1");
include('connect_db.php');
$sqlPick = 'SELECT * FROM `orders` WHERE `State`= "Open" AND `Reserved` > 0 AND `Invoice` = "" AND `Ship Date` > date_format(CURRENT_DATE(), "%m/%d/%Y") ORDER BY `Reserved` DESC';
$sqlPickShip = 'SELECT * FROM `orders` WHERE `State`= "Open" AND `Reserved` > 0 AND `Invoice` = "" AND `Ship Date`= date_format(CURRENT_DATE(), "%m/%d/%Y") ORDER BY `Reserved` DESC';
$sqlLatePickShip = 'SELECT * FROM `orders` WHERE `State`= "Open" AND `Reserved` > 0 AND `Invoice` = "" AND `Ship Date` < date_format(CURRENT_DATE(), "%m/%d/%Y") ORDER BY `Reserved` DESC';
$queryPick = mysqli_query($conn, $sqlPick);
$queryPickShip = mysqli_query($conn, $sqlPickShip);
$queryLatePickShip = mysqli_query($conn, $sqlLatePickShip);
if (!$queryPickShip || !$queryPick || !$queryLatePickShip) {
die ('SQL Error: ' . mysqli_error($conn));
}
$noLatePickShip = 0;
while ($rowLatePickShip = mysqli_fetch_array($queryLatePickShip))
{
echo '<div class="pa2 bg-red ba b--white br-pill"> SO <b>'.$rowLatePickShip['SO'].'</b>
<div>'.$rowLatePickShip['Reserved'].' Items</div> </div> '/*
---- Ship date: <b>'. date('m/d/Y', strtotime($rowLatePickShip['Ship Date']))
.'</b></div>'*/;
$noLatePickShip++;
}
$noPickShip = 0;
while ($rowPickShip = mysqli_fetch_array($queryPickShip))
{
echo '<div class="pa2 bg-orange ba b--white br-pill"> SO <b>'.$rowPickShip['SO'].'</b>
<div>'.$rowPickShip['Reserved'].' Items</div> </div> '/*
<b>'.$rowPickShip['Reserved'].'</b> </div> '/*
---- Ship date: <b>'. date('m/d/Y', strtotime($rowPickShip['Ship Date']))
.'</b></div>'*/;
$noPickShip++;
}
$noPick = 0;
while ($rowPick = mysqli_fetch_array($queryPick))
{
echo '<div class="pa2 bg-gold ba b--white br-pill"> SO <b>'.$rowPick['SO'].'</b>
<div>'.$rowPick['Reserved'].' Items</div> </div> '/*
<b>'.$rowPick['Reserved'].'</b> </div> '/*
---- Ship date: <b>'. date('m/d/Y', strtotime($rowPick['Ship Date']))
.'</b></div>'*/;
$noPick++;
}
$totalPick = $noLatePickShip + $noPick + $noPickShip;
?>
You could add a timestamp to the orders, modify it on update and only request the orders with a higher timestamp as your last request.
ALTER TABLE `orders`
ADD COLUMN `lastmodified` TIMESTAMP NOT NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP;
You're right to question polling your DB; that would be needlessly expensive. Instead, I recommend:
This should be easy, if your CRUD handlers dispatch, directly or indirectly, the patch.
Example:
{ op: DELETE, rsrc: USER_TABLE, data } (DELETE and USER_TABLE are enums).At the infrastructure / data level, you have a lot of options:
Server push propagation (via SSE or WebSockets), caching, queueing, and lazy-loading will be your friends for this.