Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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?

share|improve this question

3 Answers 3

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.

share|improve this answer
    
yes, it must be from within the php app. I'm not sure of how to do anything from within php however.. –  Bill Gray Jul 12 '09 at 17:21

Use the PHP mysql_info() function immediately after you've run your LOAD DATA INFILE query. This will return a string like:

Records: 1 Deleted: 0 Skipped: 0 Warnings: 0

See php function ref: mysql_info() Related would be the mysql_affected_rows() function.

Whatever you do, don't depend upon the row count in SHOW TABLE STATUS. It can be wildly incorrect depending upon your storage engine and is really only useful for the query optimizer to make some better judgments on optimizations. This is documented in SHOW TABLE STATUS (with the specific section quoted here)

Rows

The number of rows. Some storage engines, such as MyISAM, store the exact count. For other storage engines, such as InnoDB, this value is an approximation, and may vary from the actual value by as much as 40 to 50%. In such cases, use SELECT COUNT(*) to obtain an accurate count.

The Rows value is NULL for tables in the INFORMATION_SCHEMA database.

share|improve this answer

If all you are interested is in the number of rows in specific schemas and tables (and assuming that there are no row deletions), then you can use the MySQL command show table status or from the command line use mysqlshow --status dbname. This will list for each table in the schema some extended information including row counts (or you can use like to select specific tables).

The same data can be retrieved using simple SQL from the INFORMATION_SCHEMA database, maybe like this:

SELECT Table_name,SUM(Table_rows) FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME in ('table1','table2');

You can create a simple cron job that takes this snapshot once a day and then it should be fairly easy to extract that data in a useful way from PHP. Heck, you can even store it in a database table ;-)

share|improve this answer
    
how would I extract that data from within php however? –  Bill Gray Jul 12 '09 at 17:20
    
Well, you might want to create a table to store time-based data on the tables you want to check: CREATE TABLE table_status (check_time TIMESTAMP, table_name VARCHAR(50), rowcount INT) then run a cronjob to update it daily using INSERT INTO table_status_update(table_name,rowcount) SELECT Table_name,sum(Table_rows) FROM INFORMATION_SCHEMA.PARTITIONS WHERE TABLE_NAME in ('mytable');. Then a simple select could compare each table's rowcount between the last value and the previous week. –  Guss Jul 12 '09 at 19:58
    
Is there any way to avoid doing this as a cronjob, and just retrieve the data inserted in the last 7 days? some sort of internal mysql record perhaps? –  Bill Gray Jul 12 '09 at 21:27
    
Not that I'm aware of. Theoretically you could parse the binary logs, but I wouldn't recommend it, especially not directly from a web application. –  Guss Jul 20 '09 at 14:39

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.