Jump to content
Latest News: (loading..)
reflex-ocasion

Remove client database addresses

Recommended Posts

A customer can have multiple addresses but when their account is deleted only the primary address is deleted. How can the other addresses be deleted if they exist?

After 11 years working with osC I have the database a bit saturated and I'm doing cleaning. Spanish laws require to keep invoices for a minimum of 5 years, although they are recommended 6. From there, there is no obligation to keep obsolete old orders, they can be eliminated and that is what I am doing. The problem is that many have two addresses because they are gifts that are made to each other or that they want to receive it at home but that the invoice is made on behalf of the company or similar situations. When you delete those orders they only delete the main address, not the others, and that is filling the database little by little. I'm afraid that at any moment it will be corrupted.

Provisionally I am doing it manually but I would like to know if there is a way to do it automatically. Doing so is slow and heavy, very heavy.

There must be a simple form but right now I am also saturated checking one by one each data from the different tables in the database so as not to make an error.

Thanks in advance for the help, it is something that we would all appreciate since it had never been raised because it has not been given due importance.

Share this post


Link to post
Share on other sites

Hola Alfonso,

 

This is the relevant part of admin/customers.php:

      case 'deleteconfirm':
        $customers_id = tep_db_prepare_input($_GET['cID']);

        if (isset($_POST['delete_reviews']) && ($_POST['delete_reviews'] == 'on')) {
          $reviews_query = tep_db_query("select reviews_id from " . TABLE_REVIEWS . " where customers_id = '" . (int)$customers_id . "'");
          while ($reviews = tep_db_fetch_array($reviews_query)) {
            tep_db_query("delete from " . TABLE_REVIEWS_DESCRIPTION . " where reviews_id = '" . (int)$reviews['reviews_id'] . "'");
          }

          tep_db_query("delete from " . TABLE_REVIEWS . " where customers_id = '" . (int)$customers_id . "'");
        } else {
          tep_db_query("update " . TABLE_REVIEWS . " set customers_id = null where customers_id = '" . (int)$customers_id . "'");
        }

        tep_db_query("delete from " . TABLE_ADDRESS_BOOK . " where customers_id = '" . (int)$customers_id . "'");
        tep_db_query("delete from " . TABLE_CUSTOMERS . " where customers_id = '" . (int)$customers_id . "'");
        tep_db_query("delete from " . TABLE_CUSTOMERS_INFO . " where customers_info_id = '" . (int)$customers_id . "'");
        tep_db_query("delete from " . TABLE_CUSTOMERS_BASKET . " where customers_id = '" . (int)$customers_id . "'");
        tep_db_query("delete from " . TABLE_CUSTOMERS_BASKET_ATTRIBUTES . " where customers_id = '" . (int)$customers_id . "'");
        tep_db_query("delete from " . TABLE_WHOS_ONLINE . " where customer_id = '" . (int)$customers_id . "'");

From what I can see in that line:

tep_db_query("delete from " . TABLE_ADDRESS_BOOK . " where customers_id = '" . (int)$customers_id . "'");

All existing customer addresses should be deleted.

The code is from the latest 2.3.4 EDGE version.

 

rgds

Rainer

Share this post


Link to post
Share on other sites

@reflex-ocasion Alfonso have a look at this add on...I think it'll do what you want.  If you use order editor and take in store orders be careful since the order editor you use may or may not update the last login date when orders are entered.  

https://apps.oscommerce.com/eyKG8&inactive-user

For some additional information have a look at this thread.

Dan

Share this post


Link to post
Share on other sites

You may also want to look at the Database Optimizer. If you have never cleaned the database,  you will probably be surprised at how much useless data is removed after using this addon.

Share this post


Link to post
Share on other sites

After observing the code with a clearer head, I see that both OSC and an addon published by me (delete account) do exactly what I need.

Whether it is from the admin or the client who does it, all those extra addresses are automatically deleted when the account is deleted.

What I do not understand is because I have some more addresses that should have been deleted and have not been deleted.

I suspect that the problem already corrected comes from when I installed purchase without account in my store version 2.2rc2a.

At the time I reported that since its installation the same customer could register several times with the same email. They replied that it was impossible but I was seeing it so if it was possible.

After that I updated from zero to 2.3.4 and more than a year ago to BS. In none of these versions has this incidence been repeated.

If it happened that the account was not deleted when the session expired and the client wanted to buy again. The system said that it had to be identified because it recognized its email giving an error in the password, but when it requested a new password it did not provide it or it was useless because the system did not recognize it as a registered client.

Fortunately, that is already solved.

What I sell are photography accessories and my clients rarely repeat and when they do it has been years since the last purchase. That's why I do not want to eliminate inactive customers, because they can buy again.

As I said before, I have been using OsC for 11 years with 3 different versions of the platform and the problem comes from the first and part of the second version. I have also changed at least 5 times of hosting.

The fact that there is useless data, especially in the configuration table, I know perfectly, there are many badly programmed addons that add data to that table but then, when they are uninstalled, they are not deleted.

My problem is specifically in the address_book table and what I see caused a badly programmed or improperly installed addon the first time.

 

I thought it was a problem that could affect everyone and I was wrong, it only affects me, so I take my question for granted.

Thank you all for the interest shown!!!

Share this post


Link to post
Share on other sites

I'm not sure I follow what you said but I can say there were some versions of the PWA addon that didn't delete the accounts correctly. If that happened and you never cleaned that when moving the new version, then the non-assigned address book entries would stay. The Database Optimizer I mentioned has an option to remove orphan address book entries so I still suggest using it.

Share this post


Link to post
Share on other sites

@Jack_mcs Last night I did the test with this addon and at least the empty table session did not work. Use EDGE for what it's worth. Also to verify if the addon has updates has the wrong url, it is from another addon.

My first store was version 2.2rc2a when I installed PWA. Then I upgraded to 2.3.4 and now EDGE. Then the error in PWA follows because I have never cleaned those tables from the database.

By SuperTracker I know that the database can become very slow due to so much accumulated garbage. Since then I do periodically cleaning, but never from the customer tables or address_book.

Share this post


Link to post
Share on other sites

Be sure you have enabled it in the settings. And try running it using the manual method instead of cron, if that is how you tried.

Share this post


Link to post
Share on other sites

Checking the box is manual mode, right? It is what I have done. I have not tried the cron mode yet.

With whos_online, how many users are there at that time. When I see the sessions table I see how many sessions there are, many more than users. When executing the cleaning of the sessions table, a message says that the database has been optimized. When going to the sessions table, I continue to see exactly the same number of sessions as before the optimization.

Share this post


Link to post
Share on other sites

What gets deleted depends on the settings. The default setting for sessions is 14 days so any you see right now in Who's Online won't be removed.

Share this post


Link to post
Share on other sites

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

×