如何实时显示我的数据库(php、js、jquery、ajax)

我想知道如何实时显示我的数据库（mysql），而不发送太多的查询到我的数据库，以免过载。我知道使用ajax是必要的，但我不知道如何正确地使用它。
现在我用jquery和load函数来做这个，你能给我你的意见，告诉我如果这不是最好的解决方案怎么做（我知道不是），谢谢！
我的问题：
如何使用php和ajax实时显示mysql数据库？例如，如果我在我的数据库中添加了一个新的订单，我想在不刷新网页的情况下显示它。
这是我的密码：

<!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页面：（connect\u 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());  
}
?>

一个带有一些查询的php页面（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;
?>

你质疑你的db是对的；那将是不必要的昂贵。相反，我建议：
使用WebSocket
最初查询所有数据
修改数据库时向客户端广播修补程序。
如果您的crud处理程序直接或间接地调度修补程序，那么这应该很容易。
例子：
客户端最初加载页面
客户端请求所有数据
客户端连接到websocket
客户端具有websocket消息处理程序，以便在收到修补程序时更新视图
服务器将所有数据发送到客户端
对db执行任何crud操作时，向客户端广播一条消息，即： { op: DELETE, rsrc: USER_TABLE, data } ( DELETE 以及 USER_TABLE 是枚举）。
在基础设施方面 / 在数据级别，您有很多选择：
缓存加载的数据或延迟加载数据
队列修补程序
使用protobufs或bson（二进制消息传递）
位图数据以减少有效负载大小
h2和ipv6支持
服务器推送传播（通过sse或websockets）、缓存、排队和延迟加载将是您的朋友。

如何实时显示我的数据库(php、js、jquery、ajax)

2条答案

相关问题

热门标签

最新问答