I guess the only was you will understand what is going on with thi squery is if explain what I am doing. Right I work with a corporation and we are changing over from dBase to MySQL through the master backup files, which are in a fixed length text file.
We get all of the values out of the text file but sometimes at the end of the text file windows inserts that damn square block at the bottom and that adds a new record to the db. So when this happens and we have two text files that go together we have to tell the next record to skip a certain amount so that there is not double key
Kinda understand now??
That insert is in the middle of a for loop for the number of records in the text file.
I have included the script so you can see what I mean.
// Connect to MySQL
$link = mysql_connect("somehost","someuser","somepass");
$db = mysql_select_db("somedb",$link);
$sql_dropnbamdf = "DROP TABLE IF EXISTS nbamdf";
mysql_query($sql_dropnbamdf)
or die(mysql_error());
$sql_createnbamdf = "CREATE TABLE nbamdf (
id_nbamdf int(10) NOT NULL AUTO_INCREMENT PRIMARY KEY,
ssn char(11),
last_name char(40),
name_mi char(15),
nick_name char(10),
suffix char(3),
email char(60),
paycode char(1),
pay_to char(10),
ga_num char(10),
split char(3),
pc2 char(1),
vendor char(1),
acct_no char(20),
comm_ytd char(10),
lchck_date DATE,
appt_date DATE,
up_date DATE,
lapp_date DATE,
odf char(5))";
mysql_query($sql_createnbamdf)
or die(mysql_error());
//########################################################################################
// Select file from Xplosion and load into new table
//########################################################################################
$file_name = "somepath/CVAMDF.TXT";
$fields = file( $file_name );
if ($fields) {
// Initialize array for record parsing
$dbFields = Array();
// Length of each field in the record
$fieldLength = Array( 10,11,40,15,10,3,60,1,10,10,3,1,1,20,10,8,8,8,8,5 );
// Loop through each record and separate into an array to be inserted into mysql
foreach( $fields as $row )
{
for( $i=0; $i<count($fieldLength); $i++ )
{
$dbFields[$i] = ereg_replace( "'", " ", substr( $row, 0, $fieldLength[$i] ) );
$row = substr( $row, $fieldLength[$i] );
}
$insertRecord = "INSERT INTO nbamdf SET
id_nbamdf = '$dbFields[0]',
ssn = '$dbFields[1]',
last_name = '$dbFields[2]',
name_mi = '$dbFields[3]',
nick_name = '$dbFields[4]',
suffix = '$dbFields[5]',
email = '$dbFields[6]',
paycode = '$dbFields[7]',
pay_to = '$dbFields[8]',
ga_num = '$dbFields[9]',
split = '$dbFields[10]',
pc2 = '$dbFields[11]',
vendor = '$dbFields[12]',
acct_no = '$dbFields[13]',
comm_ytd = '$dbFields[14]',
lchck_date = '$dbFields[15]',
appt_date = '$dbFields[16]',
up_date = '$dbFields[17]',
lapp_date = '$dbFields[18]',
odf = '$dbFields[19]'";
// Perform Query from INSERT Statement
mysql_query($insertRecord, $link)
or die(mysql_error($link));
}
}
else {
echo "The file cannot be found at the specified location or is does not exist.";
}
// SSN Index
mysql_query("CREATE INDEX ssn ON nbamdf (ssn)")
or die(mysql_error());
// Last Name Index
mysql_query("CREATE INDEX last_name ON nbamdf (last_name)")
or die(mysql_error());
// Full Name Index
mysql_query("CREATE INDEX full_name ON nbamdf (last_name,name_mi)")
or die(mysql_error());
