Insert Multiple using loop and array?

[piznac]

I have a form with 5 text boxes. I have a "add row" javascript applied to this form or rather the table itself.

Now when "add row" is pressed it creates another row with the same text boxes but with a "marked up" name and id. somthing like this.

text fields:
rm,dm,store,pto,pda & date

The resulting variables are:(these can be post or get)
$rm1,$dm1,$store1,$pto1,$pda1 & $date1

Now when a row is added it looks like this:
$rm2,$dm2,$store2,$pto2,$pda2 & $date2
$rm3,$dm3,$store3,$pto3,$pda3 & $date3
...and so on...


so my questions is how do I get these variables back to the database. I assumed I would have to write some sort of foreach or while loop. with the sql being the output

$sql = "INSERT INTO tbl_name (rm,dm,store,pto,pda,date) VALUES($rm1,$dm1,$store1,$pto1,$pda1,$date1);"

but I confused on how to write this loop statement, do I need an array? I really dont even know where to start...any help would be great.

[kenrbnsn]

Why don't you use array references for the name which would make the you code to add it to the database quite easy.

Plus the names of your text boxes would always be the same, so your Javascript wouldn't have to figure out what's the next number to add to the name.

The names would be "$rm[]",$dm[],$store[],$pto[],$pda[] & $date[]"

The form method really should be "post".

