Post by OSWorX » Tue Dec 07, 2010 4:16 am

eyeweb wrote:Just thought I would update this.

ALthough your script does the job, (well with some slight modifications, such as closing the php off ;) ), ...
1. There is absolulety no need for a closing php.tag if the script is pure php
2. Much worther is the wrong opening php.tag <? which should be always <?php

Full Stack Web Developer :: Dedicated OpenCart Development & Support DACH Region
Contact for Custom Work / Fast Support.


User avatar
Guru Member

Posts

Joined
Mon Jan 11, 2010 10:52 pm
Location - Austria

Post by SXGuy » Tue Dec 07, 2010 11:29 pm

yes, as far as im aware truncate resets all values to 0

Active Member

Posts

Joined
Sun Nov 08, 2009 2:07 am

Post by SapporoGuy » Tue Dec 07, 2010 11:42 pm

Thanks SX for the answer!

930sc ... because it is fun!


User avatar
Active Member

Posts

Joined
Mon Nov 01, 2010 7:29 pm

Post by Stuff4Toys » Fri Dec 24, 2010 5:35 am

php script did Not clear the table for "Latest Products" or the "Categories"

OpenCart Install: www.ChargerPros.com
Shameless Plug: www.Stuff4Toys.com


User avatar
New member

Posts

Joined
Thu Dec 09, 2010 12:18 pm
Location - SW Florida

Post by SXGuy » Fri Dec 24, 2010 6:38 am

just add the table names to the table array that you wish to clear.

Active Member

Posts

Joined
Sun Nov 08, 2009 2:07 am

Post by Brook » Thu Dec 30, 2010 12:48 am

I have OpenCart v1.4.9.3 installed. I have an existing database. I am going to create a new database and new OpenCart install using an export of my existing database. I want to reset order, invoice, customer counters etc.

The only things that I want in my new database from my existing database are: (basically anything product related)
products
product descriptions
product catagories
product options
product images
product option images
product related
manufacturers
(I am sure there are more tables that need to be added to this product table list...)

I want to delete everything else in the database and reset all counters orders, invoices, customers, etc.

This is what I was planning on doing.... is this right?
1) Export existing database using PHP Admin
2) Import existing database to new database using PHP Admin
3) Delete all products that I no longer want
4) Delete all product categories that I no longer want
5) Delete all manufacturers that I no longer want
6) Run PHP delete_demo_data.php script (below)

I was planning on running the delete_demo_date.php PHP Script that was posted earlier in this post

Code: Select all

<?
include('config.php');

$db = mysqli_connect(DB_HOSTNAME, DB_USERNAME, DB_PASSWORD, DB_DATABASE);
$tables = array(
    'address',
    'category',
    'category_description',
    'category_to_store',
    'coupon',
    'customer',
    'download',
    'download_description',
    'manufacturer',
    'manufacturer_to_store',
    'product',
    'product_description',
    'product_discount',
    'product_featured',
    'product_image',
    'product_option',
    'product_option_description',
    'product_option_value',
    'product_option_value_description',
    'product_related',
    'product_special',
    'product_to_download',
    'product_to_store',
    'review',
    'store',
    'store_description',
    'product_tags',
    'order',
    'order_download',
    'order_history',
    'order_option',
    'order_product',
    'order_status',
    'order_total',
    'product_to_category',
    'coupon_description',
    'coupon_product',
);

// for each table add a delete line to script
foreach ($tables as $table) {
    $query = "TRUNCATE TABLE `oc_$table`;";
    $result = mysqli_query($db, $query);
    if (!$result) {
        echo "<p>The data from $table was not deleted</p>";
    echo "<p>$query</p>";
    } else {
        echo "<p>The data from $table was deleted</p>";
    }
}

?>
Two questions:
1) Is this a comprehensive list of all OpenCart tables that should be TRUNCATED for OpenCart version 1.4.9.3? If not what tables should I add?
2) I understand that TRUNCATE TABLE opencart_table will reset the counter in the table (is this correct?)

Any help would be appreciated.

Active Member

Posts

Joined
Wed Feb 24, 2010 12:15 am

