I have a cron script that runs daily to LOAD DATA INFILE into a MySQL database. I would like to, using PHP as part of a web application, generate a weekly report showing the total number of inserted records from the last 7 days.
I would like to know the best way to go about this. Patching or modifying MySQL is not an option, and changing the way data is inserted is not either. I would prefer to avoid counting the CSV files before inserting, as it would be messy to read this data bac into PHP.
Suggestions?
Best Answer
Must it be inside the PHP app? Inside your cron script, you could count the numbers of rows to insert, and add them either to a logfile or into a small log table inside the mysql db (unless that is what you mean with modifiying mysql). I am not sure, but I guess the myql function row_count() will work with INFILE data as well, so counting the numbers would be easy.
And, maybe the simplest method, if the rows are not modified after inserting, you could add a timestamp column to the database, which gets automatically set to the date of the insert and count() up the numbers for a week.