18 Replies Latest reply on Mar 27, 2013 12:12 AM by osgood_

    PHP - Delete multiple database records using checkboxes?

    osgood_ Level 8

      This has been asked zillions of times over the years BUT my Google search has been exhausted. I can't find a coherent tutorial or explanation of how to achieve this.

       

      Anyone else have a link to a tutorial?

       

      I found and implemented one, with a few tweaks, that updates multiple price field records. Deleting most likely works in a similar way, looping through all the records, but can't get my head around it at the moment.

       

      Cheers

       

      Os.

        • 1. Re: PHP - Delete multiple database records using checkboxes?
          MurraySummers Level 8

          Exactly right. Loop through the checkboxes and for each one that is checked, execute a delete query.

          • 2. Re: PHP - Delete multiple database records using checkboxes?
            osgood_ Level 8

            MurraySummers wrote:

             

            Exactly right. Loop through the checkboxes and for each one that is checked, execute a delete query.

             

            Murray,

             

            I'll have a go tomorrow and see how far I get.  I know the principal, putting it into practice may be a bit more tricky.....should be interesting.

             

            Thanks

             

            Os.

            • 3. Re: PHP - Delete multiple database records using checkboxes?
              bregent Most Valuable Participant

              If the checkboxes represent different values for the same database column, then your loop should concatenate a comma seperated list of values to be used with the IN predicate in a single DELETE statement. That would be better than separate DELETE calls for each value.

               

              DELETE from MyTable WHERE MyColumn IN ('wine', 'beer', 'scotch')

               

              So your loops would create a string variable with a value of "'wine', 'beer', 'scotch'"

              • 4. Re: PHP - Delete multiple database records using checkboxes?
                osgood_ Level 8

                I was thinking I could just leave the checkbox values blank and then they would be returned as 'true' when selected?

                 

                I could then do something like using arrays to get the information for the loop.

                 

                $result = mysql_query("SELECT * FROM days ORDER BY daysDate");

                // start a counter in order to number the input fields for each record

                $i = 0;

                 

                $countRows = count($_POST['dayTitle']); //counts the records on the page

                 

                 

                // execute php only when submit button is activated

                <?php if (array_key_exists('submit', $_POST)) {

                $i = 0; // set loop counter

                while ($i <= $countRows) {

                $recordID = $_POST['recordID'][$i];

                $checkboxName = $_POST['checkboxName'][$i];

                If ($checkboxName == "true") {

                $query = "DELETE FROM days WHERE recordID = recordID';

                mysql_query($query) or die ("Error in query: $query");

                ++$i;

                }

                }

                }

                 

                I havent quite worked it out yet but that's my starting point.

                 

                Os

                • 5. Re: PHP - Delete multiple database records using checkboxes?
                  bregent Most Valuable Participant

                  I would setup your checkboxes as a group, and then iterate through them using a foreach loop.

                  http://www.html-form-guide.com/php-form/php-form-checkbox.html

                   

                  Unfortunately I'm not a php guy, but could probably fumble my way through it if you need some code.

                   

                  And again, I would not issue the DELETE statement within the loop. Rather, build a comma separated string of values in the loop, and then issie a single delete to delete all records in that string using the IN predicate. That would be faster and more reliable.

                  • 6. Re: PHP - Delete multiple database records using checkboxes?
                    MurraySummers Level 8

                    Really, unless you are dealing with a huge number of records, I don't think you could detect much difference in the two approaches, but it's always good to know the alternatives and why one would choose one over the other.

                    • 7. Re: PHP - Delete multiple database records using checkboxes?
                      osgood_ Level 8

                      Humm I'm not getting far with this.

                       

                      I have a checkbox repeat region  in the form:

                       

                      <input name="action[<?php echo $i; ?>]" type="checkbox"  value="<?php echo $row_rsDayList['dayID']; ?>">

                       

                      Which outputs:

                       

                      <input name="action[1]" type="checkbox"  value="4">

                       

                      <input name="action[2]" type="checkbox"  value="5">

                       

                      etc, etc.

                       

                       

                      I have this as the php code: ($_POST['dayTitle']) is a hidden field.

                       

                       

                      // find out how many records there are

                      $countRecords = count($_POST['dayTitle']);

                      // start a loop in order to update each record

                      $i = 0;

                      while ($i <= $countRecords) {

                      // define variable

                      $action = $_POST['action'][$i];

                      $query = "DELETE FROM days WHERE dayID = '$action'";

                      mysql_query($query) or die ("Error in query: $query");

                      ++$i; // end while loop

                      }

                      header ('Location: record_updated_successfully.php');

                       

                       

                      $action = $_POST['action'][$i]; -  is supposed to get the values 4, 5 etc from the checkbox value fields BUT it obviously doesn't.

                       

                      Which should convert the $action variable as below:

                       

                      $query = "DELETE FROM days WHERE dayID = '4'";

                       

                       

                      If I hand code 4 in place of $action the record gets deleted, so I'm close but not close enough.

                      • 8. Re: PHP - Delete multiple database records using checkboxes?
                        bregent Most Valuable Participant

                        Osgood, no need to increment your form field name. You're only interested in the value, correct? Just use an array name and you can then access the values in array for the checkboxes that were checked:

                         

                        <input name="action[]" type="checkbox"  value="<?php echo $row_rsDayList['dayID']; ?>">

                         

                        Also, what is the datatype of dayID?

                        • 9. Re: PHP - Delete multiple database records using checkboxes?
                          bregent Most Valuable Participant

                          Correct Murray. For performance it won't make much different until the rowcounts get high. But from a safe transaction standpoint - if you are not wrapping a transaction around the loop then you should only issue a single statement. You know, if anything can go wrong then it probably will

                          • 10. Re: PHP - Delete multiple database records using checkboxes?
                            osgood_ Level 8

                            bregent wrote:

                             

                            Osgood, no need to increment your form field name. You're only interested in the value, correct? Just use an array name and you can then access the values in array for the checkboxes that were checked:

                             

                            <input name="action[]" type="checkbox"  value="<?php echo $row_rsDayList['dayID']; ?>">

                             

                            Also, what is the datatype of dayID?

                             

                            Hi bregent,

                             

                            Ok, I'll give that a go - my brain is completely scrambled at the moment, temporarily I've moved on to getting some of the other stuff out of the way first and will be coming back to this very shortly to try again.

                             

                            The datatype for the dayID field is INT, its the primary-auto key.

                             

                            I've been whacking my head against the desk lol. I used the same technique a week or so ago to update multiple prices to a dataabase and it works like a dream. I've use pretty much exactly the same code just replacing the mySql DELETE FROM stuff and it won't work.

                             

                            Gotta love this stuff havent you. I reckon I must be making about 20p an hour at the current progress lol

                            • 11. Re: PHP - Delete multiple database records using checkboxes?
                              bregent Most Valuable Participant

                              OK, so again, disclaimer that I've never written a single php page. But try this.

                               

                              Again, use this for your form fields in the repeat region.

                               

                              <input name="action[]" type="checkbox"  value="<?php echo $row_rsDayList['dayID']; ?>">

                               

                              and this for your delete statement

                               

                              foreach ( $_POST['action'] as $dayID ) {

                                $query = "DELETE FROM days WHERE dayID = $dayID;

                                mysql_query($query) or die ("Error in query: $query");

                              }

                               

                               

                              >The datatype for the dayID field is INT, its the primary-auto key.

                               

                              FYI - Since the datatype is numeric, you don't need to wrap quotes around it.

                              • 12. Re: PHP - Delete multiple database records using checkboxes?
                                osgood_ Level 8

                                bregent wrote:

                                 

                                Osgood, no need to increment your form field name. You're only interested in the value, correct? Just use an array name and you can then access the values in array for the checkboxes that were checked:

                                 

                                 

                                Humm how would I go about accessing the array using your method. I can follow the link you provided earlier:

                                 

                                $result = $_POST['action'];

                                 

                                $count = count($result);

                                 

                                But how would I tie it in with the below (does this look like it would do something)?

                                 

                                $i = 0;

                                while ($i <= $count) {

                                $result = $result[$i];

                                $query = "DELETE FROM days WHERE dayID = '$result'";

                                mysql_query($query) or die ("Error in query: $query");

                                ++$i; // end while loop

                                }

                                 

                                Cheers

                                 

                                Os

                                • 13. Re: PHP - Delete multiple database records using checkboxes?
                                  osgood_ Level 8

                                  bregent wrote:

                                   

                                  OK, so again, disclaimer that I've never written a single php page. But try this.

                                   

                                  Again, use this for your form fields in the repeat region.

                                   

                                  <input name="action[]" type="checkbox"  value="<?php echo $row_rsDayList['dayID']; ?>">

                                   

                                  and this for your delete statement

                                   

                                  foreach ( $_POST['action'] as $dayID ) {

                                    $query = "DELETE FROM days WHERE dayID = $dayID;

                                    mysql_query($query) or die ("Error in query: $query");

                                  }

                                   

                                   

                                  >The datatype for the dayID field is INT, its the primary-auto key.

                                   

                                  FYI - Since the datatype is numeric, you don't need to wrap quotes around it.

                                   

                                  Ok will give it a go.

                                  • 14. Re: PHP - Delete multiple database records using checkboxes?
                                    bregent Most Valuable Participant

                                    Osgood, that would sorta work but you use of the $result variable is not correct.

                                    $result = $result[$i];

                                    You are assigning the value of the array element to the $result array variable, destroying the contents. And you are enclosing the $result in single quotes in the DELETE statement. Numeric values are not quoted.

                                     

                                    But you are making it more complicated that it needs to be. You don't need to even setup a counter. Use the 'foreach loop' from my previous post. I already gave you the complete code:

                                     

                                    foreach ( $_POST['action'] as $dayID ) {

                                      $query = "DELETE FROM days WHERE dayID = $dayID;

                                      mysql_query($query) or die ("Error in query: $query");

                                    }

                                    • 15. Re: PHP - Delete multiple database records using checkboxes?
                                      bregent Most Valuable Participant

                                      >Ok will give it a go.

                                       

                                      OK, ignore my last post - our replies are a bit out of sync

                                      • 16. Re: PHP - Delete multiple database records using checkboxes?
                                        osgood_ Level 8

                                        Whhoooha! you beauty! That seems to have worked a treat.

                                         

                                        Thanking you kindly.

                                         

                                        So simple if you have a good grasp of this stuff.

                                         

                                        Theres me trying to count and add up and all that when a couple of simple lines does the job efficiently

                                        • 17. Re: PHP - Delete multiple database records using checkboxes?
                                          bregent Most Valuable Participant

                                          You're welcome. You might want to this method instead:

                                           

                                          $dayList = ""

                                          foreach ( $_POST['action'] as $dayID ) {

                                           

                                              $dayList = $dayList . $dayID . ', ' ;  //build the list of days to delete

                                           

                                          }

                                           

                                          $dayList = substr($dayList, 0, strrpos($dayList, ',')) //remove the last comma - we don't want it

                                          $query = "DELETE FROM days WHERE dayID IN ($dayList)";

                                          mysql_query($query) or die ("Error in query: $query");

                                           

                                           

                                          As I mentioned before, this will execute a single delete. Hopefully I got the substring syntax right.

                                          • 18. Re: PHP - Delete multiple database records using checkboxes?
                                            osgood_ Level 8

                                            bregent wrote:

                                             

                                            You're welcome. You might want to this method instead:

                                             

                                            $dayList = ""

                                            foreach ( $_POST['action'] as $dayID ) {

                                             

                                                $dayList = $dayList . $dayID . ', ' ;  //build the list of days to delete

                                             

                                            }

                                             

                                            $dayList = substr($dayList, 0, strrpos($dayList, ',')) //remove the last comma - we don't want it

                                            $query = "DELETE FROM days WHERE dayID IN ($dayList)";

                                            mysql_query($query) or die ("Error in query: $query");

                                             

                                             

                                            As I mentioned before, this will execute a single delete. Hopefully I got the substring syntax right.

                                             

                                            Thanks,

                                             

                                            I've copied this to a snippet. I'm sure it will come in handy.

                                             

                                            I've not come across the 'IN' php before but obviously thats giving instructions to delete the dayID which matches in the array.

                                             

                                            Os