Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

insert dynamic form rows into mysql

Posted on 2004-09-13
19
Medium Priority
?
341 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
Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

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

Concerto Cloud for Software Providers & ISVs

Can Concerto Cloud Services help you focus on evolving your application offerings, while delivering the best cloud experience to your customers? From DevOps to revenue models and customer support, the answer is yes!

Learn how Concerto can help you.

Question has a verified solution.

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

Password hashing is better than message digests or encryption, and you should be using it instead of message digests or encryption.  Find out why and how in this article, which supplements the original article on PHP Client Registration, Login, Logo…
Since pre-biblical times, humans have sought ways to keep secrets, and share the secrets selectively.  This article explores the ways PHP can be used to hide and encrypt information.
The viewer will learn how to create and use a small PHP class to apply a watermark to an image. This video shows the viewer the setup for the PHP watermark as well as important coding language. Continue to Part 2 to learn the core code used in creat…
This tutorial will teach you the core code needed to finalize the addition of a watermark to your image. The viewer will use a small PHP class to learn and create a watermark.
Suggested Courses

824 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