Multiple checkbox values into database

PHP
1

Hi guys,

Could really do with some help from anyone. Here’s what I need to accomplish;

I have a lookup table that associates printers to cartridges. I also have a page for a cartridge that is able to assign printers to that particular cartridge. I’m using checkboxes within a form to select the printers that a cartridge fits. What I need to do is update the lookup table to 1. update, add new rows if not already present and delete an existing row if that printer is no longer associated.



<form action="" method="POST" name="cartupdate">
<table width="100%" border="0" cellpadding="2" cellspacing="2">
<td align="left">Link Printer To Cartridge</td>
<td>
<?php
$result = mysql_query("SELECT p.PID, p.Pname, i.CartID FROM printers p LEFT JOIN itemlookup i ON i.PID = p.PID WHERE p.CID = '$cid' GROUP BY p.Pname ASC");
while($row = mysql_fetch_array($result)) {
?>
<input type="checkbox" name="itemtoprint[]" id="itemtoprint" <?php if ($row[CartID]==$itmID) {echo 'checked';} ?> value="<?php echo $row['PID'];?>"><?php echo $row['Pname'];?><br />
<?php
}
?>
</td>
</tr>
<tr><td colspan="2"><hr /></td></tr>
<tr>
<td colspan="2"><input type="hidden" name="process" value="1">
</tr>
<tr>
<td colspan="2" align="center"><input name="Submit" type="Submit" id="Submit" value="update this item"></td>
</tr>
</table>
</form>

… I’m using implode to get the checkbox values


<?php
$itmtoprinter = ($_POST['itemtoprint']);
$pieces = implode(",",$itmtoprinter);
?>

I have a lookup table ‘itemlookup’ set up as follows;

lkID | PID | CartID
1 255 2
2 146 33
3 123 2

…and so on

my query is as follows;


&lt;?php
$sql = "INSERT INTO `itemlookup` (`ikID`, `PID`, `CartID`) VALUES ('NULL', '$pieces', '$cartridgetarget') ON DUPLICATE KEY UPDATE lkID=LAST_INSERT_ID(lkID), PID=$pieces";
$perform_insert = mysql_query($sql) or die(mysql_error());
?&gt;

i’ve not tested this yet as the thought occured to me that if the printer was deselected then how am I going to detete that row from the table?

Basically, I need to update, insert and delete rows in one query. Any suggestions or methods, advice would be greatly appreciated.

Cheers

2

Well you cant update, insert, and delete in one query. It’s gonna take at least 2.

Let me see if i’ve got this straight.
You have an N-M relationship between Printer and Cartridge.
You have a table in which you store… an imploded list of Cartridges? and a single PrinterID.

You’ve got an artificial (presumably autoincrementing) key but are trying to say ON DUPLICATE KEY; that’s… not gonna work. ikID is (unless i’m missing something) a pointless artificial key. You should use the natural key (PID?) to index the table. Each printer should appear in the list only once if you’re doing an imploded list; if not, the natural key is PID,CartID. Personally i’d recommend not imploding them.

You’ve got your first query down pretty well; you should really try and do all the insertions at once, though.

Assuming you’re not imploding the results (Each Printer-Cartridge pair has it’s own row), the second query is actually pretty simple;
DELETE FROM itemlookup WHERE PID= <theprinterid> AND CartID NOT IN (<imploded list of all CartID’s now associated to the printer>)

EDIT: rereading your post, the page design actually calls for the inverse of the above query; where CartID = <cartridge ID> AND PID NOT IN (<imploded list of printers>)

3

Hi StarLion,

Thanks for the reply. I have ditched the imploding. I’ve now used a for each loop for the posted values of the checkboxes and thought that I would have to also ditch the lkID column too as you have already mentioned it’s meaningless. The printer will appear more than once as I have both the printerID(PID) and the cartridgeID(CartID) in the same table, using it as purely a lookup table to match the two together. I’ve got myself a bit muddled as it should really be quite a simple task.

4

Well, consider this; you can STILL do all your inserting in a single query; and still use imploding (though doing it differently).


If (count($_POST['itemtoprint']) &gt; 0) {
  $clean = array_map('intval',$_POST['itemtoprint']); //Sanitize Input to Integer Values
  $query = "INSERT IGNORE INTO `itemlookup` (`PID`, `CartID`) VALUES ( ".$pid.",".implode("), (".$pid.", ",$clean).")";
}

Example run:


