Insert and Update

[flemingmike]

if(isset($_POST['lqlabour']))
{
//insert quoted labour
foreach ($_POST['lqlabour'] as $k => $lqlabour) {
    $ldescription = $_POST['ldescription'][$k];
	$llabour = $_POST['llabour'][$k];
	$lhelper = $_POST['lhelper'][$k];
	$lmarkup = $_POST['lmarkup'][$k];
	$ltotal = $_POST['ltotal'][$k];
	
	
	
	if ($ltotal == '0.00'){
	 $lstatus = "2";
	 } else {
	 $lstatus = "1";
	 }
	
	 $sql = "INSERT INTO quotedlabour VALUES (
	 NULL, 
	 $quoteid,
	 $k,
	 '" . mysql_real_escape_string($lqlabour) ."',
	 '" . mysql_real_escape_string($ldescription) ."',
	 '" . mysql_real_escape_string($llabour) ."',
	 '" . mysql_real_escape_string($lhelper) ."',
	 '" . mysql_real_escape_string($lmarkup) ."',
	 '" . mysql_real_escape_string($ltotal) ."',
	 '" . mysql_real_escape_string($lstatus) ."'
	 )";
	 mysql_query($sql) or die('Error 33 adding.  Check you fields and try again.');	 
}
}
//insert quoted labour




//update existing labour status
foreach ($_POST['lid'] as $s => $lid) {
$lid = $_POST['lid'][$s];
$ltotal = $_POST['ltotal'][$s];


if ($ltotal == '0.00'){
	 $lstatus = "2";
	 } else {
	 $lstatus = "1";
	 }

$sql1 = "UPDATE quotedlabour SET status = '" . mysql_real_escape_string($lstatus) ."', total = '" . mysql_real_escape_string($ltotal) ."' WHERE id = '$lid'"; 
  
$result3 = mysql_query($sql1) or die('Error, updating 222 failed.  Check you fields and try again.');

}
//update existing labour status




$lii=200;		
$result = mysql_query("SELECT * FROM quotedlabour WHERE quoteid = $quoteid ORDER BY linenumber");
while($row = mysql_fetch_array($result))
{

  $id=$row['id'];
  $qlabour=$row['qlabour'];
  $description=$row['description'];
  $labour=$row['labour'];
  $helper=$row['helper'];
  $markup=$row['markup'];
  $total=$row['total'];
  $status=$row['status'];
  
  if($status == 1) {     
    $active = "checked";
}
  if($status == 2) {     
    $active = "unchecked";
	}
 
$lqty="lqlabour-$lii";
$lqhelper="lhelper-$lii";
$lqlabour="llabour-$lii";
$lqtotal="ltotal-$lii";
$lqbox="lbox-$lii";
  

  echo "<tr>";
  echo "<input type='hidden' name='lid[]' value='" . $id . "'>";
  echo "<td align='center'><input type='text' name='lqlabour[]' id='" . $lqty . "' size='10' disabled='disabled' value='" . $qlabour . "'></td>";
  echo "<td align='center'>" . $description . "</td>";
  echo "<td align='center'><input type='text' name='llabour[]' id='" . $lqlabour . "' size='10' disabled='disabled' value='" . $labour . "'></td>";
  echo "<td align='center'><input type='text' name='lhelper[]' id='" . $lqhelper . "' size='10' disabled='disabled' value='" . $helper . "'></td>";
  echo "<td align='center'><input type='text' name='ltotal[]' id='" . $lqtotal . "' size='10' readonly value='" . $total . "'></td>";
  echo "<td align='center'><input type='checkbox' name='lbox[]' id='" . $lqbox . "' value='0' $active onblur='filllabour(this.id)'></td>";
  echo "</tr>";
  
  $lii=$lii+1;
  }
		

Hi All, I have the following code which is a form that pulls records from a database to show and it also allows you to dynamically add new rows to the database and im stumped.  when I submit a form the record that previously existed is updated perfectly but any time I add a new record it is adding it but one field called total is getting its value from the first row of the database instead of using the data from the form.  any help is much appreciated.

 

 

[flemingmike]

ive narrowed it down to an issue with the foreach.  its using the foreach count for the existing first then the newly added. I need to have it as 2 separate for each counts but not sure how I can because I need all the fields to be consistently named ie. name='ltotal[]' for the math that happens on the page.

[mac_gyver]

you would need to show us how you are dynamically adding elements to the form, i.e. we need to see all the relevant code that would be needed to reproduce the problem. my guess is the added elements don't have an ltotal[] field and your code is looping over the existing database rows ltotal values.

 

your code is also inserting all the submitted form data (existing and dynamically added elements), which doesn't make sense as that would duplicate rows in the database table.

 

lastly, what is the $_POST data - 

echo '<pre>',print_r($_POST,true),'</pre>';

edit: i have a recommendation for your $status value. rather than using non-descriptive values like 1,2,... use defined constants that would result in meaningful constant names for those values - 

define('STATUS_CHECKED',1);
define('STATUS_UNCHECKED',2);

then your code using those values would look and read like - 

if ($ltotal == '0.00'){
  $lstatus = STATUS_UNCHECKED;
} else {
  $lstatus = STATUS_CHECKED;
}

and - 

if($status == STATUS_CHECKED) {
  $active = "checked";
} else {
  $active = "unchecked";
}

[Psycho]

OK, let's go over several things:

 

1. When you have input fields that should be "grouped", it is best (IMO) to ensure there is a provided index that will group them. Using just [] and "assuming" that the appropriate fields will have the same index is problematic. Plus, you have checkbox values and the data will get screwed up since unchecked fields are not included in the POST data and the indexed will get mis-matched! I also prefer to have a single multi-dimensional array instead of several flat arrays. That way you can process the data with a single foreach() loop.

 

2. What is this?

 

if ($ltotal == '0.00'){
$lstatus = "2";
} else {
$lstatus = "1";
}

What is the significance of 1 and 2? Are these supposed to be some sort of true/false equivalent? If so, then you should use 0 and 1 as those will be interpreted as equivalents of the Boolean True / False. Plus, the ternary operator would work better for this as well.

 

3. You should not be using mysql_ functions they are long deprecated.

 

4. The code has separate logic for existing data vs. new data. That's a waste when you can do it all with one query using the ON DUPLICATE KEY UPDATE logic.

 

5. No need to create unique variables in the loop to create the form if you are only going to use them once. Just use the $row array values.

 

6. Don't use 'SELECT *', it is a waste. List out the fields you will use.

 

7. You have a readonly field for the total - but you use it in the UPDATE query. Why? Are you aware a user can override that readonly status?

 

8. The 'active' checkbox has a value of 0. Should be a 1 to be more logical.

 

9. You have description and lmarkup as part of the POST data processing, but there are no input fields for those values.