Post by SapporoGuy » Thu Dec 30, 2010 1:07 am

#2.) as per the reploy above yes.

step 6.) will wipe out all the data in the tables listed in the script. so, possibly your steps above might be useless.

@ this script
opencart should really offer 3 types of data setup:
1.) full demo as of now
2.) data for only the sections that setup up configs and such.
3.) an option to #2 where you can choose your countries and zones. Erasing all those that you don't need can be a bit tedious.

930sc ... because it is fun!


User avatar
Active Member

Posts

Joined
Mon Nov 01, 2010 7:29 pm

Post by SXGuy » Thu Dec 30, 2010 1:22 am

Seems to me, that for now, your time is best served just backing up the tables you want to keep, and importing them into a new database once you migrate, then just delete the old database.

Active Member

Posts

Joined
Sun Nov 08, 2009 2:07 am

Post by Brook » Thu Dec 30, 2010 1:31 am

I guess my big challenge is wanting to reset the counters in the new database (Ex. customers, invoices, orders, etc.)

I want the counters in the new database to start at 1, any suggestions?

Active Member

Posts

Joined
Wed Feb 24, 2010 12:15 am

Post by SapporoGuy » Thu Dec 30, 2010 1:40 am

Use the script above.
You can always re-enter the products you want. (That is if you are using the demo data).

Backing up and then importing is not that hard but requires excel or similar software. You will also need to watch out how the product numbers change and their relations to other tables and such. This takes time and you would normally end up being faster with just redoing the products and such.

930sc ... because it is fun!


User avatar
Active Member

Posts

Joined
Mon Nov 01, 2010 7:29 pm

Post by Brook » Thu Dec 30, 2010 1:49 am

Thank you for your reply. The whole point of my post was that I already have the products in my existing database and want to import those products and all other related (product) tables to the new database. Sorry but, reentering the products is not a valid option. Way too time consuming :)

Again, my main challenge is understanding how to "reset" the counters in the customers, orders, invoices etc. tables and identifiying all the tables that need to have their counters reset.

Active Member

Posts

Joined
Wed Feb 24, 2010 12:15 am

Post by SXGuy » Thu Dec 30, 2010 7:06 am

well identifying which tables need to have the counters reset is probably easiest done by checking which have auto increment set.

But as SapporoGuy said, matching product numbers and their relations to other tables can be quite tricky.

I personally would avoid changing anything to do with orders and invoices, except for invoice and order numbers, their relation to product id's is gonna be tricky to re-match should you truncate product id's.

Active Member

Posts

Joined
Sun Nov 08, 2009 2:07 am

Post by SapporoGuy » Thu Dec 30, 2010 5:33 pm

Thought about this some more.

You can't.

Here is the scenario:
You dump your db, open it in excel, count all the number of products you have (by hand), copy that number to the auto-increment part in the db dump and paste it in (forgot if you need to +1 or not).

So, if you had lets say 300 products, your db was counting at 755.
You now have it reset to 300.
Next product that you make will nicely be product_id 301 .... perfect!!!! touch down.

errr, referee calls a penalty -- 15 yeards for use of leverage.
Sorry, no touch down.

What happened?
You made a NEW product with product_id of 301.
The is already a product with product_id in the db.

So, you can't just truncate the db and hope to start with fresh numbers.

You have to use excel and be careful about changing your numbers.

IF this were the settings table.
You could truncate the table.
Erase all the setting_id(s), need to keep the column blank by using '' (double single quotes) and then import into the table. All the data will go in starting from 1. This should have been done but it takes the time to drink half a coke-a-cola. Oh well.

930sc ... because it is fun!


User avatar
Active Member

Posts

Joined
Mon Nov 01, 2010 7:29 pm

Post by Brook » Fri Dec 31, 2010 1:50 am

Thank you for your post. I think, and I could be wrong, but it appears as though you are thinking that I want to reset the counter in the Product table. This is not true. I want to keep product ids as is. I am not changing the values in the opencart_product table.

This should be very simple, export the database via PHP Admin, import the database to the new database and TRUNCATE the correct tables (so that the counters are set back to 1). My challenge is really just identifiying all of the tables that I should TURNCATE.

