CODEWITHSUNDEEP
mysql
Kenneth Whewell-Clarke
Kenneth Whewell-Clarke

Reputation: 21

SELECT Last x Rows from Table and use last index number of selection (+1) for start of next same query

I need to capture last data added to a mysql table in blocks to files every 5 mins (cron)

I need to use last index number as the beginning of next query (+1) .. and so on.

Basically I need to capture blocks of unique records to individual text files with no overlap of records.

I.E.

Index_No Forename Surname

1012 bob Smith

1013 Ann Smith

1014 Mike Hope

First run query would get a certain number of events up to 1014.

The next query would run automatically 5 minutes later (cron ?) and start with 1015 upwards.

Etc etc ... every 5 minutes.

I have looked everywhere on www for answer but not yet found one. I am looking specifically for last record from previous query (+1) up to last current entry cycling every 5 minutes.

Upvotes: 2

Views: 180

Answers (2)

Madhivanan
Madhivanan

Reputation: 13700

Have a log table with index_No and update it with the last No whenever you query. Something like this

Create table index_log(Index_no int)

Insert into index_log(Index_no)
select 1014

select columns from source_table
where index_no>(select index_no from index_log)

Update index_log
set index_no=(select max(index_no) from source_table

Upvotes: 0

Joe
Joe

Reputation: 236

I think if I was implementing this requirement, I would write a little program that runs the query and spits the output into a file. The program would also use some kind of persistant data store (text file/db/whatever) to keep track of the last record.

You can then just use cron to invoke your program.

HTH

Upvotes: 1

Related Questions