Solved

insert dynamic form rows into mysql

Posted on 2004-09-13
19
329 Views
Last Modified: 2013-12-12
Hi Experts,

I have a javascript that inserts dynamic form rows in my form.
how do i insert this into mysql?  was thinking a foreach loop, but it doesn't seem to be working...can someone help me out????? this is urgent.
0
Comment
Question by:lienny
  • 11
  • 7
19 Comments
 
LVL 27

Expert Comment

by:Diablo84
ID: 12044566
Difficult to advise without seeing code but this is the process you need to follow...

When the form rows are dynamically created with Javascript use html arrays, that is to say each one is called name[] (the important part being the square brackets).

On the processing page (il carry on using the name, "name" for example but obviously replace it with the name of the form elements) you can then do this:

$arr = $_POST['name']; //html array now in php variable
foreach ($arr as $var) {
 //run your query etc for each item using
 //$var to identify the value of each item
}
0
 

Author Comment

by:lienny
ID: 12044645
javascript:
 <style type="text/css">
     <!--
      INPUT, TEXTAREA, SELECT {font-family: Tahoma; font-size: small;}
     .php {font-family: Tahoma; font-size: small;}
     .ewTableOrderIndicator {font-family: Webdings;}
     #id {display: none;}
     #theInput {display: none;}
     -->
     </style>

<script type="text/javascript">
window.onload = init;

function init()
{
  document.getElementById("tblTotals").style.display = "none";
}

tdNames = new Array("", "x_cars", "x_dolAvg", "x_hoursWork", "x_dayBonus", "x_weekBonus",
"x_onlineSales", "x_offlineSales");

function addNew()
{
  var tbody = document.getElementById("tbl").getElementsByTagName("tbody")[0];
  var newRow = tbody.insertRow(tbody.rows.length-1);

  var cell0 = newRow.insertCell(0);
  var clone = document.getElementById("id").cloneNode(true);
  clone.id = "id-" + (tbody.rows.length-1);
  clone.name = clone.id;
  cell0.appendChild(clone);

  var textInput = document.getElementById("theInput");
  for (var i = 1; i < tdNames.length; i ++)
  {
    var cell = newRow.insertCell(i);
    var clone = textInput.cloneNode(true);
    clone.id = tdNames[i] + "_" + (tbody.rows.length-1);
    clone.name = clone.id;
    clone.size = "10";
    clone.onchange = changeTotal;
    cell.appendChild(clone);
  }
}

function changeTotal()
{
  var tbody = document.getElementById("tbl").getElementsByTagName("tbody")[0];
  var cellNum = 0;
  for (var i = 1; i < tdNames.length; i ++)
  {
    if (this.name.indexOf(tdNames[i]) > -1)
    {
      cellNum = i;
      break;
    }
  }
  var total = 0;
  for (var i = 0; i < tbody.rows.length-1; i ++)
  {
    var currValue = tbody.rows[i].cells[cellNum].getElementsByTagName("input")[0].value;
    if (!isNaN(parseInt(currValue)))
      total += parseInt(currValue);
  }
  var totals = document.getElementById("tblTotals");
  totals.style.display = "";
  totals.cells[cellNum].innerHTML = total;
}
</script>

html form:

<?php if (empty($x_numType)) { $x_numType = "E"; } // set default value ?><input type="hidden" name="x_numType" value="E">
<input type="hidden" name="x_lid" value="<? echo $x_lid ?>">
<input type="hidden" name="x_startDate" value="<? echo $x_startDate ?>">
<input type="hidden" name="x_endDate" value="<? echo $x_endDate ?>">
    <tr id="tblTotals">
       <th class="php" align="right">Totals:</th>
      <th class="php" align="center">0</th>
      <th class="php" align="center">0</th>
      <th class="php" align="center">0</th>
      <th class="php" align="center">0</th>
      <th class="php" align="center">0</th>
      <th class="php" align="center">0</th>
      <th class="php align="center"">0</th>
    </tr>
  </tbody>
</table>
<input type="submit" name="Action" value="SAVE">
</form>

<select name="id" id="id">
  <?
          $sQuery = mysql_query("SELECT id, firstName, lastName FROM employees");
                   while ($sRow = mysql_fetch_row($sQuery))
          {
             print ("<OPTION value='$sRow[0]'>$sRow[2], $sRow[1]</OPTION>");
          }
?>
</select>
<input type="text" id="theInput" />
0
 

Author Comment

by:lienny
ID: 12045485
diablo...by the way..you can look at what I have at www.thecandidgroup.com/csa/test.php
0
 
LVL 48

Expert Comment

by:hernst42
ID: 12046656
you can try this code to insert the multiple records to your database (untested):

