Community Forums

[1.4.x][MOD] - Remove All Demo Data

Community created contributions & mods for OpenCart 1.x

Re: [MOD] - Remove All Demo Data

Postby OSWorX » Mon Dec 06, 2010 3:16 pm

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
EU-Cookie-Solution OpenCart Rechtskonform - Buttonlösung
LiveSupport (Chat) Deutsches OpenCart / OCIE Forum
http://osworx.net :: Installation & Update Services for OpenCart, Custom Development
Individual modules - Payment modules - Shipping modules
Dt. Sprachpaket 1.5.x - Dt. Sprachpaket 2.x- Sofortüberweisung
User avatar
OSWorX
Global Moderator
 
Posts: 2874
Joined: Mon Jan 11, 2010 9:52 am
Location: Austria

Re: [MOD] - Remove All Demo Data

Postby SXGuy » Tue Dec 07, 2010 10:29 am

yes, as far as im aware truncate resets all values to 0
SXGuy
 
Posts: 1798
Joined: Sat Nov 07, 2009 1:07 pm

Re: [MOD] - Remove All Demo Data

Postby SapporoGuy » Tue Dec 07, 2010 10:42 am

Thanks SX for the answer!
930sc ... because it is fun!
User avatar
SapporoGuy
 
Posts: 1132
Joined: Mon Nov 01, 2010 6:29 am

Re: [MOD] - Remove All Demo Data

Postby Stuff4Toys » Thu Dec 23, 2010 4:35 pm

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
Stuff4Toys
 
Posts: 33
Joined: Wed Dec 08, 2010 11:18 pm
Location: SW Florida

Re: [MOD] - Remove All Demo Data

Postby SXGuy » Thu Dec 23, 2010 5:38 pm

just add the table names to the table array that you wish to clear.
SXGuy
 
Posts: 1798
Joined: Sat Nov 07, 2009 1:07 pm

Re: [MOD] - Remove All Demo Data

Postby Brook » Wed Dec 29, 2010 11: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.
Brook
 
Posts: 452
Joined: Tue Feb 23, 2010 11:15 am

Re: [MOD] - Remove All Demo Data

Postby SapporoGuy » Wed Dec 29, 2010 12:07 pm

#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
SapporoGuy
 
Posts: 1132
Joined: Mon Nov 01, 2010 6:29 am

Re: [MOD] - Remove All Demo Data

Postby SXGuy » Wed Dec 29, 2010 12:22 pm

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.
SXGuy
 
Posts: 1798
Joined: Sat Nov 07, 2009 1:07 pm

Re: [MOD] - Remove All Demo Data

Postby Brook » Wed Dec 29, 2010 12:31 pm

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?
Brook
 
Posts: 452
Joined: Tue Feb 23, 2010 11:15 am

Re: [MOD] - Remove All Demo Data

Postby SapporoGuy » Wed Dec 29, 2010 12:40 pm

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
SapporoGuy
 
Posts: 1132
Joined: Mon Nov 01, 2010 6:29 am

Re: [MOD] - Remove All Demo Data

Postby Brook » Wed Dec 29, 2010 12:49 pm

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.
Brook
 
Posts: 452
Joined: Tue Feb 23, 2010 11:15 am

Re: [MOD] - Remove All Demo Data

Postby SXGuy » Wed Dec 29, 2010 6:06 pm

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.
SXGuy
 
Posts: 1798
Joined: Sat Nov 07, 2009 1:07 pm

Re: [MOD] - Remove All Demo Data

Postby SapporoGuy » Thu Dec 30, 2010 4:33 am

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
SapporoGuy
 
Posts: 1132
Joined: Mon Nov 01, 2010 6:29 am

Re: [MOD] - Remove All Demo Data

Postby Brook » Thu Dec 30, 2010 12:50 pm

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.
Brook
 
Posts: 452
Joined: Tue Feb 23, 2010 11:15 am

Re: [MOD] - Remove All Demo Data

Postby SapporoGuy » Thu Dec 30, 2010 5:00 pm

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
SapporoGuy
 
Posts: 1132
Joined: Mon Nov 01, 2010 6:29 am

Re: [MOD] - Remove All Demo Data

Postby proxisoft.net » Wed Mar 16, 2011 5:14 am

ATTENTION: DO NOT INCLUDE "order_status" into this deleting routine otherwise your history order will not work!!!
proxisoft.net
 
Posts: 15
Joined: Sat Mar 12, 2011 11:40 am

Re: [MOD] - Remove All Demo Data

Postby NitroLiq » Thu Apr 21, 2011 10:28 pm

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
"; } } ?>
NitroLiq
 
Posts: 5
Joined: Sat Nov 20, 2010 10:49 pm

Re: [MOD] - Remove All Demo Data

Postby elaineben » Sat May 28, 2011 2:43 pm

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?
elaineben
 
Posts: 8
Joined: Wed May 11, 2011 7:02 am

Re: [MOD] - Remove All Demo Data

Postby opencart-templates » Sat Jun 18, 2011 11: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
fresh-shop.php
Tested for OC-1.5.0.5
Warning Remove all catalog data and orders. Also remove product images and database cache.
(2.51 KiB) Downloaded 454 times
Last edited by opencart-templates on Thu Jul 07, 2011 1:16 pm, edited 3 times in total.
User avatar
opencart-templates
 
Posts: 119
Joined: Mon May 16, 2011 6:24 am
Location: UK

Re: [MOD] - Remove All Demo Data

Postby musa » Tue Jun 21, 2011 4:08 am

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
musa
 
Posts: 9
Joined: Fri Jun 17, 2011 8:02 am

PreviousNext

Return to Free Contributions

Who is online

Users browsing this forum: No registered users and 7 guests

Hosted by Arvixe Web Hosting