Uploading a CSV Data Import via PHP & MySql

Ridgejp
Ridgejp used Ask the Experts™
on
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
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®
$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
Most Valuable Expert 2011
Top Expert 2016
Commented:
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

Most Valuable Expert 2017
Distinguished Expert 2018
Commented:
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

Do more with

Expert Office
Submit tech questions to Ask the Experts™ at any time to receive solutions, advice, and new ideas from leading industry professionals.

Start 7-Day Free Trial