Page 2 of 4

Re: [MOD] - Remove All Demo Data

Posted: Tue Dec 07, 2010 4:16 am
by OSWorX
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

Re: [MOD] - Remove All Demo Data

Posted: Tue Dec 07, 2010 11:29 pm
by SXGuy
yes, as far as im aware truncate resets all values to 0

Re: [MOD] - Remove All Demo Data

Posted: Tue Dec 07, 2010 11:42 pm
by SapporoGuy
Thanks SX for the answer!

Re: [MOD] - Remove All Demo Data

Posted: Fri Dec 24, 2010 5:35 am
by Stuff4Toys
php script did Not clear the table for "Latest Products" or the "Categories"

Re: [MOD] - Remove All Demo Data

Posted: Fri Dec 24, 2010 6:38 am
by SXGuy
just add the table names to the table array that you wish to clear.

Re: [MOD] - Remove All Demo Data

Posted: Thu Dec 30, 2010 12:48 am
by Brook
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.

Re: [MOD] - Remove All Demo Data

Posted: Thu Dec 30, 2010 1:07 am
by SapporoGuy
#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.

Re: [MOD] - Remove All Demo Data

Posted: Thu Dec 30, 2010 1:22 am
by SXGuy
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.

Re: [MOD] - Remove All Demo Data

Posted: Thu Dec 30, 2010 1:31 am
by Brook
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?

Re: [MOD] - Remove All Demo Data

Posted: Thu Dec 30, 2010 1:40 am
by SapporoGuy
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.

Re: [MOD] - Remove All Demo Data

Posted: Thu Dec 30, 2010 1:49 am
by Brook
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.

Re: [MOD] - Remove All Demo Data

Posted: Thu Dec 30, 2010 7:06 am
by SXGuy
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.

Re: [MOD] - Remove All Demo Data

Posted: Thu Dec 30, 2010 5:33 pm
by SapporoGuy
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.

Re: [MOD] - Remove All Demo Data

Posted: Fri Dec 31, 2010 1:50 am
by Brook
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.

Re: [MOD] - Remove All Demo Data

Posted: Fri Dec 31, 2010 6:00 am
by SapporoGuy
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.

Re: [MOD] - Remove All Demo Data

Posted: Wed Mar 16, 2011 6:14 pm
by proxisoft.net
ATTENTION: DO NOT INCLUDE "order_status" into this deleting routine otherwise your history order will not work!!!

Re: [MOD] - Remove All Demo Data

Posted: Fri Apr 22, 2011 11:28 am
by NitroLiq
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
"; } } ?>

Re: [MOD] - Remove All Demo Data

Posted: Sun May 29, 2011 3:43 am
by elaineben
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?

Re: [MOD] - Remove All Demo Data

Posted: Sun Jun 19, 2011 12:14 am
by opencart-templates
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

Re: [MOD] - Remove All Demo Data

Posted: Tue Jun 21, 2011 5:08 pm
by musa
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
:(