AJAX - Return only new mySQL entries.

[clbembry]

I have a mySQL that is continuously being added to and I need to make an AJAX function that checks the database periodically and only returns the results that it hasn't seen before.

[tgreer]

Define "returns results that it hasn't seen before". Surely you don't want to try to store the results of every query, per session, and do a comparison in real time. You want to do this work at the database level. You should add a timestamp field or some other field to use as a flag and build the proper SELECT query to return the results you want.

[diafol]

Have a status field that shows 0 when added. Change to 1 when retrieved. So on new ajax request ask for all records that are 0 and then immediately change them to 1. There's an outside chance that you'll miss an entry between two queries so if you're worried about this, query for the max id no of status = 0 so only the ones retrieved with Id <= max id are set status=1.

[tgreer]

Again, do this work at the database level. Don't have a select query followed by an update query, but rather construct a stored procedure that does what you need.

[diafol]

Interesting. You'd still need to run update followed by select?? Using a var from one to the next or lock tables?? How'd you go about it?

[cjohnweb]

I think what he is saying is that you should add a timestamp field to each entry in the database.

The script that retrieves results could do something like this:

<?php

//connect to db

$gr = mysql_query("SELECT * FROM table WHERE timestamp ORDER BY timestamp DESC LIMIT 10"); // get results, limit to 10 results max, modify this to meet your needs

$cgr = mysql_num_rows($gr); // count get results

if($cgr > 0){
while($agr = mysql_fetch_array($gr)){ // array get results
extract($age); // id_column, other_column

// check if results have already been displayed
foreach($_SESSION['last_results'] as $k => $v){

foreach($v as $kk => $vv){


//If the ID of this SQL entry already exists in session...
if($kk == $id_column){
//results already exist in the session...don't add new entries

}else{
//results have not been added to session yet, so add it 
$_SESSION['last_results'][][$id_column] = "$other_column"; // store ID and data into an array
}

}

}

}

}

?>

You can display the results on any other page with the same 2 foreach loops:

<?php
echo "<ul>";
foreach($_SESSION['last_results'] as $k => $v){
foreach($v as $kk => $vv){

//$kk contains the MySQL column ID in this example
echo "<li>$vv</li>"

}

}
echo "</ul>";
?>

I haven't tested the code, but should be good to go for the most part.

[diafol]

No. I understand a datetime solution. I was wondering about a select/update solution. Following a trawl I couldn't find a one statenent solution. But as you say he was probably referring to a datetime.

[tgreer]

No... what I was suggesting is to create a stored procedure which will select the records you want, then update those records. If you do a web search for "stored procedure select update" you'll find several discussions and examples.

[Brillig]

If it should be session dependent (you want "new" defined as what's been seen in this browser session) then declare a javascript variable outside of a function and set it to the mas unique id returned form each call and then pass it back in each time so the sql knows where to start.

This will start back at the beginning for another user or if the user refreshes.

For it to work globally, which is to say that if you view the new records then no one else will see them, then the solution is the store procedure that tgreer mentions above. There are multiple ways to do this, from marking read records as "read" with a bit field to storing the maximum unique Id retrieved in another table and updating.