generating mysql statistics

Question

I have a csv file, which is generated weekly, and loaded into a mysql database. I need to make a report, which will include various statistics on the records imported. The first such statistic is how many records were imported.

I use PHP to interface with the database, and will be using php to generate a page showing such statistics.

However, the csv files are imported via a mysql script, quite separate from any PHP.

Is it possible to calculate the records that were imported and store the number in a different field/table, or some other way?

Adding an additional timefield to work out fields added since a certain time is not possible, as the structure of the database can not be changed.

Is there a query I can use while importing from a mysql script, or a better way to generate/count the number of imported records from within php?

Answer 1

You can get the number of records in a table using the following query.

SELECT COUNT(*) FROM tablename

So what you can do is you can count the number of records before the import and after the import and then select the difference like so.

$before_count = mysql_fetch_assoc(mysql_query("SELECT COUNT(*) AS c FROM tablename"));
// Run mysql script
$after_count = mysql_fetch_assoc(mysql_query("SELECT COUNT(*) AS c FROM tablename"));
$records_imported = $after_count['c'] - $before_count['c'];

You could do this all from the MySql script if you would like but I think using PHP to do it turns out to be a bit more clean.

Answer 2

A bit of a barrel-scraper, but depending on permissions you could edit the cron executed MySQL script to output some pre-update stats into a file using INTO OUTFILE and then parse the resultant file in PHP. You'd then have the 'before' stats and could execute the stats queries against via PHP to obtain the 'after' stats.

However, as with many of these solutions it'll be next to impossible to find updates to existing rows using this solution. (Although new rows should be trivial to detect.)

Answer 3

Not really sure what you're after, but here's a bit more detail:

1. Get MySQL to export the relevant stats to a known directory using
   SELECT... INTO OUTFILE.. This directory would need to be readable/writable by the MySQL
   user/group and your web server's user/group (or whatever user/group
   you're running PHPas if you're going to automate the cli via cron on a
   weekly basis. The file should be in CSV format and datestamped as
   "stats_export_YYYYMMDD.csv".

2. Get PHP to scan the export directory for files beginning
   "stats_export_", perhaps using the "scandir" function with a simple
   substr test. You can then add the matching filename to an array. Once
   you're run out of files, sort the array to ensure it's in date order.

3. Read the stats data from each of the files listed in the array in
   turn using fgetcsv. It would be wise to place this data into a clean
   array which also contains the relevant datestamp as extracted from the
   filename.

4. At this point you'll have a summary of the stats at the end of each
   day in an array. You can then execute the relevant stats SQL queries
   again (if so required) directly from PHP and add the stats to the data
   array.

5. Compare/contrast and output as required.

Answer 4

Load the files using PHP and 'LOAD DATA INFILE .... INTO TABLE .. ' and then get the number of imported rows using mysqli_affected_rows() (or mysql_affected_rows)