Submitting form values to database, php

Question

I currently have a form that is built from an uploaded CSV. When the user uploads the CSV and hits 'Preview' Button it directs to a window that shows the entire CSV in a form table that is editable. The CSV is 5 records and 229 fields. The input names are built from row count and column count, so with this CSV it should start at row1col1 and go to row5col229.

I've discovered that the names are working like that, as expected, however I still have a problem. SOme of the CSV files will have 4 rows and some may have 8 or 9. I need to find a way to take the form input and submit it all into the 229 field staging table.

Is there a way to create an array and statement for one row and loop it for however many rows are actually present?

Here's my current code:

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">';
    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."col".$colCount."' type='text' value='$value' /></td>";

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

        if (++$rowCount > $maxPreviewRows) {
            break;
        }
    }
    echo '</tbody></table>';
    echo '<input type=\'submit\' value=\'Submit\' >';
    var_dump($_POST);
    echo '</form>';
}
?> 

I feel like I'm on the right track but I have no idea how to go about building the array of elements or the statement so that it is a template, so to speak, and loop it for all rows.

Answer 1

In answer to the comments on the Answer by Tom:

You can set vales in the form into an array $_POST['rows']['columns'] ad then simply count($_POST['rows']); to count values and then foreach over each value in the row.

-- Martin

So I wouldn't need to go through and declare 229 elements? Just create the array and count, then loop with the foreach? In this case, how would I create a statement in SQL to insert to the database?

-- Tom

Your form would be an array of POST values such as

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

            $colCount++;
        }  

This will then produce an array POST value such as:

$_POST['row'][1][1] = $value;
$_POST['row'][1][2] = $value;
$_POST['row'][1][3] = $value;
...
$_POST['row'][1][229] = ...;
$_POST['row'][2][1] = ... ;
...
$_POST['row'][2][229] = ...;
...
$_POST['row'][5][229] = ...;

You can then run a foreach loop on this array and then extract the value of the data saved, for each key of the array:

$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 <table> (".implode(",",$sql).") VALUES(".implode(",",$inserts).")";
       $db_connection->prepare($sqlFull); 
       /***
        * EDIT: bind param MUST come after the prepare call
        ***/
       foreach($binds as $bindKey=>$bindRow){
            $db_connection->bind_param(":".$bindKey, $bindRow);
       }
       unset($bindKey,$bindRow);      
       $sql = $inserts = $binds = []; //reset arrays for next row iteration. 
       /***
        * db_connection then executes the statement constructed above
        ***/
        $db_connection->execute();
     } //close if.
}
unset($rowValue);

---

Please note this a quick and dirty example only and I've not had time to check my syntax is exact, but it's more to give you a rough idea of the query structure

You can use count() to count the rows and columns in the $_POST array.

Answer 2

I actually figured this out, and The names are working as expected. However, I have an issue. Some CSV files will have 5 rows and some will have more, so I can't create a static way to do this by just input names. Is there a way to create one array and statement and loop it for however many rows are present?

---

EDIT The current source code used for solving issues raised in comments to Martins answer.

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

if ($connect->connect_error) {
die("Connection failed: " . $conn->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 $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);
   /***
    * 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 is then given the SQL. 
    ***/
    $connect->execute();

  echo "<p>\$sqlFull:<pre>".print_r($sqlFull,true)."</pre></p>\n";

  if(mysqli_multi_query($connect, $sqlFull)) 
  {
    echo'File submitted'; 
  } else { 
    echo "Error: " . mysqli_error($connect); 
  }
 } //close if.


}

unset($rowValue);



?>