LOAD DATA LOCAL INFILE PHP EXAMPLES

//DB table structure:

//CREATE TABLE IF NOT EXISTS `test` (
//  `test1` varchar(255) NOT NULL,
//  `test2` varchar(255) NOT NULL
//) ENGINE=MyISAM DEFAULT CHARSET=utf8;

//test.txt:
//qwe-rty:qaz-wsx:poi-uyt:plm-koi

// mysqli example

<?php

    $mysqli = mysqli_init();
    mysqli_options($mysqli, MYSQLI_OPT_LOCAL_INFILE, true);
    mysqli_real_connect($mysqli,'localhost','user','passwd','test');

    if (mysqli_connect_errno()) {
        printf("Connect failed: %s\n", mysqli_connect_error());
        exit();
    }

    $sql = "LOAD DATA LOCAL INFILE 'test.txt'
        INTO TABLE test
        FIELDS TERMINATED BY '-'
        LINES TERMINATED BY ':'";

    if (!($stmt = $mysqli->query($sql))) {
        echo "\nQuery execute failed: ERRNO: (" . $mysqli->errno . ") " . $mysqli->error;
    };

?>

// pdo example

<?php

    $databasehost = "localhost";
    $databasename = "test";
    $databaseusername = "user";
    $databasepassword = "passwd";

    $opt = array(
        PDO::MYSQL_ATTR_LOCAL_INFILE => 1,
        PDO::MYSQL_ATTR_READ_DEFAULT_GROUP => 'client' // add local-infile=1 in client section of my.cnf
    );

    try {
        $pdo = new PDO('mysql:host=' . $databasehost . ';dbname=' . $databasename , $databaseusername, $databasepassword, $opt);
    }

    catch (PDOException $e) {
        die('Error!: ' . $e->getMessage() . '<br/>');
    }

    $pdo->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);

    $sql = "LOAD DATA LOCAL INFILE 'test.txt'
        INTO TABLE test
        FIELDS TERMINATED BY '-'
        LINES TERMINATED BY ':'";

    $pdo->exec($sql);

?>