In you code to process the data, you should have something like:
[code]<?php
$fields = array('rm','dm','store','pda','date');
for ($i=0;$i<count($_POST['rm'];$i++) {
    $qtmp = array();
    foreach($fields as $fld)
         $qtmp[] = $fld . " = '" . mysql_real_escape_string(trim(stripslashes($_POST[$fld][$i]))) . "'";
    $query = "insert into table_name set " . implode(', ',$qtmp);
    $rs = mysql_query($query) or die('Problem with query(' . $i . '): ' . $query . '<br>' . mysql_error());
}
?>[/code]

Ken

[piznac]

[!--quoteo(post=368146:date=Apr 24 2006, 04:47 PM:name=kenrbnsn)--][div class=\'quotetop\']QUOTE(kenrbnsn @ Apr 24 2006, 04:47 PM) [snapback]368146[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Why don't you use array references for the name which would make the you code to add it to the database quite easy.

Plus the names of your text boxes would always be the same, so your Javascript wouldn't have to figure out what's the next number to add to the name.

The names would be "$rm[]",$dm[],$store[],$pto[],$pda[] & $date[]"

The form method really should be "post".

In you code to process the data, you should have something like:
[code]<?php
$fields = array('rm','dm','store','pda','date');
for ($i=0;$i<count($_POST['rm'];$i++) {
    $qtmp = array();
    foreach($fields as $fld)
         $qtmp[] = $fld . " = '" . mysql_real_escape_string(trim(stripslashes($_POST[$fld][$i]))) . "'";
    $query = "insert into table_name set " . implode(', ',$qtmp);
    $rs = mysql_query($query) or die('Problem with query(' . $i . '): ' . $query . '<br>' . mysql_error());
}
?>[/code]

Ken
[/quote]

Ken,

I guess I'm still a newbie with this stuff. Anyway can I talk you into explaining this with a little more detail.

[kenrbnsn]

Based on your previous posts I create a test script to illustrate the techniques. Instead of using Javascript to add the new row, I used PHP, but what each row looks like to the browser is the same.

Here's the code:
[code]<?php
<?php
session_start();
$fields = array('rm','dm','store','pda','date');
$nbr_rows = (isset($_SESSION['nbr_rows']))?$_SESSION['nbr_rows']:5;
$_SESSION['nbr_rows'] = $nbr_rows;
//
// Put your database connection code here
//
if (isset($_POST['submit'])) switch($_POST['submit']) {
    case 'Add a row':
        $nbr_rows++;
        $_SESSION['nbr_rows'] = $nbr_rows;
        break;
    case 'Submit':
        echo '<pre style="color:blue">' . htmlentities(stripslashes(print_r($_POST,true)) ) . '</pre>';
        echo 'The following queries would be generated:<br>';
        $tmp = array();
        for ($i=0;$i<$nbr_rows;$i++) {
            $qtmp = array();
            foreach($fields as $fld) {
                if (trim(stripslashes($_POST[$fld][$i])) != '')
                    $qtmp[] = $fld . " = '" . mysql_real_escape_string(trim(stripslashes($_POST[$fld][$i]))) . "'";
            }
            if (!empty($qtmp)) $tmp[] = "insert into table_name set " . htmlentities(implode(', ', $qtmp));
        }
        if (!empty($tmp))
            echo '<span style="color:red;font-family:sans-serif;font-weight:bold">' . implode("<br>\n",$tmp)."</span><br>\n";
        break;
    case 'Reset to 5 rows':
        $nbr_rows = 5;
        $_SESSION['nbr_rows'] = $nbr_rows;
        break;
    }

function disp_value($fld,$i)
{
    if (!isset($_POST[$fld])) return;
    if (isset($_POST['submit']) && $_POST['submit'] == 'Reset to 5 rows') return;
    return 'value="' . htmlentities(trim(stripslashes($_POST[$fld][$i]))) . '"';
}
?>
<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01//EN" "http://www.w3.org/TR/html4/strict.dtd">

<html>
<head>
    <title>Add Rows Example</title>
    <style type="text/css">
    body, html {
        font-family: Verdana, Geneva, Arial, Helvetica, sans-serif;
        font-size: 100%;
        margin: 0;
        padding: 0;
        width: 96%;
        margin-right: auto;
        margin-left: auto;
        padding-top: 1em;
    }
    </style>
</head>

<body>
<form action="<?php echo $_SERVER['PHP_SELF'] ?>" method="post">
<?php
$tmp = array();
for ($i=0;$i<$nbr_rows;$i++) {
    $tmp2 = array();
    foreach($fields as $fld) {
        $tmp2[] = '<span style="font-weight:bold">' . strtoupper($fld) . ':</span><input name="' . $fld . '[]" style="width:15%" type="text" ' . disp_value($fld,$i) . '>'; }
    $tmp[] = implode('&nbsp;',$tmp2);
    }
echo implode("<br>\n",$tmp)."<br>\n";
?>
<input type="submit" value="Add a row" name="submit">&nbsp;<input type="submit" value="Submit" name="submit"><?php if ($nbr_rows > 5) echo '&nbsp;<input type="submit" name="submit" value="Reset to 5 rows">'; ?>
</form>
</body>
</html>[/code]

I left in the debug statment that prints the $_POST arrary so you can see how the data is being returned to the script.

This script can be run at [a href=\"http://www.rbnsn.com/phpfreaks/addarow.php\" target=\"_blank\"]http://www.rbnsn.com/phpfreaks/addarow.php[/a]

Ken

[piznac]

first and foremost .. thank you for taking the time to do this.

But I'm still confused. I have inserted my connection and query script. And it does diplay the queries. But how would I then make that query work? Maybe Im being dumb here but Ive been working on this all morning and cant figure it out.

I assume that this script was never meant to work but just to help explain it to me. But now I dont know what to take from this script and apply it to make it work. Maybe that dosent make sense, it dosent to me!

I dont think Im advanced enough to make this happen but alas,...I must. If you can maybe just explain this script with some comments,..it would help. I know this is asking alot and you have already helped some much.

[kenrbnsn]

In order to figure out how to apply my suggestions to your script, we would have to see your script. Please post it either here or at [a href=\"http://pastebin.com/\" target=\"_blank\"]http://pastebin.com/[/a].

Kem

[piznac]

[!--quoteo(post=369334:date=Apr 27 2006, 03:45 PM:name=kenrbnsn)--][div class=\'quotetop\']QUOTE(kenrbnsn @ Apr 27 2006, 03:45 PM) [snapback]369334[/snapback][/div][div class=\'quotemain\'][!--quotec--]
In order to figure out how to apply my suggestions to your script, we would have to see your script. Please post it either here or at [a href=\"http://pastebin.com/\" target=\"_blank\"]http://pastebin.com/[/a].

Kem
[/quote]


Ok well,
all I have so far is the javascript & HTML of the first page (Ive been trying to play with yours to figure it out)

here it is:
[code]<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN" "http://www.w3.org/TR/html4/loose.dtd">
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=iso-8859-1">
<title></title>
<script language="JavaScript" type="text/javascript">
// Last updated 2006-02-21
function addRowToTable()
{
  var tbl = document.getElementById('tblSample');
  var lastRow = tbl.rows.length;
  // if there's no header row in the table, then iteration = lastRow + 1
  var iteration = lastRow;
  var row = tbl.insertRow(lastRow);
  
  // left cell
  var cellLeft = row.insertCell(0);
  var textNode = document.createTextNode(iteration);
  cellLeft.appendChild(textNode);
  
  // right cell
  var cellRight = row.insertCell(1);
  var el = document.createElement('input');
  el.type = 'text';
  el.name = 'dm' + iteration;
  el.id = 'dm' + iteration;
  el.size = 20;
  
  cellRight.appendChild(el);
  
  //2nd cell to left
  var cellRightb = row.insertCell(2);
  var el2 = document.createElement('input');
  el2.type = 'text';
  el2.name = 'rm' + iteration;
  el2.id = 'rm' + iteration;
  el2.size = 20;
  

  cellRightb.appendChild(el2);
  
  //3rd cell to left
  var cellRightc = row.insertCell(3);
  var el3 = document.createElement('input');
  el3.type = 'text';
  el3.name = 'store' + iteration;
  el3.id = 'store' + iteration;
  el3.size = 8;
  

  cellRightc.appendChild(el3);
  
  //4th cell to left
  var cellRightd = row.insertCell(4);
  var el4 = document.createElement('input');
  el4.type = 'text';
  el4.name = 'pto' + iteration;
  el4.id = 'pto' + iteration;
  el4.size = 10;
  

  cellRightd.appendChild(el4);
  
  //5th cell to left
  var cellRighte = row.insertCell(5);
  var el5 = document.createElement('input');
  el5.type = 'text';
  el5.name = 'pda' + iteration;
  el5.id = 'pda' + iteration;
  el5.size = 10;
  

  cellRighte.appendChild(el5);
  
  //6th cell to left
  var cellRightf = row.insertCell(6);
  var el6 = document.createElement('input');
  el6.type = 'text';
  el6.name = 'date' + iteration;
  el6.id = 'date' + iteration;
  el6.size = 20;
  

  cellRightf.appendChild(el6);
}

function removeRowFromTable()
{
  var tbl = document.getElementById('tblSample');
  var lastRow = tbl.rows.length;
  if (lastRow > 2) tbl.deleteRow(lastRow - 1);
}
</script>
</head>

<body>
<form action="test_return.php" method="post">
<p>&nbsp;</p>
<p>
<span id="spanOutput" style="border: 1px solid #000; padding: 3px;"> </span>
</p>
<table border="0" id="tblSample">
  <tr>
    <th>&nbsp;</th>
    <th>RM</th>
    <th>DM</th>
    <th>Store</th>
    <th>PTO</th>
    <th>PDA</th>
    <th>Date</th>
  </tr>
  <tr>
    <td>1</td>
    <td><input type="text" name="rm1"
     id="rm1" size="20" /></td>
    <td><label>
      <input name="dm1" type="text" id="dm1" size="20">
    </label>      </td>
    <td><label>
      <input name="store1" type="text" id="store1" size="8">
    </label></td>
    <td><label>
      <input name="pto1" type="text" id="pto1" size="10">
    </label></td>
    <td><label>
      <input name="pda1" type="text" id="pda1" size="10">
    </label></td>
    <td><label>
      <input name="date1" type="text" id="date1" size="20">
    </label></td>
  </tr>
</table>
<br>
<input type="submit" name="Submit" value="Submit">
<input name="button" type="button" onClick="addRowToTable();" value="Add Row" />
<input name="button" type="button" onClick="removeRowFromTable();" value="Remove Row" />
</form>

</body>
</html>[/code]

I have another page that just displays the varaiables as I name them. Thats about as far as I have gotten. The only thing I need after that (I assume) is to make the array then the loop and insert into the database. Sounds easy enough..lol. I really want to know how to do this, because this comes up a lot. So Im not asking you to write it for me. But some code with some comments would be great

Thanks for ALL the help!!!! :)

[piznac]

bump