Solved

insert dynamic form rows into mysql

Posted on 2004-09-13
19
328 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
no luck...error: Column count doesn't match value count at row 1
0
Free Trending Threat Insights Every Day

Enhance your security with threat intelligence from the web. Get trending threat insights on hackers, exploits, and suspicious IP addresses delivered to your inbox with our free Cyber Daily.

 
LVL 48

Expert Comment

by:hernst42
Comment Utility
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
Comment Utility
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
Comment Utility
There is missing a '  before that 5, maybe missed where you build you SQL-statement
0
 

Author Comment

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

Author Comment

by:lienny
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
okay...I got it to work...thanks for your help! =D
0

Featured Post

Easy Project Management (No User Manual Required)

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

Join & Write a Comment

Introduction Many web sites contain image galleries; a common design for these galleries includes a page with a collection of thumbnail images.  You can click on each of the thumbnail images to see the larger version of the image.  This is easily i…
Consider the following scenario: You are working on a website and make something great - something that lets the server work with information submitted by your users. This could be anything, from a simple guestbook to a e-Money solution. But what…
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…
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

762 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

7 Experts available now in Live!

Get 1:1 Help Now