$i =1;
// loop as long the are entries for id-x fields
while(array_key_exists('id-'.$i, $_REQUEST)) {
  $fields = array();
  // set the id
  $fields['id'] = $_REQUEST['id-' .$i];
  // loop over all variables and take those who are needed for the x input:
  foreach($_REQUEST as $k => $v) {
    //take all fields that start with an x_ and the consist of one word withput any digit
    if (preg_match('/^x_([a-zA-Z]+)$/', $k, $m)) {
      $fields[$m[1]] = $v;
    }
    // take all fields for the different ids (start with x_ one word and then end with _ and the id
    if (preg_match('/^x_([a-zA-Z]+)_'.$i.'$/', $k, $m)) {
      $fields[$m[1]] = $v;
    }
    // now do your insert into the db by picking the neccessary fields
  }
  ++$i
}
0
 

Author Comment

by:lienny
ID: 12047690
giving me a parse error on line 48..which should be the last } bracket.

here's my code:

/ open connection to the database
$conn = mysql_connect(HOST, USER, PASS);
mysql_select_db(DB);

if (isset($_POST['submit'])) {
            // get the form values
            $id = $_POST["x_id"];
            $eid = $_POST["id"];
            $lid = $_POST["x_lid"];
            $numType = $_POST["x_numType"];
            $startDate = $_POST["x_startDate"];
            $endDate = $_POST["x_endDate"];
            $cars = $_POST["x_cars"];
            $dolAvg = $_POST["x_dolAvg"];
            $hoursWork = $_POST["x_hoursWork"];
            $dayBonus = $_POST["x_dayBonus"];
            $weekBonus = $_POST["x_weekBonus"];
            $onlineSales = $_POST["x_onlineSales"];
            $offlineSales = $_POST["x_offlineSales"];
            $loc_count_cars = $_POST["x_loc_count_cars"];
}
            
$i =1;
// loop as long the are entries for id-x fields
while(array_key_exists('id-'.$i, $_REQUEST)) {
  $fields = array();
  // set the id
  $fields['id'] = $_REQUEST['id-' .$i];
  // loop over all variables and take those who are needed for the x input:
  foreach($_REQUEST as $k => $v) {
    //take all fields that start with an x_ and the consist of one word withput any digit
    if (preg_match('/^x_([a-zA-Z]+)$/', $k, $m)) {
      $fields[$m[1]] = $v;
    }
    // take all fields for the different ids (start with x_ one word and then end with _ and the id
    if (preg_match('/^x_([a-zA-Z]+)_'.$i.'$/', $k, $m)) {
      $fields[$m[1]] = $v;
    }
    // now do your insert into the db by picking the neccessary fields
      $insertSQL = "INSERT INTO numbers (x_lid, x_eid, x_numType, x_startDate, x_endDate, x_cars, x_dolAvg, x_hoursWork, x_dayBonus, x_weekBonus, x_onlineSales, x_offlineSales) VALUES ('$lid','$eid','$startDate','$endDate','$dolAvg','$hoursWork','$dayBonus','$weekBonus','$onlineSales','$offlineSales')";
    $insertSQL = mysql_query($insertSQL) or die(mysql_error());
            
      
  }
  ++$i
}
0
 
LVL 48

Expert Comment

by:hernst42
ID: 12048039
Just test this script: Took your settings and fixed the error with the bracket and the sql, which missed that variable $numType

// open connection to the database
$conn = mysql_connect(HOST, USER, PASS);
mysql_select_db(DB);

if (isset($_REQUEST['submit'])) {
    // not found in the form, and don't sem to be used:
    /*
    $loc_count_cars = $_POST["x_loc_count_cars"];
    $id = $_POST["x_id"];
    */

    $i =1;
    // loop as long the are entries for id-x fields
    while(array_key_exists('id-'.$i, $_REQUEST)) {
        $fields = array();
        // set the id
        $eid = $_REQUEST['id-' .$i];
        // loop over all variables and take those who are needed for the x input:
        foreach($_REQUEST as $k => $v) {
            //take all fields that start with an x_ and the consist of one word without any digit
            if (preg_match('/^x_([a-zA-Z]+)$/', $k, $m)) {
                ${$m[1]} = $v;
            }
            // take all fields for the different ids (start with x_ one word and then end with _ and the id)
            if (preg_match('/^x_([a-zA-Z]+)_'.$i.'$/', $k, $m)) {
                ${$m[1]} = $v;
            }
        }
        // now do your insert into the db by picking the neccessary fields
        $insertSQL = "INSERT INTO numbers (x_lid, x_eid, x_numType, x_startDate, x_endDate, x_cars, x_dolAvg, x_hoursWork, x_dayBonus, x_weekBonus, x_onlineSales, x_offlineSales) VALUES ('$lid','$eid','$numType', '$startDate','$endDate','$dolAvg','$hoursWork','$dayBonus','$weekBonus','$onlineSales','$offlineSales')";
        $insertSQL = mysql_query($insertSQL) or die(mysql_error());
        ++$i;
    }
}

0
 

Author Comment

by:lienny
ID: 12048174
it's still not inserting into the db...numType is used as a hidden field to pass a predefined value along to th db.
0
 
