SitePoint Sponsor

User Tag List

Results 1 to 12 of 12
  1. #1
    SitePoint Addict
    Join Date
    Mar 2009
    Posts
    272
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)

    Multiple checkbox values into database

    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.

    Code:
    <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


    Code:
    <?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;

    Code:
    <?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());
    ?>
    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. #2
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    73 Post(s)
    Tagged
    0 Thread(s)
    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>)
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  3. #3
    SitePoint Addict
    Join Date
    Mar 2009
    Posts
    272
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #4
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    73 Post(s)
    Tagged
    0 Thread(s)
    Well, consider this; you can STILL do all your inserting in a single query; and still use imploding (though doing it differently).
    PHP Code:
    If (count($_POST['itemtoprint']) > 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:
    PHP Code:
    <?php
    $_POST
    ['itemtoprint'] = array(0,2,4);
    $pid 2;

    If (
    count($_POST['itemtoprint']) > 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;
    ?>
    Output: INSERT IGNORE INTO `itemlookup` (`PID`, `CartID`) VALUES ( 2,0), (2, 2), (2, 4)
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  5. #5
    SitePoint Addict
    Join Date
    Mar 2009
    Posts
    272
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #6
    SitePoint Addict
    Join Date
    Mar 2009
    Posts
    272
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #7
    SitePoint Addict
    Join Date
    Mar 2009
    Posts
    272
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #8
    SitePoint Addict
    Join Date
    Mar 2009
    Posts
    272
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #9
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    73 Post(s)
    Tagged
    0 Thread(s)
    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.
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  10. #10
    SitePoint Addict
    Join Date
    Mar 2009
    Posts
    272
    Mentioned
    0 Post(s)
    Tagged
    0 Thread(s)
    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. #11
    Keeper of the SFL StarLion's Avatar
    Join Date
    Feb 2006
    Location
    Atlanta, GA, USA
    Posts
    3,748
    Mentioned
    73 Post(s)
    Tagged
    0 Thread(s)
    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.
    Never grow up. The instant you do, you lose all ability to imagine great things, for fear of reality crashing in.

  12. #12
    SQL Consultant gold trophysilver trophybronze trophy
    r937's Avatar
    Join Date
    Jul 2002
    Location
    Toronto, Canada
    Posts
    39,322
    Mentioned
    63 Post(s)
    Tagged
    3 Thread(s)
    Quote Originally Posted by StarLion View Post
    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)
    very nicely stated
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL
    "giving out my real stuffs"


Tags for this Thread

Bookmarks

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •