3 Replies Latest reply on Nov 10, 2009 11:32 AM by Matt Le Fevre

    need help to efficiently delete multiple items from a datagrid/mysql db...

    xandesign

      Afternoon all,

       

      I have a flex app which displays a datagrid showing data retrieved from a mysql database, the first column of the datagrid is a selectable checkbox and there is also a delete button.

       

      Users of my application can select either one or multiple checkboxes and then click the delete button. At this point the selected rows of data from the datagrid will be deleted from the mysql database.

       

      I' m not sure if I should loop through all the selected checkboxes from the datagrid and delete each item of data one at a time from the mysql database, removing each successfully delete item from the datagrid as I go or if I should do one multiple delete staement and then loop through the selected checkboxes again removing the data from the datagrid.Basically I'm looking to do this as efficiently as possible as it is a web app.

       

      Can anyone offer suggestions on the best way to go about this process?

       

       

      Thanks in advance,

       

      Xander

        • 1. Re: need help to efficiently delete multiple items from a datagrid/mysql db...
          Matt Le Fevre Level 4

          The way i currently do this in my project, is have all the entries in my datagrid loaded with ID value, the ID value corresponds to each item in database, eg;

           

          <Item><Name>Example</Name><ID>1</ID></Item>

           

          i have the datafield set to Name so ID is not displayed and is there only for manipulation purposes only.

           

          On deletion, i get the ID values for all the selected items, pass them to a PHP script which then simply runs through and removes each ID from the database.

           

          then at the end of the PHP script, output the updated data from the database, feed it into a result event and refresh the datagrids dataprovider.

           

          simple, fast and allows for error catching.

           

          (also you don't even need the checkboxs if you don't want, just use something like (for my example) "datagrid.selectedItem.Item.ID" to get the reference for deletion)

          1 person found this helpful
          • 2. Re: need help to efficiently delete multiple items from a datagrid/mysql db...
            xandesign Level 1

            I need the checkbox to let users select multiple rows in the datagrid to delete. I thought that the best way would be to use an sql statement like "DELETE FROM table_name WHERE id IN (1,2,3,..."), send it to php to execute against the mysql database.

             

            The only problem I have is how to update the datagrid to reflect which rows of data have been deleted. I can't just remove all the rows just incase during the delete process that 1 or more of the rows error and don't get removed from the database.

             

            Any thoughts on how to handle the error/datagrid update process?

             

             

            Thanks in advance,

             

            Xander

            • 3. Re: need help to efficiently delete multiple items from a datagrid/mysql db...
              Matt Le Fevre Level 4

              I need the checkbox to let users select multiple rows in the datagrid to delete.

               

               

              you could set the datagrid attribute allowMultipleSelection to true instead. It's not vital and the checkbox alternative will work, it's just another option

               

              The only problem I have is how to update the datagrid to reflect which rows of data have been deleted. I can't just remove all the rows just incase during the delete process that 1 or more of the rows error and don't get removed from the database.

               

               

              have the php print back all the entries in the database after deletion, that'll fill up your datagrid with the most updated version of your database, whilst removing the ones that have been deleted.

               

              use a result event on the HTTPService, just do datagrid.dataprovider = event.result.[xmlnode]

               

               

              you could also pass back the names of all the deleted fields, along with a boolean indicating if they were deleted or not.

               

               

              or you could store the before/after array collections and compare them on deletion complete to see which have been deleted successfully

               

              probably several other ways as well