Inserting long form data to database table

[thenorman138]

I have a form that is populated by a CSV file, always 229 fields but anywhere up to 10 or 12 rows. The form populates correctly, but I'm having issues saving it all. The loop seems like it's missing something or maybe something is mismatched.

 

Here is the form code:

 

**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>';

        }

        ?>

 

 

 

Here's the code for the submission page with the insert loop:

 

**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);

 

    ?>

 

You can see in the submission page where I comment out the var_dump for post data. When it's active, it prints out this for the array:

 

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

 

and continues printing the 229 elements for all 7 rows.

 

There is something that's not quite working in this loop because even a debug statement within the loop to print the query won't work. I just need to make sure it iterates all 229 fields of every row to the end of the form and puts them into my staging table.

 

My staging table, which is temporary, has 229 named fields in the exact order as the CSV, so I shouldn't have to declare each field I don't believe.

[benanamen]

If all you want to do is get the CSV data into the DB there is no need for a form. Import it directly to your database. There are several ways to do that. You could also use Phpmyadmin or any number of the DB GUI managers that are out there. There is also Mysql's LOAD DATA INFILE. https://dev.mysql.com/doc/refman/5.7/en/load-data.html

Edited May 5, 2017 by benanamen