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.

I rolled back your edit and put your edit onto your answer rather than your question! — Mm-Art-In
– Mm-Art-In, Commented May 4, 2017 at 17:43

Community · Accepted Answer · 2017-05-23 12:10:39Z

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.

edited May 23, 2017 at 12:10

CommunityBot

11 silver badge

answered May 4, 2017 at 15:58

Mm-Art-In

23.1k13 gold badges81 silver badges144 bronze badges

Sign up to request clarification or add additional context in comments.

18 Comments

Mm-Art-In Over a year ago

I'm afraid this will need a bit of editing as you can't ->bindparam before setting the ->prepare

Mm-Art-In Over a year ago

@TomN. I have updated my answer so the bind params should work correctly now. I must highly encourage you to look into prepared statements as well as making full use of PHP error logging. It wil help find issues so much quicker than blank screens! Good luck!

Mm-Art-In Over a year ago

@TomN. I don't like using isset, I think it's far too vague a function.

Mm-Art-In Over a year ago

I spotted the issue, my foreach loop in my answer doesn't take into account that row contains arrays (of columns). I will edit it....

Mm-Art-In Over a year ago

@TomN. the way I would suggest would be to put print_r() in your code for every variable (with a describer for each one such as print "binder: ".print_r($binder,true)."<br>"; ) and work through it like that, to see exactly what data is being placed where, and then using that to tweak the code so the data is the correct shape for the SQL to use. From this you should also be able to more practically see what is actually going on and where. Good luck.

|

Geoff_S · Accepted Answer · 2017-05-04 20:44:19Z

0

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



?>

edited May 4, 2017 at 20:44

answered May 4, 2017 at 15:15

Geoff_S

5,1257 gold badges62 silver badges174 bronze badges

4 Comments

Mm-Art-In Over a year ago

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.

Geoff_S Over a year ago

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?

Mm-Art-In Over a year ago

That's a pretty big question; you can use the foreach loop to construct the parameters of the SQL insert and then after the loop run the insert. There will be lots of reference material to this on SO

Geoff_S Over a year ago

Ok, I think I understand. I will do some research on that, thank you

Collectives™ on Stack Overflow

Submitting form values to database, php

2 Answers 2

18 Comments

4 Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

18 Comments

4 Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related