php - 使用PHP将长格式数据保存到数据库

Question

我有一个由 CSV 文件填充的表单，始终包含 229 个字段，但最多 10 或 12 行。表单正确填充，但我在保存所有表单时遇到问题。循环似乎缺少某些东西，或者可能某些东西不匹配。

表单代码如下:

page-confirm.php

$connect = mysqli_connect($server, $user, $pw, $db);

if ($connect->connect_error) {
    die("Connection failed: " . $conn->connect_error);
}else{
    //echo'success!';
}

if(isset($_POST['preview']))
{
ini_set('auto_detect_line_endings', true);


$file = $_FILES["file"]["tmp_name"];
$handle = fopen($file, "r");
$maxPreviewRows = PHP_INT_MAX;  // this will be ~2 billion on 32-bit system, or ~9 quintillion on 64-bit system
$hasHeaderRow = true;
    echo "<form method='post' action='/form-submit' >";
    echo '<table>';

    if ($hasHeaderRow) {
        $headerRow = fgetcsv($handle);
        echo '<thead><tr>';
        foreach($headerRow as $value) {
            echo "<th>$value</th>";
        }
        echo '</tr></thead>';
    }

    echo '<tbody>';

    $rowCount = 0;
    while ($row = fgetcsv($handle)) {
        $colCount = 0;
        echo '<tr>';
        foreach($row as $value) {
            echo "<td><input name='row[".$rowCount."][".$colCount."]' type='text' value='$value' /></td>";

                $colCount++;
                } 
        echo '</tr>';

        if (++$rowCount > $maxPreviewRows) {
            break;
        }
    }
    echo '</tbody></table>';
    echo "<input type='submit' name='confirm' value='confirm'>";
    echo '</form>';
    }
    ?>

这是带有插入循环的提交页面的代码:

page-formsubmit.php

$connect = mysqli_connect($server, $user, $pw, $db);



if ($connect->connect_error) {
die("Connection failed: " . $connect->connect_error);
}else{
echo'success!';
}

//var_dump($_POST);


$sql = $inserts = $binds = [];
foreach ($_POST['row'] as $rowValue){
if(is_array($rowValue) && count($rowValue) > 0 ){
    foreach($rowValue as $rowData){
       /***
        * Stupidly, I had missed that row contains arrays 
        * rather than values, so you need a foreach, inside the 
        * foreach as so:
        ***/
        foreach ($rowData as $columnKey  => $columnValue){
            //$columnValue will now equal $value
            //$columnKey will be the column number (1...229)
            /***
             * This is the area you can construct your SQL query values.
             * db_connection is assumed to be setup.
             ***/
             $sql[] = "`column_name_".$columnKey."`";
             $binder = "value".$columnKey;
             $inserts[] = ":".$binder;
             $binds[$binder] = $columnValue;
             unset($binder);
        }
       unset($columnKey,$columnValue);             
   }
   unset($rowData);
   /***
    * This is the area the SQL query is set on a per row basis
    ***/
   $sqlFull = "INSERT INTO staging (".implode(",",$sql).") VALUES(".implode(",",$inserts).")";
   $connect->prepare($sqlFull); 
   /***
    * EDIT: bind param MUST come after the prepare call
    ***/
   foreach($binds as $bindKey=>$bindRow){
        $connect->bind_param(":".$bindKey, $bindRow);
   }
   unset($bindKey,$bindRow);
   var_dump($binds);      
   $sql = $inserts = $binds = []; //reset arrays for next row iteration. 
   /***
    * db_connection then executes the statement constructed above
    ***/
    $connect->execute();
 } //close if.
}
unset($rowValue);

?>

您可以在提交页面中看到，我在其中注释掉了发布数据的 var_dump。当它处于事件状态时，它会为数组打印出这个:

success!array(1) { ["row"]=> array(5) { [0]=> array(229) 

并继续打印所有 7 行的 229 个元素。

在这个循环中有些东西不能正常工作，因为即使循环中用于打印查询的调试语句也不起作用。我只需要确保它将每一行的所有 229 个字段迭代到表单的末尾并将它们放入我的暂存表中。

我的临时表有 229 个命名字段，其顺序与 CSV 完全相同，因此我不必声明每个我不相信的字段。

Answer 1

听起来您正在达到 PHP 配置的默认最大输入变量数。在您的 php.ini 中，您需要将 max_input_vars 指令设置为大于默认值 1000 的值。

max_input_vars = 3000

http://php.net/manual/en/info.configuration.php#ini.max-input-vars

要解决将数据输入数据库的问题，您需要修复一堆错误。我只是像这样重写了“page-formsubmit.php”......

page-formsubmit.php

/**
 * This requires the use of PHP 5.6 or greater. Older versions are not supported
 */
$connect = mysqli_connect( $server, $user, $pw, $db );

if( $connect->connect_error ) 
{
    die("Connection failed: " . $connect->connect_error);
} else 
{
    echo'success!';
}

//var_dump($_POST);

foreach ($_POST['row'] as $rowValue)
{
    if( is_array($rowValue) && count($rowValue) > 0 )
    {
        $sqlFull = "INSERT INTO staging VALUES( DEFAULT, ". substr( str_repeat ( '?, ', count($rowValue) ), 0, -2 ) .")";
        if( !$stmt = $connect->prepare($sqlFull) )
        {
            echo "Prepare failed: (" . $connect->errno . ") " . $connect->error;
        }
        $stmt->bind_param( str_repeat ( 's', count($rowValue) ), ...$rowValue );

        $stmt->execute();
    }
}