&lt;?php
$_POST['itemtoprint'] = array(0,2,4);
$pid = 2;

If (count($_POST['itemtoprint']) &gt; 0) {
  $clean = array_map('intval',$_POST['itemtoprint']); //Sanitize Input to Integer Values
  $query = "INSERT IGNORE INTO `itemlookup` (`PID`, `CartID`) VALUES ( ".$pid.",".implode("), (".$pid.", ",$clean).")";
}  

echo $query;
?&gt;

Output: INSERT IGNORE INTO itemlookup (PID, CartID) VALUES ( 2,0), (2, 2), (2, 4)

5

Hi StarLion,

Thanks for the reply. I have ditched the imploding. I’ve now used a for each loop for the posted values of the checkboxes and thought that I would have to also ditch the lkID column too as you have already mentioned it’s meaningless. The printer will appear more than once as I have both the printerID(PID) and the cartridgeID(CartID) in the same table, using it as purely a lookup table to match the two together. I’ve got myself a bit muddled as it should really be quite a simple task.

6

Sorry for the double post. Thanks StarLion for the code, I will take a look at it again today. Needed a break from it as it was driving me nuts…

7

OK…

I’ve looked at this and added your code and it does indeed work, to a point… The only thing is that the $pid and $clean need to be the other way around so as in the query I need to add PID then CartID not CartID after PID. I’ll try and reverse the variables so that it matches with the columns. Thanks for the help btw, really helpful. Very appreciated.

8

I’m a little confused here I admit. Having looked through the code and adjusting it I can now get the query to insert the data but it’s just adding and not just adding those that are new rows, adding everything. I’ve taken a quick look at ‘INSERT IGNORE’ too and from what I’ve read that it will ignore any errors and keep the query running. I need the process to look at the checkboxes ticked and display them in a dynamic checkbox list. If a checkbox is ticked that was not already checked, update the table with the relevant data and ignore those rows that already exist and finally, if a checkbox that is already ticked is unticked, remove that value from the database. All these actions are processed with a form.

Maybe it’s just me. I apologise StarLion if I come across as a bit ‘dim’ but just struggling to get this right in my head at the moment.

<?php
if (count($itmtoprinter) >0) {
$clean = array_map (‘intval’, $_POST[‘itemtoprint’]);
$sql = “INSERT IGNORE INTO itemlookup (CartID, PID) VALUES ( “.$cartridgetarget.”,”.implode(“), (”.$cartridgetarget.“, “,$clean).”)”;
echo $sql;
}
?>

results:
INSERT IGNORE INTO itemlookup (CartID, PID) VALUES ( 1,1147), (1, 1148), (1, 1149)

9

have you remembered to add a key to your database table on (CartID,PID) ?

The thing about ‘updating’ is that there’s nothing to update. The row either SHOULD exist, or it SHOULDNT.

The Insert query adds any entries that should exist but dont (the IGNORE part is meant to let the script ignore error messages about duplicate keys, and make them warnings, thus letting the script insert all of it’s new entries. effectively it’s there to represent “ON DUPLICATE KEY DO NOTHING” (which isnt real code))
The Delete query removes any entries that do exist but shouldnt.

10

The problem I have is that both CartID and PID are not unique in the table. I have used one row each to match the printer to the cartridge. So for example a single printer can take multiple cartridges as can multiple cartridges fit several printers. It’s all very frustrating. I’ll start again I think and go through it. Amazing how one small task can become so troublesome. Cheers StarLion for the help given so far.

Can’t I utilise the lkID column as a key as before? I can reintroduce this column if need be. Also, to run the DELETE part of the process should I run an addition query for example:

<?php
if (count($itmtoprinter) >0) {
$clean = array_map (‘intval’, $_POST[‘itemtoprint’]);
$sql = “DELETE IGNORE INTO itemlookup (CartID, PID) VALUES ( “.$cartridgetarget.”,”.implode(“), (”.$cartridgetarget.“, “,$clean).”)”;
echo $sql;
}
?>

Obviously I won’t run this until I know for sure that the query updates and adds properly.

11

CartID and PID are not unique -by themselves-

They ARE unique, however, as a duple. MySQL is capable of taking multiple-field keys. Your key (singular) should be (CartID,PID). You dont need to introduce any artificial fields to do this. (You could, but it would be a waste of database space)

You should be running a DELETE query, as outlined in the edit of post #2 in this thread.

12

very nicely stated :award: