(Solved) Can deleted items be removed?

Comments

13 comments

  • Avatar
    Chris Muench

    You would have to execute sql statements to remove items. NOTE: If there are any sales this will break them as they wouldn't reference any items anymore.

    You would need to use phpMyAdmin or other tool if you are using download version. If you are using cloud verison you would need to open a ticket

    SET FOREIGN_KEY_CHECKS=0;
    TRUNCATE TABLE phppos_items;
    TRUNCATE TABLE phppos_inventory;
    TRUNCATE TABLE phppos_inventory_counts_items;
    TRUNCATE TABLE phppos_items_taxes;
    TRUNCATE TABLE phppos_item_kits;
    TRUNCATE TABLE phppos_item_kit_items;
    TRUNCATE TABLE phppos_item_kits_tier_prices;
    TRUNCATE TABLE phppos_item_kits_taxes;
    TRUNCATE TABLE phppos_location_items;
    TRUNCATE TABLE phppos_location_items_taxes;
    TRUNCATE TABLE phppos_location_items_tier_prices;
    TRUNCATE TABLE phppos_location_item_kits;
    TRUNCATE TABLE phppos_location_item_kits_taxes;
    TRUNCATE TABLE phppos_location_item_kits_tier_prices;
    SET FOREIGN_KEY_CHECKS=1;

    1
    Comment actions Permalink
  • Avatar
    SterlingH

    Chris, thank you for your response. What purpose does the Cleanup Old Items serve? I was under the impression that it would remove the Item IDs for use again.

    0
    Comment actions Permalink
  • Avatar
    Chris Muench

    It removes the UPC + Product ID from deleted items so they can be re-used

    0
    Comment actions Permalink
  • Avatar
    SterlingH

    One last question, does your SQL query remove all items or just deleted items?

    0
    Comment actions Permalink
  • Avatar
    Chris Muench

    It removes all items. If you want jus deleted you would need to only run these queries

     

    SET FOREIGN_KEY_CHECKS=0;

    DELETE FROM phppos_items WHERE deleted = 1

    1
    Comment actions Permalink
  • Avatar
    SterlingH

    Do I need to 

    SET FOREIGN_KEY_CHECKS=1;

    after the two other queries?

    0
    Comment actions Permalink
  • Avatar
    Chris Muench

    no

    1
    Comment actions Permalink
  • Avatar
    Marc

    I assume this query:

    SET FOREIGN_KEY_CHECKS=0;

    DELETE FROM phppos_items WHERE deleted = 1

    does not remove images assigned to the original item? I ran it to remove over 3800 deleted items (which it did) but database size export went from 2.5G to 2.49G.  So I assume those 3800 images are still embedded in db?

     

    Is there a way to delete the images along with the item?

    0
    Comment actions Permalink
  • Avatar
    Chris Muench

    they are in phppos_app_files table and also referenced in phppos_item_images

    But be careful not to delete all as active items are in there too

    0
    Comment actions Permalink
  • Avatar
    Marc

    I restored the database with the copy I made before removing. Is there a way to run the query that will take each deleted item and it's associated image out in one fell swoop?

     

    0
    Comment actions Permalink
  • Avatar
    Chris Muench

    I don't recommend running the following; but the others should be fine. The reason is that past reporting will be messed up.

    DELETE FROM phppos_items WHERE deleted = 1;

     

    RUN THE FOLLOWING TO CLEANUP IMAGES

    SET FOREIGN_KEY_CHECKS=0;

    DELETE FROM phppos_app_files WHERE file_id IN(SELECT image_id FROM phppos_item_images WHERE item_id IN (SELECT item_id FROM phppos_items WHERE `deleted` = 1));

    DELETE FROM phppos_item_images WHERE item_id IN (SELECT item_id FROM phppos_items WHERE `deleted` = 1);

     

    0
    Comment actions Permalink
  • Avatar
    Marc

    Ohh! I didn't think about the reporting enough I think.  Will it break financial reports and everything?  I understand that it would mess up reports containing the specific item but would historical sales figures also change?

    0
    Comment actions Permalink
  • Avatar
    Marc

    Sweet!  The second query set worked like a charm!  left the items so that the reporting isnt broken but removed the images from them and knocked over 400M off my database!  Thank you!

    0
    Comment actions Permalink

Please sign in to leave a comment.