Here's the scenario. I have 2 PC clients (Client CA and CB) and 1 MYSQL server (S1). CA generates and stores data on S1 constantly, CB obtains data from S1 and generates a graphic with it.
CB needs to poll records from S1 all the time, that's why I have it within a while loop. How do I retrieve the last n records within the loop without getting duplicates from previous queries?. I.E.
n = 100,
CA inserts 100 records, CB gets last 100 records
CA inserts 50 records, CB gets last 100 records. 50 of these records are the same from previous query,
Please help.
Add a field to your table that tracks when the record was inserted.
Next, have the CB client track what was the most recent timestep that it has retrieved, and then limit its selects to only records that are more recent than the last time that was pulled.
I presume you store some timestamp with your data in column "create_time":
SELECT * FROM my_table WHERE create_time > time_modified_on_every_iteration ORDER BY create_time LIMIT 0, 100
And your loop:
Date lastDate = some_very_old_date;
while(true) {
rows = sql_query.getData(time_modified_on_every_iteration = lastDate);
iter = rows.iterator()
while (iter.hasNext()) {
// do stuff with current row
if(!iter.hasNext()) {
// last cycle
lastDate = iter.current.getCreate_time();
}
}
}
Apologies for not precise syntax, it's about the idea.
If you have the potential for multiple records created during the same second I would suggest using an auto incrementing id instead of a timestamp. Then, as for the timestamp examples given, you can simply query for records with an id greater than that of the last received record -
SELECT * FROM tbl WHERE id > last_received_id ORDER BY id ASC LIMIT 100;