Best way to display more of 800.000 lines on web page-CodePudding

I have to display a table that counts more than 800,000 lines on my PHP page, but I don't know what is the best way to do that because if I follow the classic method, the loading is too long.

I would like to load a number of pages that permit a good flow of loading pages. What do you suggest?

The page is a simple table where I get the results with a fetch_assoc.

And that's the code

<tbody>
                <?php
                $qry = $conn->query("SELECT en.id_order, en.reference, en.total_paid, en.date_add, s.identificativo_ordine, en.id_cart
                from develettronw.ww_ps_orders en 
                inner join smarty.orders s on en.id_order = s.id_ps where en.id_shop = 1  
                order by en.date_add desc LIMIT 0 , 1000 ");
                $i = 0;
                while($row= $qry->fetch_assoc()):
                    $i   ;
                ?>
                <tr>
                    <td >
                        <?php 
                            echo "<b>".$row['date_add'] . "</b><br>";
                            echo "ID PRESTA : <b>" .  $row['id_order'] . "</b><br>";
                            echo "ID : <b>" .  $row['identificativo_ordine'] . "</b><br>";
                            echo "REFERENZA: <b>" .  $row['reference'] . "</b><br>";
                        ?>
                    </td>
                    <td><b>VUOTO</b></td>
                    <td><b><?php /*$qryProduct = $conn->query("SELECT pr.name, pr.id_product,krt.quantity
                        from develettronw.ww_ps_product_lang pr 
                        inner join develettronw.ww_ps_cart_product krt on pr.id_product = krt.id_product
                        inner join smarty.orders a on krt.id_cart = a.id_cart
                        WHERE pr.id_lang = 1 AND a.id_cart = ".$row['id_cart'] ); 
                while($row2= $qryProduct->fetch_assoc()){echo "c";}
                    echo "c";*/
                ?></b></td>
                    <td><b>VUOTO</b></td>
                    <td><b>VUOTO</b></td>
                    <td><b>VUOTO</b></td>
                    <td><b><?php echo (number_format($row['total_paid'],2,'.')) ?> €</b></td>

                </tr>   
            <?php endwhile; var_dump($i); ?>
            </tbody>

The result of my request contain more than 800,000 results.

CodePudding user response：

Use a pagination approach to handle lines from server in php.

CodePudding user response：

I recommend jQuery DataTables to you because it'll help you to paginate the records, and can use it like so

<!DOCTYPE html>
<html lang="en">

<head>
    <meta charset="UTF-8">
    <meta http-equiv="X-UA-Compatible" content="IE=edge">
    <meta name="viewport" content="width=device-width, initial-scale=1.0">
    <title>Orders</title>
    <link rel="stylesheet" href="//cdn.datatables.net/1.11.5/css/jquery.dataTables.min.css">
</head>

<body>
    <table id="myTable">
        <thead>
            <tr>
                <th scope="col">#</th>
                <th scope="col">First</th>
                <th scope="col">Last</th>
                <th scope="col">Handle</th>
                <th scope="col">...</th>
            </tr>
        </thead>
        <tbody>
            <?php
            $qry = $conn->query("SELECT en.id_order, en.reference, en.total_paid, en.date_add, s.identificativo_ordine, en.id_cart
                from develettronw.ww_ps_orders en 
                inner join smarty.orders s on en.id_order = s.id_ps where en.id_shop = 1  
                order by en.date_add desc LIMIT 0 , 1000 ");
            $i = 0;
            while ($row = $qry->fetch_assoc()) :
                $i  ;
            ?>
                <tr>
                    <td >
                        <?php
                        echo "<b>" . $row['date_add'] . "</b><br>";
                        echo "ID PRESTA : <b>" .  $row['id_order'] . "</b><br>";
                        echo "ID : <b>" .  $row['identificativo_ordine'] . "</b><br>";
                        echo "REFERENZA: <b>" .  $row['reference'] . "</b><br>";
                        ?>
                    </td>
                    <td><b>VUOTO</b></td>
                    <td><b><?php /*$qryProduct = $conn->query("SELECT pr.name, pr.id_product,krt.quantity
                        from develettronw.ww_ps_product_lang pr 
                        inner join develettronw.ww_ps_cart_product krt on pr.id_product = krt.id_product
                        inner join smarty.orders a on krt.id_cart = a.id_cart
                        WHERE pr.id_lang = 1 AND a.id_cart = ".$row['id_cart'] ); 
                while($row2= $qryProduct->fetch_assoc()){echo "c";}
                    echo "c";*/
                            ?></b></td>
                    <td><b>VUOTO</b></td>
                    <td><b>VUOTO</b></td>
                    <td><b>VUOTO</b></td>
                    <td><b><?php echo (number_format($row['total_paid'], 2, '.')) ?> €</b></td>

                </tr>
            <?php endwhile;
            var_dump($i); ?>
        </tbody>
    </table>

    <script src="https://code.jquery.com/jquery-3.6.0.slim.min.js"></script>
    <script src="https://cdn.datatables.net/1.11.5/js/jquery.dataTables.min.js"></script>
    <script>
        $(document).ready(function() {
            $('#myTable').DataTable();
        });
    </script>
</body>

</html>