updating/modifying db using dynamically created checkboxes

[CincoPistolero]

Hi all,

I hope I can explain this clearly. I have:

customerTBL, orderTBL, optionsTBL, orderOptTBL

 

orderOptTBL

orderOptID

orderID

optID

 

so it might look something like below with data in db

 

orderOptID  orderID    optID

1                1            1

2                1            2

3                1            5

 

etc

 

the orderOptTBL is populated by a list of dynamically created options with checkboxes next to them. I have a backend system that pull up the order, and lists all the options and checkboxes dynamically and places checkmarks in the boxes of the options that have been selected. So pulling out of the db is all good. Now, using the sample above, a sales person wants to change/add some of the options selected per the customer, we want to change the options selected from 1, 2, 5 to 1, 3, 4. So keep one the same, remove two and add two. I thinking I need a series of if statements with different sql functions like update, insert, yada yada. Checking more checkboxes is just an insert, but will the id=1 above be duplicated, and how do I get it to realize that orderID=1 had optID=2 checked before but now it needs to be removed.

 

Basically I need the check boxes to scroll through recognize whats there for that order and either do and update, insert or delete. Are there any tutorials out there for this sort of thing. I can attach my foreach code for doing inserts, but I'm confused on how to do the checking for existing options for the order and so on.

 

Any help is greatly appreciated.

[sloth456]

I can see you had great difficulty explaining that and I'm having difficulty understanding it.  Do you have the script hosted somewhere, if you give us the URL along with all the code for it and then try and explain again what needs doing, we might be able to help.

[CincoPistolero]

I've got part of it working. I'll try to explain then give some code below. I dynamically create two columns to check boxes through the use of type=hidden. Each check box has an ID, Price, and name. when i do a view source on this page. All data is correct. Now when I select check box 1, 7, 9, 12 and hit submit, what gets updated in db is: ID(1,2,3,4), price(1,2,3,4) and name(1,7,9,12) so the names are working, but all else is not. I'm as confused as my description. So I'll try to show via code below.

 

This is code that displays checkboxes. they display correctly and display checked boxes if that option is checked in db

<?php
$max2=2; 
//               $query2="SELECT op.optPricesID, o.name, op.basePrice FROM optPrices op, options o WHERE op.optionID = o.optionID AND op.drumTypeID=1"; 
                 $query2="SELECT op.optPricesID, o.name, op.basePrice, IF(op.optPricesID IN (SELECT optPricesID FROM woUpg WHERE awoID='$awoID'),1,0) as checked FROM optPrices op, options o WHERE op.optionID = o.optionID AND op.drumTypeID=1"; 
                $result2=mysql_query($query2); 
                $num2=mysql_num_rows($result2); 

                 
                if(empty($num2)){ 
                    $final2="\t<tr><td><center><span class='optTitles'>Nothing to Display</span></center></td></tr>\n"; 
                }else{ 
                   while($row2=mysql_fetch_array($result2,MYSQL_NUM)){ 
					if($row2[3]!=0) {
					 $array2[]="<input type=checkbox name='btOpt[]' checked value='".$row2[1]."'\">
					            <input type='hidden' name='btOptP[]' value='".$row2[2]."'\">
					            <input type='hidden' name='btOptI[]' value='".$row2[0]."'><br/>".$row2[1];
					             
					}else {
					$array2[]="<input type='checkbox' name='btOpt[]' value='".$row2[1]."'\">
					           <input type='hidden' name='btOptP[]' value='".$row2[2]."'\">
					           <input type='hidden' name='btOptI[]' value='".$row2[0]."'><br/>".$row2[1]; 

                       }
			}

                     
                mysql_free_result($result2); 
                 
                    $final2="<tr>\n"; 
                     
                    foreach($array2 as $thumbnail2){ 
                    if($counter2==$max2){ 
                        $counter2=1; 
                        $final2.="\n<tr>\n"; 
                    }else $counter2++; 
                        $final2.="\t<td align=\"left\" width=\"270\" height=\"20\"><span class='optTitles'>".$thumbnail2."</span></td>\n"; 
                    }                      
                        if($counter2){ 
                            if($max2-$counter2){ 
                                $final2 .= "\t<td width=\"270\" height=\"20\"> </td>\n";  
                            } 
                            $final2.="</tr>"; 
                            } 
                        } 
					echo " $final2"; 
?>

 

When I do a view source above, I get accurate pricing and IDS

[code<tr>
<td align="left" width="270" height="20"><span class='optTitles'><input type='checkbox' name='btOpt[]' value='Gauger Floor Cradle'">
          <input type='hidden' name='btOptP[]' value='180'">
          <input type='hidden' name='btOptI[]' value='59'><br/>Gauger Floor Cradle</span></td>
<td align="left" width="270" height="20"><span class='optTitles'><input type='checkbox' name='btOpt[]' value='Gauger Hanging Tom Mount'">
          <input type='hidden' name='btOptP[]' value='80'">
          <input type='hidden' name='btOptI[]' value='58'><br/>Gauger Hanging Tom Mount</span></td>

[/code]

 

Now, when I submit this to the db, the IDs and the prices become those of the first however many checkboxes I selected. If I select the last three, it sends ID and price for the first three. below is my update db page

<?php //Insert Artist Submission Upgrade and Upgrade Price into Work Order
foreach ($_POST['btOpt'] as $key => $this_tomBassOptN)
{
$this_tomBassOptP = $_POST['btOptP'][$key];
$this_tomBassOptI = $_POST['btOptI'][$key];
$query4 = "INSERT INTO woUpg (awoID, optPricesID,upgrade, upgradePrice, upgradeType)
VALUES ('$awoID','$this_tomBassOptI','$this_tomBassOptN','$this_tomBassOptP',1)";
$result4 = mysql_query($query4) or die ("Error in query: $query4. " . mysql_error());
}
?>

 

Don't know why my hiddens refuse to send the correct IDs and prices. Any help is greatly appreciated.