Uploading a CSV Data Import via PHP & MySql

Hi,

I'm using the code below to upload a CSV Data import directly into my website. I've written this to alleviate the need to process the import via phpMyAdmin using the csv import - so that others can upload the files without the need to back-room access.

However, I'm finding that my import routine has a sensitivity to 'special characters' in php so for example if one of the records contained a delivery from "St John's Ambulance" the ' in the wording "John's" would cause the whole row to be rejected. This is also true for trialling spaces and the following characters " ' \ ` / ( ) +.

How, do I dial down the sensitivity in this regard for field that maybe contain such characters or should it be this way - I know that in phpMyAdmin you can always check the box that states "Do Not Abort on Insert Error" which over-rides these errors.

Can you do the same in the import file?

$file = $_FILES['file']['tmp_name'];
        
        $handle = fopen($file,"r");
        // fgets = misses the header row.
        fgets($handle);
        
        while(($fileop = fgetcsv($handle,10000,",")) !== false)
        
            {
            
            $dateOfInvoice          = $fileop[0];
            $dateOfPurchaseOrder    = $fileop[1];
            $poNumber          		= $fileop[2];
            $supplier               = $fileop[3];
            $supplierNumber         = $fileop[4];
            $dateOfBookingIn        = $fileop[5];
            $SKU             		= $fileop[6];
            $supplierPCode          = $fileop[7];
            $supplierDesc           = $fileop[8];
            $unitsRequired          = $fileop[9];
            $cartonsSize            = $fileop[10];
            $cartonsRequired        = $fileop[11];
            $qtyReceived            = $fileop[12];
            $receiptedBy            = $fileop[13];
            $dateSysUpdated         = $fileop[14];
            $comments             	= $fileop[15];
                
            $query1 = "INSERT into goodsIn 
                                        
                            (    dateOfInvoice
                                ,dateOfPurchaseOrder
                                ,poNumber
                                ,supplier
                                ,supplierNumber
                                ,dateOfBookingIn
                                ,SKU
                                ,supplierPCode
                                ,supplierDesc
                                ,unitsRequired
                                ,cartonsSize
                                ,cartonsRequired
                                ,qtyReceived
                                ,receiptedBy
                                ,dateSysUpdated
                                ,comments
                                
                            ) 
                                            
                                VALUES
                                            
                            (  '$dateOfInvoice'
                              ,'$dateOfPurchaseOrder'
                              ,'$poNumber'
                              ,'$supplier'
                              ,'$supplierNumber'
                              ,'$dateOfBookingIn'
                              ,'$SKU'
                              ,'$supplierPCode'
                              ,'$supplierDesc'
                              ,'$unitsRequired'
                              ,'$cartonsSize'
                              ,'$cartonsRequired'
                              ,'$qtyReceived'
                              ,'$receiptedBy'
                              ,'$dateSysUpdated'
                              ,'$comments'

                            )";

            $result = $mysqli->query($query1);
                        
            }

            unset($fileop);

Open in new window


J
RidgejpManaging DirectorAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Peos JohnPHPCommented:
$value = "St John's Ambulance";

$value = $mysqli->real_escape_string($value);

Open in new window


Use escape string to make it valid entry to database

http://php.net/manual/en/mysqli.real-escape-string.php
1
Ray PaseurCommented:
You can probably iterate over the $fileop array and escape everything.
while(($fileop = fgetcsv($handle,10000,",")) !== false)
{
    foreach ($fileop as $key => $value)
    {
        $fileop[$key] = $mysqli->real_escape_string($value);
    }
    
    /** Rest of the processing here...
}

Open in new window

0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Julian HansenCommented:
Have you had a look at the MySQL LOAD DATA INFILE

Specifically for high speed loading of text data into a MySQL database.

If that does not work are you aware that you can write multiple records in a single insert

INSERT INTO table (field1, field2) VALUES (1,2), (3,4),(5,6) ....(1000,1001);

Open in new window


In other words you set the INSERT INTO part once and then write multiple value blocks after that. If you have a really large set of data you an batch this
[NB Concept code only - not tested]
// DO 100 RECORDS AT A TIME
define('MAXRECORDS',100);
$prefix = "INSERT INTO table (field1, field2) VALUES";
$query = $prefix;
$recordcount = 1;
while(($fileop = fgetcsv($handle,10000,",")) !== false) {
    // IF YOUR DATA IS TRUSTED YOU CAN SELECTIVELY APPLY
    // real_escape_string() TO FIELDS THAT COULD CONTAIN SPECIAL CHARS
    $dateOfInvoice  = $mysqli->real_escape_string($fileop[0]);
    $dateOfPurchaseOrder = $mysqli->real_escape_string($fileop[1]);

    $query .= <<< QUERY
        ('{$dateOfInvoice}','{$dateOfPurchaseOrder}'),
QUERY;

    if ($recordcount++ % MAXRECORDS == 0) {
        $query = trim($query, ',');
        $result = $mysqli->query($query);
        $query = $prefix;
    }
}

// Send any stragglers through
if ($recordcount % 100 > 1) {
    $query = trim($query, ',');
    $result = $mysqli->query($query);
}

Open in new window

0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
PHP

From novice to tech pro — start learning today.