I am trying to do a bulk upload of a CSV file which contains somewhere around 80,000 records.
So optimized programming is very important here.
The data in the records need to go to two tables. - Table A and Table B.
At first,the user selects the file and upload it to the server using move_uploaded_file() . A sample of the file will be shown
in a grid . Above each column I place a drop down box which contains all the column headings of the table and the user needs to select which column in the grid goes to which column in the database.
Once he clicks the upload button, the data needs to go for insertion. I was able to do the insertion correctly without any issues. But the problem is with the performance. To upload a file of 15000, it took lot of time (after 300 sec, it still only inserted 3000 records). The code I have written is as shown below. The program tries to read each line from the file and then dynamically created the query string and then tries to insert them.
Code: Select all
$dbcon = & new DBConnection(); // DB connection is a class written by me
$dbcon->connectdb('localhost','3306','root','1234,0);
$dbcon->selectdb('emaildb',0);
while( false != ( $cell = $reader->next() ) )
{
if(!$dbcon->insertRecord($insertTable1))
echo "<td>". mysql_error(). "</td>\n";
else
{ // Now insert the record to second table
$query = insert into table2(mysql_insert_id(),....);
if(!$dbcon->insertRecord($query))
echo "<td>". mysql_error(). "</td>\n";
else
{
echo "<td bgcolor='".( ( $line % 2 ) ==0 ? '#efefef' : '#ffffff' )."'>Insertion success</td>\n";
echo "</tr>\n";
}
}
mysql_close($dbcon);
} // End of while loopI will be running the web server in Fedora Linux OS.
Thanks in advance