Jump to content

Inserting long form data to database table


thenorman138

Recommended Posts

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.
Link to comment
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.