MySQL Database & Scheduled CSV Imports?

Hey! What's the context behind your automatic upload? i.e. where's the file coming from etc..

Ah okay, well I'm not pro, I've only worked with the manual uploads you mentioned.

So if you have a specific folder the upload is sent to, maybe you could create a function that will search for new files inside that folder? Make sure you add a lot of validation too, i.e. if the file name increments by 1 for each upload, you could write the function to only work with the next file/increment. The trouble is.. php would only kick on when a page is requested. You could run the 'check folder and upload' function on every page request - that seems a little messy, but it would work.

Maybe this might help http://au2.php.net/manual/en/function.file-exists.php

Another point of validation - you could receive the file into a temporary folder, check it - and only if it's acceptable, move it into the validated folder and continue with the overwrite: http://www.php.net/manual/en/function.move-uploaded-file.php

You said you've looked into it already, but I feel I should suggest anyway: Make sure you use PDO and a try-catch block because it protects you against SQL injections.. Other php mysql functions (i think) are now considered pretty dated and insecure.

If it helps, here's the block I started with where the $files param is the $_FILES array.

As I said, I'm not 100%, just throwing information at you :-p

Database file

<?php

try {
    $db = new PDO("mysql:host=" . DB_HOST . ";dbname=" . DB_NAME . ";port=" . DB_PORT,DB_USER,DB_PASS, 
                                       array(
                                        PDO::MYSQL_ATTR_LOCAL_INFILE => true,
                                        PDO::ATTR_ERRMODE => PDO::ERRMODE_EXCEPTION
                                       )
                                  );
    $db->setAttribute(PDO::ATTR_ERRMODE,PDO::ERRMODE_EXCEPTION);
    $db->exec("SET NAMES 'utf8'");
} catch (Exception $e) {
    echo "Could not connect to the database.";
    exit;
}

Function

<?php
function add_csv_file_to_db($files) {

    $valid = validate_csv_upload($_FILES);

    if ($valid) {
        $name = $files['file']['name'];
        $file = ROOT_PATH . 'admin/uploads/' . $name;   
        $fieldseparator = ","; 
        $enclosedseparator = '"';
        $escapedseparator = '\\';
        $lineseparator = "\n";
        $databasetable = "price_import";


        require(ROOT_PATH . "inc/database.php") ; //attempt connection if valid

        try {
            $affectedRows = $db->exec("
            LOAD DATA LOCAL INFILE ".$db->quote($file)."REPLACE INTO TABLE `" . $databasetable . "`
            FIELDS TERMINATED BY ".$db->quote($fieldseparator)."
            ENCLOSED BY ".$db->quote($enclosedseparator)."
            ESCAPED BY ".$db->quote($escapedseparator)."
            LINES TERMINATED BY ".$db->quote($lineseparator));
        } catch (Exception $e) {
            var_dump($db->errorInfo()); //debugging only, make sure you remove this if live.
//          echo 'there was an error';
            exit;   
        }
        $success_message[] = "Loaded a total of $affectedRows records from this csv file.\n";
        setSuccessMessage($success_message); //sets session var
        unlink($file); //deletes file
        return true;
    } else {
        return false;
    }
}
?>

Haha, sorry man. The try section of the

add_csv_file_to_db($files)

Is just the same as if you're adding the csv through phpMyAdmin (again my apologies if you already know all this!).

In phpMyAdmin, click on any table in any database. Then click import at the top. Add a dummy file - it doesn't matter what - just not a csv file because you want it to fail!! Scroll down and select CSV (USING LOAD DATA). The page should re-render to show boxes for 'enclosed by' and 'escaped by' etc. Leave them as they are for this test and hit 'go'. You should get a nice big error message, but also the SQL code. This code might help you decrypt the try block.

Good luck.. It sounds pretty tricky ;)