如何在必须插入varchar的情况下将数组插入MySQL

提交表单时，_POST（或_REQUEST）将显示为以下

array (
  'year' => 
  array (
    0 => '2001',
    1 => '2010',
  ),
  'cs' => 
  array (
    0 => '1',
    1 => '2',
  ),
  't_r' => 
  array (
    0 => '12:00:00',
    1 => '13:00:00',
  ),
  'submit' => 'SUBMIT',
)

如果它看起来像会更好

array(
    'records' = array(
        0=>array('year'=>'2001', 'cs'=>'1', 't_r'=>'12:00:00'),
        0=>array('year'=>'2010', 'cs'=>'2', 't_r'=>'13:00:00'),
    ),
)

这需要更改javascript代码（我现在太懒了……所以我使用SPL MultipleIterator来"模拟"该数据格式）

if ( isset($_POST['submit']) ) {
    // add tests for is_array() POST[year], POST[cs] and POST[t_r]
    // this will make mysqli throw an exception whenever an operation results in an 
    // (mysql) error code other than 0 -> no further error handling included in this script....
    mysqli_report(MYSQLI_REPORT_ALL|MYSQLI_REPORT_STRICT);
    $mysqli = new mysqli('localhost', 'localonly', 'localonly', 'test');
    // create a prepared statement and bind the parameters, see http://docs.php.net/mysqli.quickstart.prepared-statements
    $stmt = $mysqli->prepare('INSERT INTO firearms_id_secs (year, case_no, t_received) VALUES (?,?,?)');
    if ( !$stmt->bind_param('sss', $year, $caseno, $time) ) { // binding all parameters as strings ...let mysql's type system handle it...
        yourErrorHandlerHere(); // or throw an exception....
    }
    // when ever the statement is executed, the current values (at _that_ moment) in $year, $caseno and $time will be used where the ? are in the statement
    // this wouldn't be necessary if the POST body looked like record[1][year]=2001&....
    $mit = new MultipleIterator;
    $mit->attachIterator( new ArrayIterator($_POST['year']) );
    $mit->attachIterator( new ArrayIterator($_POST['cs']) );
    $mit->attachIterator( new ArrayIterator($_POST['t_r']) );
    foreach( $mit as $record ) {
        echo 'executing statement with [', join(',', $record), "]<br/>'r'n";
        // assign the values to the bound parameters
        list($year,$caseno,$time) = $record;
        // and then execute the statement (with those values)
        /*
        you might want to wrap this in a try-catch block, so a single faulty record will not throw off your entire script.
        You might also want to look into transactions (in case a single faulty record is supposed to roll back the entire operation)
        see http://docs.php.net/language.exceptions , http://dev.mysql.com/doc/refman/5.7/en/commit.html
        */
        $stmt->execute();
    }
}

编辑：a） 要允许NULL值，您应该在参数中将空字符串替换为NULL

...
// replace empty strings by NULL
$record = array_map(
    function($e) {
        return 0<strlen(trim($e)) ? $e : NULL;
    },
    $record
);
// assign the values to the bound parameters
list($year,$caseno,$time) = $record;
...

b） 我不知道在这种情况下为什么以及在哪里需要迭代器的密钥，但是。。。。

<?php
$data=array(
    'maj'=>new ArrayIterator(array('A','B','C')),
    'min'=>new ArrayIterator(array('a','b','c')),
    'foo'=>new ArrayIterator(array('do'=>'re', 'mi'=>'fa', 'so'=>'la', 'ti')),
);
$mit = new MultipleIterator(MultipleIterator::MIT_NEED_ANY|MultipleIterator::MIT_KEYS_ASSOC);
$mit->attachIterator( $data['maj'], 'majuscule' );
$mit->attachIterator( $data['min'], 'minuscule' );
$mit->attachIterator( $data['foo'], 'lalala' );
foreach( $mit as $r ) {
    var_export($r);
}

打印

array (
  'majuscule' => 'A',
  'minuscule' => 'a',
  'lalala' => 're',
)array (
  'majuscule' => 'B',
  'minuscule' => 'b',
  'lalala' => 'fa',
)array (
  'majuscule' => 'C',
  'minuscule' => 'c',
  'lalala' => 'la',
)array (
  'majuscule' => NULL,
  'minuscule' => NULL,
  'lalala' => 'ti',
)

确保它是字符串

$c_no        = mysql_real_escape_string("$_REQUEST['cs']");
$t_r        = mysql_real_escape_string("$_REQUEST['t_r']");

或

$c_no        = mysql_real_escape_string( strval($_REQUEST['cs']) );
$t_r        = mysql_real_escape_string( strval($_REQUEST['t_r']) );

尝试将''设置为varchar值

$mysqli->query("INSERT INTO firearms_id_secs (year, case_no, t_received) 
                VALUES ($year[$k], '$c_no[$k]', '$t_r[$k]')");

我将帮助您完成请求的解码部分，其余部分之前已经由@rmondsilva（通过在VARCHARs上加引号）回答。

$year_param = $_REQUEST['year'];
$case_param = $_REQUEST['cs'];
$time_param = $_REQUEST['t_r'];
if (isset($_REQUEST['submit']) && isset($_REQUEST['year']) ) {
  foreach ($_REQUEST['year'] as $k=> $value ){ // loop through array
    $year     = $year_param[$k];
    $c_no        = mysql_real_escape_string($case_param [$k]);
    $t_r        = mysql_real_escape_string($time_param [$k]);

$mysqli->query("INSERT INTO firearms_id_secs (year, case_no, t_received) VALUES 
   ($year, '$c_no', '$t_r')");
}

考虑通过