How to split string in php from CSV file and insert to multiple row in database?

I was trying to come up with a sleek way to simply do one LOAD DATA and also extract out the two protocol columns you want, but was unable to do so. The next best thing might be to do a LOAD DATA and then use MySQL's string functions to create the columns you want from the raw data.

LOAD DATA INFILE 'file.csv'
INTO TABLE protocol (id, protocol_orig)
FIELDS TERMINATED BY '|'
LINES TERMINATED BY '\r\n'

Next create the two columns you want:

ALTER TABLE protocol ADD protocol_type VARCHAR(55);
ALTER TABLE protocol ADD protocol VARCHAR(10);

Now populate the two new protocol columns:

UPDATE protocol
    SET protocol_type = SUBSTRING_INDEX(SUBSTRING_INDEX(protocol_orig, ' ', 1), ' ', -1),
        protocol = SUBSTRING_INDEX(SUBSTRING_INDEX(protocol_orig, ' ', 2), ' ', -1);

Finally, you can drop the original column protocol_orig if you won't need it anymore:

ALTER TABLE protocol DROP COLUMN protocol_orig;

Here is a demo of the logic used in the UPDATE statement. It is robust to the case where you might only have a protocol type but a missing protocol:

SQLFiddle

Let's break things into phases

1. Grab the data from CSV

function readCSV($csvFile){
    $file_handle = fopen($csvFile, 'r');
    while (!feof($file_handle) ) {
        $line_of_text[] = fgetcsv($file_handle, 1024);
    }
    fclose($file_handle);
    return $line_of_text;
}


// Set path to CSV file
$csvFile = 'sample.csv';
$csv = readCSV($csvFile);

This will retrieve the data in your CSV file. Which $csv returns

array(5) {
  [0] =>
  array(2) {
    [0] => string(2) "id"
    [1] => string(8) "protocol"
  }
  [1] =>
  array(2) {
    [0] => string(1) "1"
    [1] => string(8) "SNMP GET"
  }
  [2] =>
  array(2) {
    [0] => string(1) "2"
    [1] => string(9) "TEST POST"
  }
  [3] =>
  array(2) {
    [0] => string(1) "3"
    [1] => string(7) "ETC LOL"
  }
  [4] => bool(false)
}

2. Iterate the $csv into proper readable array.

$res = array();
foreach ($csv as $idx => $data)
{
    // This will skip the header and validate if data is present.
    if ($idx != 0 && $data)
    {
        $rowId = $data[0];
        $protocolType = explode(" ", $data[1])[0]; //Returns 'SNMP'
        $protocol = explode(" ", $data[1])[1]; // Returns 'GET'
        $res[] = array(
            "id" => $rowId, 
            "protocol_type" => $protocolType, 
            "protocol" => $protocol
        );
    }
}

In this phase, $res returns,

array(3) {
  [0] =>
  array(3) {
    'id' => string(1) "1"
    'protocol_type' => string(4) "SNMP"
    'protocol' => string(3) "GET"
  }
  [1] =>
  array(3) {
    'id' => string(1) "2"
    'protocol_type' => string(4) "TEST"
    'protocol' => string(4) "POST"
  }
  [2] =>
  array(3) {
    'id' => string(1) "3"
    'protocol_type' => string(3) "ETC"
    'protocol' => string(3) "LOL"
  }
}

3. Store them into your table

Let's say that you already have the table protocol with columns of id, protocol_type, protocol.

You can just iterate the array of $res and store them into your database. e.g.

foreach ($res as $row)
{
    $sqlCommand = "
        INSERT INTO protocol (id, protocol_type, protocol) 
        VALUES ({$row['id']}, {$row['protocol_type']}, {$row['protocol']})";
}