LVL 48

Expert Comment

by:hernst42
ID: 12048344
Yes I know but $numType was missing in your SQL-statement. I think I have found the error. In your form the submit-button has the name Action
so this line:
if (isset($_REQUEST['submit'])) {
should be
if (isset($_REQUEST['Action'])) {

You might also replace all $_REQUEST with $_POST so only the post-variables are taken and not the GET-vars which I used for testing.
0
 

Author Comment

by:lienny
ID: 12048717
no luck...error: Column count doesn't match value count at row 1
0
Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

 
LVL 48

Expert Comment

by:hernst42
ID: 12048913
Check your SQL-Statement:
INSERT INTO numbers (
x_lid,
x_eid,
x_numType,
x_startDate,
x_endDate,
x_cars,
x_dolAvg,
x_hoursWork,
x_dayBonus,
x_weekBonus,
x_onlineSales,
x_offlineSales) VALUES (
'$lid',
'$eid',
'$numType',
'$startDate',
'$endDate',
'$dolAvg',
'$hoursWork',
'$dayBonus',
'$weekBonus',
'$onlineSales',
'$offlineSales')

So the x_cars is not set in the values. Maybe you missed the $car variable. Add that variable at the right position and it should work
0
 

Author Comment

by:lienny
ID: 12049104
no it's not working
You have an error in your SQL syntax. Check the manual that corresponds to your MySQL server version for the right syntax to use near '10',5','0','0','0','0','0')' at line 1?
0
 
LVL 48

Expert Comment

by:hernst42
ID: 12049258
There is missing a '  before that 5, maybe missed where you build you SQL-statement
0
 

Author Comment

by:lienny
ID: 12055490
made the corrections and it's still giving me the error...any reason why?  
0
 

Author Comment

by:lienny
ID: 12055588
here's what I'm trying to accomplish:

http://www.thecandidgroup.com/csa/enumbersadd.php

when a user adds location numbers and hit submit it'll take them to the page above and list the record they just entered..below will have a button where i call the javascript function.  everything is working up to this point except for mysql insert.
0
 
LVL 48

Expert Comment

by:hernst42
ID: 12055699
It looks like the table you insert your SQL does not have the column x_eid. So make sure you build you INSERT to statement correctly. Have you a working INSERT-statement that you can execute via phpmyadmin or mysql-directly ??
So a structure of the table might also be usefull (how the table was created) Maybe the coulns are called eid, lid, .. and not x_edit, x_lid, ???
0
 

Author Comment

by:lienny
ID: 12055980
yes, but it shouldn't matter should it? it's working in all my other forms...if i change the x_eid to just eid does the php foreach statement that you created for me change also?  i checked in phpadmin and it's working correctly.

# Table structure for table `numbers`
#

CREATE TABLE `numbers` (
  `id` int(11) NOT NULL auto_increment,
  `eid` int(11) default NULL,
  `lid` int(11) default NULL,
  `numType` enum('L','E') NOT NULL default 'L',
  `startDate` date default '0000-00-00',
  `endDate` date default '0000-00-00',
  `cars` int(11) NOT NULL default '0',
  `dolAvg` decimal(11,2) NOT NULL default '0.00',
  `hoursWork` int(11) NOT NULL default '0',
  `dayBonus` decimal(11,2) NOT NULL default '0.00',
  `weekBonus` decimal(11,2) NOT NULL default '0.00',
  `onlineSales` decimal(11,2) NOT NULL default '0.00',
  `offlineSales` decimal(11,2) default '0.00',
  `loc_count_cars` int(11) default '0',
  PRIMARY KEY  (`id`)
) TYPE=MyISAM AUTO_INCREMENT=3 ;
0
 
LVL 48

Accepted Solution

by:
hernst42 earned 500 total points
ID: 12056840
yes it will only work if you remove the x_ in
INSERT INTO numbers (x_lid,x_eid, ...
The script removes the x_ and puts the value into the correspondig variables without the x_ before.
0
 

Author Comment

by:lienny
ID: 12058676
nope it's not working...tried a couple of different things but it doesn't want to seem to work...since I made the changes, now it won't even add any data to my numbers table. i.e. i have 2 forms that add to the same table.  they have to add the location numbers before they can add the employees number.
0
 

Author Comment

by:lienny
ID: 12059392
okay...I got it to work...thanks for your help! =D
0

Featured Post

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Nothing in an HTTP request can be trusted, including HTTP headers and form data.  A form token is a tool that can be used to guard against request forgeries (CSRF).  This article shows an improved approach to form tokens, making it more difficult to…
This article discusses four methods for overlaying images in a container on a web page
Learn how to match and substitute tagged data using PHP regular expressions. Demonstrated on Windows 7, but also applies to other operating systems. Demonstrated technique applies to PHP (all versions) and Firefox, but very similar techniques will w…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…

930 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

15 Experts available now in Live!

Get 1:1 Help Now