Skip to main content
Participant
Nov 11, 2010 4:02 AM UTC
Question

How to Insert Record with multiple rows

Hello alls,

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

thanks.

This topic is closed to new replies. Start a new post to keep the conversation going.

4 replies

David_Powers
Inspiring
Nov 11, 2010 12:39 PM UTC

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.

Translate
skyline2Author
Participant
Nov 12, 2010 1:22 AM UTC

thank you very much david

how to design it using looping or source code?

Translate
David_Powers
Inspiring
Nov 12, 2010 11:19 AM UTC

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

Translate