How do i insert multiple rows at once?

delfa

I´ve beed trying to find useful examples in this forum about this subject but I founf nothing useful!

Can someone give me some detailed code. Most of the unswers I found used some loops.

I have only:

+------+--------+-----------+
| id     | name  | color      |
+------+--------+-----------+
|    1  | delsio  |  red       |
|    2  | sergio  | green    |
+------+--------+-----------+

$cores = array('red','blue','pink','yellow');

foreach ( $cores as $cor ) {
    echo "U like $cor?\n";
}

That code above seams to be useful to create the forms.

How do i insert multiple rows at once? 😕

mogster

Well, if you have multiple selects or checkboxes, the data would be in an array.
Then you would check the size of the posted array and create a for-loop for iteration:

for($i=0;$i<sizeof($colors);$i++) {
$updDb = mysql_query("insert into table values ('', '$name[$i]', '$color[$i]'");
}

A suggestion 😉

knutm

delfa

Humm ........ sorry i don´t get it.

Actually I´m not very good with arrays.

can you give me an example starting from the forms?

mogster

Well, what do you want to do?
I'm not sure what you're after 😉

If you have a form with a name and a color to be put in, then you don't need multiple inserts? Or should the user select more than one color?

knutm

delfa

Maybe if I show u what I did u will understand:

<?php
function GetSQLValueString($theValue, $theType, $theDefinedValue = "", $theNotDefinedValue = "") 
{
  $theValue = (!get_magic_quotes_gpc()) ? addslashes($theValue) : $theValue;

  switch ($theType) {
    case "text":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;    

    case "long":
    case "int":
      $theValue = ($theValue != "") ? intval($theValue) : "NULL";
      break;
    case "double":
      $theValue = ($theValue != "") ? "'" . doubleval($theValue) . "'" : "NULL";
      break;
    case "date":
      $theValue = ($theValue != "") ? "'" . $theValue . "'" : "NULL";
      break;
    case "defined":
      $theValue = ($theValue != "") ? $theDefinedValue : $theNotDefinedValue;
      break;
  }
  return $theValue;
}
 require_once('../Connections/vidisco.php'); ?>
<?
if ((isset($HTTP_POST_VARS["MM_insert"])) && ($HTTP_POST_VARS["MM_insert"] == "form1")) {

 for($i=1;$i<="3";){
 $n = "name".$i;
 $c = "cores".$i;
 $namex = $HTTP_POST_VARS["$n"];
 $coresx = $HTTP_POST_VARS["$c"];
echo $namex;

/*   $insertSQL = sprintf("INSERT INTO teste (name, cores) VALUES (%s, %s)",
                       GetSQLValueString($namex, "text"),
                       GetSQLValueString($coresx, "text"));

  mysql_select_db($database_vidisco, $vidisco);
  $Result1 = mysql_query($insertSQL, $vidisco); 
echo mysql_errno() . ": " . mysql_error(). "\n"; */
$i++;


} 
}
?>
<form action="index.php" method="post" enctype="application/x-www-form-urlencoded" name="form1">
<table width="100%" border="0" cellspacing="0" cellpadding="0">
<tr>
<td>cores</td>
<td>name</td>
</tr><? 
 for($i=1;$i<="3";){
?>

<tr> 
<td width="24%"><input name="cores" type="text" id="cores<? echo $i; ?>"></td>
<td width="76%"><input name="name" type="text" id="name<? echo $i; ?>"></td>
</tr>
<?  $i++;
}

?>
<tr> 
<td><input name="cores" type="text" id="cores"> <input type="hidden" name="MM_insert" value="form1"></td>
<td><input name="name" type="text" id="name"> <input type="submit" value="Submit"></td>
</tr>
</table>
</form>

I´m trying to generate multiple form values to put in the same table:

1 name1 cores1
2 name2 cores2
3 name3 cores3
4 name4 cores4

get all this and put to the table:

name cores

using a single submission form

Sorry for my bad english!

Thanx for any help!

ultraslacker

mysql has something called extended inserts.

INSERT INTO tablename VALUES (foo, bar),(bar, foo),...

delfa

Ok, that seams to be a very intelligent solution, but i can´t find what´s wrong:

$insertSQL = "INSERT INTO teste (nome, cores) ";

foreach ($HTTP_POST_VARS as $nome) {
$insertSQL .= " VALUES ($nome, $cores) ";

$insertSQL .= ',';
} 
$insertSQL .= " VALUES (NULL, NULL) ";
$Result1 = mysql_query($insertSQL, $vidisco); 
echo mysql_errno() . ": " . mysql_error(). "\n";

}

ultraslacker

Use VALUES only once, to designate the list of values.

delfa

Ok i solved that problem with VALUES.

But the columns nome and cores aren´t filled correctly with the information i submitted.

Can you help me with that loop, it´s doesn´t seam to work:


$insertSQL = "INSERT INTO teste (nome, cores)  VALUES ";
$maxval = max($HTTP_POST_VARS);
echo $maxval;
for ($i = 1; $i < $maxval;) {
$insertSQL .= "('\"$nome\"', '\"$cores\"') ,"; 
$i++;
} 

$insertSQL .= " (NULL, NULL) ";

$Result1 = mysql_query($insertSQL, $vidisco);