So far I have identified:
opencart_customer
opencart_order
.... I am sure that there are more tables

The only tables that I am wanting to reset the counters are hopefully the orders, customers, invoices, etc.

Active Member

Posts

Joined
Wed Feb 24, 2010 12:15 am

Post by SapporoGuy » Fri Dec 31, 2010 6:00 am

Ahh, the above scenario was for products.

Are starting a new shop (as in go live on the net after developing on a closed machine)?

If so, then just go through your db and look at the script that has been provided and erase the product related tables.

If you are trying to bring in old data from other tables, the above scenario might happen.

930sc ... because it is fun!


User avatar
Active Member

Posts

Joined
Mon Nov 01, 2010 7:29 pm

Post by proxisoft.net » Wed Mar 16, 2011 6:14 pm

ATTENTION: DO NOT INCLUDE "order_status" into this deleting routine otherwise your history order will not work!!!

Newbie

Posts

Joined
Sun Mar 13, 2011 12:40 am

Post by NitroLiq » Fri Apr 22, 2011 11:28 am

I'm trying to get the script working locally but nothing is being deleted. I just get the echo output. Manually deleting or truncating in phpadmin works but I'd really like to get the script working. This is 1.4.9.4. All tables are prefixed with oc_. I've tried the script as is; tried changing the single quotes around oc_$table (in the truncate line) to striaght single quotes (thinking maybe the curly types weren't intentional...still new at php and sorting out the syntax). Anyone have an idea what I might be missing?

Code: Select all

The data from $table was not deleted

"; echo "

$query
"; } else { echo "

The data from $table was deleted
"; } } ?>

Newbie

Posts

Joined
Sun Nov 21, 2010 11:49 am

Post by elaineben » Sun May 29, 2011 3:43 am

i followed the instructions but didn't delete the files. i run script in sql window in phpmyadmin. this is what i got after:
Error
SQL query:

DELETE FROM oc_address;



MySQL said:

#1046 - No database selected





what did i miss here? i even tried the other one...like opening the browser after posting delete file in the root directory of opencart but my server is blocking it. what can i do?

Newbie

Posts

Joined
Wed May 11, 2011 8:02 pm

Post by opencart-templates » Sun Jun 19, 2011 12:14 am

mgirouard wrote:Howdy friends. I found this thread useful with a recent install so thanks :)

I've hacked up a quick PHP script which solves this problem, but in a little more OpenCart-ish way. Hopefully someone else finds it useful.

The Gist is available here:
http://gist.github.com/605619

and I've written up a quick example here:
http://www.lovemikeg.com/2010/09/30/cle ... mple-data/

Best to you all,
Mike G.
I have updated this to work with Opencart 1.5. All you need to do is place this file in the /upload file and access this file directly in your browser. Remember to delete this file once you have deleted the temp data.

Adding to mgirouard code to delete the temporary files installed with opencart:
  • image/data
    images/cache
    downloads
    system/cache
the images/data and images/cache

Attachments

Tested for OC-1.5.0.5
Warning Remove all catalog data and orders. Also remove product images and database cache.

Last edited by opencart-templates on Fri Jul 08, 2011 2:16 am, edited 3 times in total.

Advanced Professional Email Template
Customers Pre-Sale. Inc abandoned cart email
Order Follow-Up Email. Inc request review
Email Validation with ZeroBounce


User avatar
Active Member

Posts

Joined
Mon May 16, 2011 7:24 pm
Location - UK

Post by musa » Tue Jun 21, 2011 5:08 pm

While running the "destroy-sample-data.php" it throws error -

TRUNCATE TABLE product_featured .......................................... Error: Table 'opencart.product_featured' doesn't exist<br />Error No: 1146<br />TRUNCATE TABLE product_feature

And i've checked, there is no table product_feature in my opencart 1.5 installation
:(

=========================================================
codeTrio is specialized for
Enterprise E-Commerce Development
Web Application Development
Mobile Application Development
=========================================================


User avatar
Newbie

Posts

Joined
Fri Jun 17, 2011 9:02 pm

Who is online

Users browsing this forum: No registered users and 10 guests