How to Insert Record with multiple rows

[skyline2]

Hello alls,

i want to ask something... how to use insert record server behavior -  php myysql, to insert multiple rows from textfiled?

thanks.

[David_Powers]

Out of the box, the Insert Record server behavior can insert only one row at a time. You need to adapt the script yourself to loop through the rows or to build the appropriate SQL query. It's not difficult to do, but it depends on your knowledge of PHP and/or SQL.

[skyline2]

thank you very much david

how to design it using looping or source code?

[David_Powers]

It would have been more helpful if you had shown me your existing code. However, this shows the basic principles.

First of all, the name of each input field in the form needs to end in an empty pair of square brackets like this:

<input type="text" name="field_name[]" . . .

This results in $_POST['field_name'] being treated as an array of the values in each input field. Change field_name to the actual name of the input field. Without the square brackets, only the last value is submitted by the form.

The Insert Record server behavior code looks like this:

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {
  $insertSQL = sprintf("INSERT INTO my_table (field1, field2) VALUES (%s, %s)",
                       GetSQLValueString($_POST['field1'], "int"),
                       GetSQLValueString($_POST['field2'], "text"));

  mysql_select_db($database_connAdmin, $connAdmin);
  $Result1 = mysql_query($insertSQL, $connAdmin) or die(mysql_error());

  $insertGoTo = "another_page.php";
  if (isset($_SERVER['QUERY_STRING'])) {
    $insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?";
    $insertGoTo .= $_SERVER['QUERY_STRING'];
  }
  header(sprintf("Location: %s", $insertGoTo));
}

You need to amend it like this:

if ((isset($_POST["MM_insert"])) && ($_POST["MM_insert"] == "form1")) {

  mysql_select_db($database_connAdmin, $connAdmin);

  // loop through the input values
  for ($i = 0, $len = count($_POST['field1']); $i < $len; $i++) {
      $insertSQL = sprintf("INSERT INTO my_table (field1, field2) VALUES (%s, %s)",
                            GetSQLValueString($_POST['field1'][$i], "int"),
                            GetSQLValueString($_POST['field2'][$i], "text"));

      $Result1 = mysql_query($insertSQL, $connAdmin) or die(mysql_error());
  } // end loop

  $insertGoTo = "another_page.php";
  if (isset($_SERVER['QUERY_STRING'])) {
    $insertGoTo .= (strpos($insertGoTo, '?')) ? "&" : "?";
    $insertGoTo .= $_SERVER['QUERY_STRING'];
  }
  header(sprintf("Location: %s", $insertGoTo));
}

This uses a for loop (see http://docs.php.net/manual/en/control-structures.for.php) to access each element of the $_POST['field1'] and $_POST['field2'] arrays (see http://docs.php.net/manual/en/language.types.array.php).

[whiteheat74]

Hello David. How would you modify this code to skip null values of field1 when setting the number of loops required in your example?