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>
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>)
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.
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.
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.
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.
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.
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:
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.