Solved

insert dynamic form rows into mysql

Posted on 2004-09-13
19
331 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
Free Tool: Postgres Monitoring System

A PHP and Perl based system to collect and display usage statistics from PostgreSQL databases.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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

Free Tool: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Suggested Solutions

Deprecated and Headed for the Dustbin By now, you have probably heard that some PHP features, while convenient, can also cause PHP security problems.  This article discusses one of those, called register_globals.  It is a thing you do not want.  …
Foreword (July, 2015) Since I first wrote this article, years ago, a great many more people have begun using the internet.  They are coming online from every part of the globe, learning, reading, shopping and spending money at an ever-increasing ra…
The viewer will learn how to dynamically set the form action using jQuery.
The viewer will learn how to look for a specific file type in a local or remote server directory using PHP.

856 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