[RESOLVED] Thoughts and suggestions critique

[spiritssight]

Hello Al,

I am wordering if any one would like to take a look at this file and give suggestions on inproving it and if they see a better way of doing the job.

The goal is to migrate a files that are about 90mb to the database and this file is a flat fixed-width file. I have 6 of these file to do each month. over 2 million records all tgether.

MySQL version is 4.1.18

I am switching to different hosting company very soon, its going to have version 5 of MySQL


Sincerely,
Christopher
SEAS-Net


<?php

// Server Name, also can put port number like localhost.3306
$db_hostname = 'localhost1';

// Database name to logon to
$db_name = 'seasnet_db';

// Database Username to use to login
$db_username = 'user_write';

// Database Password to use to login
$db_password = '******';

$tbl_users = 'users';
$tbl_irs_eoml = 'IRS_EOML';

// include 'config.php';
// require_once( "config.php" );

// used to connect to db server and select db to use
include 'db_open.php';

// Connect to the database server
$db_conn = mysql_connect($db_hostname, $db_username, $db_password) or die ('Error connecting to mysql');

// Picks the database to use
mysql_select_db($db_name) or die ( "Unable to select database!" );


// INSERT the data into the database table
$query = "LOAD DATA INFILE '/home/httpd/vhosts/example.org/httpdocs/uploads/EO.LST'
INTO TABLE IRS_EOML
(@var1)

SET
col0 = substr(@var1,0,9),
col1 = substr(@var1,9,70),
col2 = substr(@var1,79,35),
col3 = substr(@var1,114,35),
col4 = substr(@var1,149,22),
col5 = substr(@var1,171,2),
col6 = substr(@var1,173,10),
col7 = substr(@var1,183,4),
col8 = substr(@var1,187,2),
col9 = substr(@var1,189,1),
col10 = substr(@var1,190,4),
col11 = substr(@var1,194,6),
col12 = substr(@var1,200,1),
col13 = substr(@var1,201,2),
col14 = substr(@var1,203,9),
col15 = substr(@var1,212,1),
col16 = substr(@var1,213,2),
col17 = substr(@var1,215,6),
col18 = substr(@var1,221,6),
col19 = substr(@var1,227,1),
col20 = substr(@var1,228,1),
col21 = substr(@var1,229,3),
col22 = substr(@var1,232,3),
col23 = substr(@var1,235,2),
col24 = substr(@var1,237,13),
col25 = substr(@var1,250,13),
col26 = substr(@var1,263,1),
col27 = substr(@var1,264,13),
col28 = substr(@var1,277,1),
col29 = substr(@var1,278,4),
col30 = substr(@var1,282,35),
col31 = substr(@var1,317,1)";

$result = mysql_query($query);
if(!$result) die(mysql_error());
else { $affected++; }

// printf("File inserted: %d records\n", $affected);

// closes the mysql database connection
mysql_close($db_conn);

?>

[Shrike]

I think the way you are currently doing this might actually be the quickest. Certainly having PHP load and parse the file might take just as long. Of course the faster way would be to get the file as a CSV or other structured document - that might not be possible. Have you run any metrics against this? I imagine substring on a 90mb string is rather slow :)

Presumably you get sent this file 6 times a month. To fully automate this process I would look at running the script by cron each day. How do you get hold of the file? It should be fairly trivial to let PHP handle this.

The crucial thing for me would be that it runs during the middle of the night, or at the least busy time.

[spiritssight]

Hello there,

This script would have 6 files to do once each month, a script is pulling it off the website that they are from then it will unzip the file and then upload the file to the database.

Thanks for your input and look forward to any more input