Jump to content
Sign in to follow this  
thenorman138

Inserting long form data to database table

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.

Share this post


Link to post
Share on other sites

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 by benanamen
  • Like 1

Share this post


Link to post
Share on other sites

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

Sign in to follow this  

×
×
  • 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.