当插入的值与表中的列数不匹配时，使用PHP将数据插入MySQL表

Question

I have a mysql table with over 100 columns, but I've shortened it to 6 for this example:

col0(autoincrement), col1, col2, col3, col4, col5, col6

I'm getting data from external sources ($csvcontent below), and that data will have 6 or less values per line. I'd like to use PHP to read these values into an array, and insert the array values into into my mysql table.

$csvcontent = file from external source
$fieldseparator = ",";
$lineseparator = "
";
$linearray = array();

foreach(explode($lineseparator,$csvcontent) as $line) {
    $linearray = explode($fieldseparator,$line);
    $linemysql = implode("','",$linearray);
    $query = "insert into MYTABLE values('','$linemysql');";
}

How can I insert the following rows, assuming that I don't know ahead of time how many values are stored inside of $csvcontent? This existing code works well when I always have 6 values, but not when I have fewer.

insert into MYTABLE values('','1','2','3','4','5','6');  //works

insert into MYTABLE values('','1','2','3','4');          //doesn't work

insert into MYTABLE values('','1','2','3','4','5');      //doesn't work

Answer 1

Well, based on the size of $linearray, you can create a column sequence and attach that to your insert query.

<?php


$columns = ['col1','col2','col3','col4','col5','col6'];

$linearray_samples =  [
            [1,2,3,4,5],
            [1,2,3],
            [1,2,3,4],
            [1],
            [1,2],
            [1,2,3,4,5,6]
        ];


foreach($linearray_samples as $each_sample){
    echo "(",commaSeparatedColumns($each_sample,$columns),")",PHP_EOL;
}

function commaSeparatedColumns($sample,$columns){
    return implode(",",array_slice($columns,0,count($sample)));
}

The code outputs:

(col1,col2,col3,col4,col5)
(col1,col2,col3)
(col1,col2,col3,col4)
(col1)
(col1,col2)
(col1,col2,col3,col4,col5,col6)

Demo: https://3v4l.org/QLnFo