CODEWITHSUNDEEP
phpjquerymysql
Victordb
Victordb

Reputation: 549

Print data from mysql

In a kiosk like application, I'm trying to print at a set interval (my case 60 secs) the last 60 secs of inserted records in a mysql database.

With the code below is there any possibility from a ajax refresh delay or anything to omit printing a record? If it is , is there anything I can do to avoid that?

   <!doctype html>
<html>
<head>
<meta charset="utf-8">
<title>Untitled Document</title>
<style>
@media print {
tr.page-break {
    display: block;
    page-break-before: always;
}
}
</style>
<script src="https://ajax.googleapis.com/ajax/libs/jquery/3.2.1/jquery.min.js"></script>
<script>
    $(document).ready(function(){
        function getData(){
           $.ajax({
                type: 'GET',
                url: 'data.php',
                success: function(data){
                    $('#output').html(data);

                             function isEmpty( el ){
          return !$.trim(el.html())
      }
      if (!isEmpty($('#output'))) {
           window.print();
      }                         
                }
            });
        }

        getData();
        setInterval(function () { getData();  }, 60000);  // it will refresh your data every 1 sec

    });
</script>
</head>

<body>
<div class="container" id="output"></div>
</body>
</html>

And the data.php :

<?php

$servername = "localhost";
$username = "root";
$password = "xxx";
$dbname = "testdb";

// Create connection
$conn = new mysqli($servername, $username, $password, $dbname);
// Check connection
if ($conn->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}

$sql = "select * from orders where time > date_sub(now(), interval 1 minute) ORDER BY id DESC";
$result = $conn->query($sql);

if ($result->num_rows > 0) {
    echo "<table>";
    // output data of each row
    while($row = $result->fetch_assoc()) {
        $data1 =$row["id"];
        $data2= $row["product"];
        $data3= $row["details"];
        echo "<tr class='page-break'>";
        echo "<td>" . $data1  . "</<td>";
        echo "<td>" . $data2  . "</td>";
        echo "<td>" . $data3  . "</td>";
        echo "</tr>";
    }
    echo "</table>";
} 
$conn->close();

?>

The code is just for testing not production so don't mind the php security issues (sql injection and so). But I would greatly appreciate if you could improve in any way the javascript part.

Is there any better solution than this to print the last 60 secs records in a mysql database?

Upvotes: 0

Views: 450

Answers (2)

Victordb
Victordb

Reputation: 549

Based on @Adder's code I ended up with this:

<script>
            $(document).ready(function () {

                $.ajax({ // Get lastID
                    type: 'GET',
                    url: 'lastid.php',
                    dataType: 'json',
                    'success': function (data) {
                        callback(data);
                    }
                });
                var return_last;
                function callback(response) {
                    return_last = response;
                    var lastID = return_last.id;




                    function getData() {
                        $.ajax({
                            type: 'GET',
                            url: 'data.php',
                            data: {lastID: lastID},
                            dataType: 'json',
                            success: function (data) {
                                lastID = data[0].id;
                                console.log(lastID);
                                $.each(data, function (i, item) {
                                    var $tr = $('<tr class="page-break">').append(
                                            $('<td>').text(item.id),
                                            $('<td>').text(item.name),
                                            $('<td>').text(item.details)
                                            ).appendTo('#output');
                                });

                                function isEmpty(el) {
                                    return !$.trim(el.html());
                                }
                                if (!isEmpty($('#output'))) {
                                    window.print();
                                }

                            }
                        });
                    }

                    getData();


                    setInterval(function () {
                        $("tr").addClass("no-print");
                        getData();
                    }, 60000);  // it will refresh your data every 1 minute
                }
            });
        </script>

And the sql 

$lastID = $_GET['lastID'];
$sql = "select * from orders where id > ".$lastID." ORDER BY id DESC";
$result = $conn->query($sql);
$dataArray = array();
if ($result->num_rows > 0) {
    while($row = $result->fetch_assoc()) {
        $dataArray[] = $row;
    }

    echo json_encode($dataArray);

}

Upvotes: 1

Adder
Adder

Reputation: 5878

I have written the following code as an example for using a lastTime variable to use in the SQL query, in order for the SQL query to continue where the last query ended.

$(document).ready(function(){
      var lastTime = 0;
      function getData(){
           $.ajax({
                type: 'GET',
                url: 'data.php',
                data: {lastTime: lastTime},
                dataType: 'json',
                success: function(data){
                    for(var i = 0; i<data.length; ++i) {
                        var row = data[i];
                        printRow('#output', row);
                        lastTime = row.time;    
                    }

                }
            });
        }

        function printRow(where, row) {
            //build html
            var html = '';
            html+= '<div class="row">';
            ... // TODO
            html+= '</div>';
            $(where).append(html);
        }

        getData();
        setInterval(function () { getData();  }, 60000);  // it will refresh your data every 1 minute

    });

Upvotes: 0

Related Questions