0

I want to append new data once if it was added on sql in last 20 second.

So I used below script in my wall.php page.

Now in my server.php file query I created as

$timestamp = date("Y-m-d H:i:s", time() - 20);
$results = mysqli_query($dbh,"SELECT * FROM comments_lite WHERE qazi_id='1012' AND `date` > '".$timestamp."' ORDER BY date DESC LIMIT 1") or die(mysqli_error($dbh));

I also tried which not work:

WHERE qazi_id='1012' AND `date` >= NOW() - INTERVAL 20 SECOND ORDER BY date DESC LIMIT 1

My sql date format is 2015-02-25 19:45:28

But my query get same data in case of I used $timestamp one after one which above 5+ minute also.

If the post occurred time is 20:20:00 and my query's current time is 20:20:10, it will be display the post. but if my query's current time is 20:20:30 (more then 20 second from post occurred time) , it will not display anything.

Please give me a guideline to solved it " append new data once if added on sql in last 20 second ".

My full code here below:

js:

    <script type="text/javascript" charset="utf-8">
function addmsg(type, msg){
    $("#messages").append(
        "<div class='msg "+ type +"'>"+ msg +"</div>"
    );
}

function waitForMsg(){

    $.ajax({
        type: "GET",
        url: "/server/server.php",

        async: true, 
        cache: false,
        timeout:50000, 

        success: function(data){ 
            addmsg("new", data); 
            setTimeout(
                waitForMsg, 
                1000 
            );
        },
        error: function(XMLHttpRequest, textStatus, errorThrown){
            setTimeout(
                waitForMsg, 
                15000); 
        }
    });
};

$(document).ready(function(){
    waitForMsg(); 
});
</script>

Server.php

include("../db.php");
global $dbh;
header('Content-Type: application/json; charset=utf-8');
while (true) {
//fetch data
$timestamp = date("Y-m-d H:i:s", time() - 20);
$results = mysqli_query($dbh,"SELECT * FROM comments_lite WHERE qazi_id='1012' AND `date` > '".$timestamp."' ORDER BY date DESC LIMIT 1") or die(mysqli_error($dbh));
$rows =  mysqli_fetch_assoc($results);
$data = $rows['description'];

//has data
if (!empty($data)) {
    echo json_encode($data);
    flush();
    exit(0);
}
sleep(5);
}
joy
  • 81
  • 9
  • 1
    Please explain `found in last 20 second`. It sounds like you run a continuous search, and you want all the results found in the past 20 seconds, which sounds a bit odd. Another explanation would be "Launch a search in MySQL, stop after 20 seconds and return what you found" but it's a bit strange as well. Could you clarify this? – Jeremy Thille Feb 24 '15 at 11:53
  • It will not work with your date format. Use datetime column type. – Marek Feb 24 '15 at 11:58
  • @Jeremy Thille update above. Thank u. – joy Feb 24 '15 at 12:00
  • So from what I understand, you want to fetch what have been posted in the last 20 seconds (what is not older than 20 seconds in the database). I think you want to use `SELECT DATE (NOW() - INTERVAL 20 SECOND )` http://stackoverflow.com/questions/4123036/how-to-subtract-time-in-mysql – Jeremy Thille Feb 24 '15 at 12:05
  • Is it right: `WHERE qazi_id='1012' AND `date` (NOW() - INTERVAL 20 SECOND ) ORDER BY date DESC LIMIT 1` ? – joy Feb 24 '15 at 12:22
  • @koch You compare `'Feb 24, 15; 5:26 pm' > '2015-02-24 17:26:40'`. You don't even have seconds in database, how do you want to achieve 20 second precision? – Marek Feb 24 '15 at 16:24
  • @Marek Thank u sir. 'Feb 24, 15; 5:26 pm' also display with every post of my Clint so if I remain it in the same format how to make my query to get data added in last 20 second. – joy Feb 24 '15 at 16:42
  • @koch Format dates when they are displayed, never store them formatted in database. Now you have to fix this situation: create new datetime column, update it using `strtotime('Feb 24, 15 5:26 pm')` (only `;` needs to be removed), drop the old column, fix your code. – Marek Feb 24 '15 at 17:10
  • After update my datebase format as you said, I used `date >= '".$timestamp."' ` and then `date >= NOW() - INTERVAL 30 SECOND` also. which not work sir. – joy Feb 24 '15 at 17:37

1 Answers1

0

Try this way:

$datetime = date('Y-m-d H:i:s', strtotime('-15 second'));
$results = mysqli_query($dbh,"SELECT * FROM comment WHERE qazi_id='1012' AND date >= '$datetime' ORDER BY date DESC LIMIT 1") or die(mysqli_error($dbh));
koc
  • 955
  • 8
  • 26