Zen Cart Common SQL commands

Shop Daily maintenance process, it is inevitable to deal with a variety of bulk operations,

While most operations can be done via bulk upload (easy populate) and fast update modules,

But in the application of convenience and some functional adjustment, there is always a certain limitation;

All the data of the website are stored in the database, and there is no doubt that it is the most convenient way to change the direct operation database.

Since the database is to be moved, the SQL statement is definitely inseparable,

Daily with the site, to contact familiar with various code, here is another code, a bit to make a big head,

As a result, the following is a common batch operation SQL statement that may be involved in everyday applications,

Multiple simultaneous execution can also be executed on demand; A complete SQL statement with; End of the number.

The following command statement can be used to install SQL script submissions through the background tool

You can also copy and paste directly into the SQL entry in the corresponding database in PHPMyadmin

Batch Delete all products and other data


# ——————————————————–
# function: Empty the demo data of Zen Cart Chinese version 1.3.x
# Reminder: please do backup!!!
# Execute SQL Script using: Tools, Management page
#
# Zen Cart Chinese Blog
# ——————————————————–

Code:
# Clear Product categories, products, attributes
TRUNCATE TABLE categories;
TRUNCATE TABLE categories_description;

# Empty items and attributes
TRUNCATE TABLE media_clips;
TRUNCATE TABLE Media_manager;
TRUNCATE TABLE media_to_products;
TRUNCATE TABLE media_types;
TRUNCATE TABLE music_genre;
TRUNCATE TABLE Product_music_extra;
TRUNCATE TABLE product_types_to_category;
TRUNCATE TABLE Products;
TRUNCATE TABLE products_attributes;
TRUNCATE TABLE products_attributes_download;
TRUNCATE TABLE products_description;
TRUNCATE TABLE products_discount_quantity;
TRUNCATE TABLE products_notifications;
TRUNCATE TABLE products_options;
TRUNCATE TABLE products_options_types;
TRUNCATE TABLE products_options_values;
TRUNCATE TABLE products_options_values_to_products_options;
TRUNCATE TABLE products_to_categories;
TRUNCATE TABLE record_artists;
TRUNCATE TABLE Record_artists_info;
TRUNCATE TABLE Record_company;
TRUNCATE TABLE Record_company_info;

# Clear Recommended Items
TRUNCATE TABLE featured;

# Clear Promotional Items
TRUNCATE TABLE Salemaker_sales;

# Empty Specials
TRUNCATE TABLE Specials;

# Clear Group Price
TRUNCATE TABLE group_pricing;

# Empty Manufacturers and information
TRUNCATE TABLE manufacturers;
TRUNCATE TABLE Manufacturers_info;

# Clear Customer Reviews
TRUNCATE TABLE Reviews;
TRUNCATE TABLE reviews_description;

#清空前台注册用户 Order History Orders and other information used with caution
TRUNCATE TABLE ' Address_book ';
TRUNCATE TABLE ' Admin_activity_log ';
TRUNCATE TABLE ' counter ';
TRUNCATE TABLE ' counter_history ';
TRUNCATE TABLE ' customers ';
TRUNCATE TABLE ' Customers_basket ';
TRUNCATE TABLE ' Customers_info ';
TRUNCATE TABLE ' orders ';
TRUNCATE TABLE ' orders_products ';
TRUNCATE TABLE ' orders_status_history ';
TRUNCATE TABLE ' orders_total ';
TRUNCATE TABLE ' PayPal ';
TRUNCATE TABLE ' paypal_payment_status_history ';
TRUNCATE TABLE ' paypal_session ';
Update ' products_description ' set ' products_viewed ' = ' 0 ' WHERE ' products_viewed ' > ' 0 ';
Update ' products ' Set ' products_ordered ' = ' 0 ' WHERE ' products_ordered ' > ' 0 ';

Increase the number of common item data fields in two batches


#=====================================================================
# @ Increase Zen Cart database fields
#
# @ How to use: (The following three ways to import any one does not have to repeat the operation)
# @ One copy this file code, paste it into Zen Cart background----Submit in the SQL Installer or find the file in its browse file directly submit the import
# @ Two SQL entries in the current database directly in phpMyAdmin paste this file code, execute it
# @ Three in the current data in PHPMyadmin Click Import Item and import this file to execute
#
#=====================================================================
ALTER TABLE Categories MODIFY categories_image varchar (255);
ALTER TABLE categories_description MODIFY categories_name varchar (255);
ALTER TABLE Products MODIFY products_model varchar (255);
ALTER TABLE Products MODIFY products_image varchar (255);
ALTER TABLE products_description MODIFY products_name varchar (255);
ALTER TABLE address_book MODIFY entry_firstname varchar (255);
ALTER TABLE address_book MODIFY entry_lastname varchar (255);
ALTER TABLE categories_description MODIFY categories_name varchar (255);
ALTER TABLE configuration_group MODIFY configuration_group_title varchar (255);
ALTER TABLE customers_wishlist MODIFY products_name varchar (255);
ALTER TABLE customers_wishlist MODIFY wishlist_name varchar (255);
ALTER TABLE files_uploaded MODIFY files_uploaded_name varchar (255);
ALTER TABLE orders MODIFY customers_name varchar (255);
ALTER TABLE orders MODIFY customers_email_address varchar (255);
ALTER TABLE orders MODIFY delivery_name varchar (255);
ALTER TABLE orders MODIFY billing_name varchar (255);
ALTER TABLE orders MODIFY cc_owner varchar (255);
ALTER TABLE orders_products MODIFY products_name varchar (255);
ALTER TABLE orders_products MODIFY products_model varchar (255);
ALTER TABLE address_book MODIFY entry_street_address varchar (255);

Change the product part attribute in three batches



#==========================
# @ Zen Cart Chinese Blog
#==========================

#批量设置特价商品起始时间, where 0001-01-01 is no start time
UPDATE Specials SET specials_date_available = ' 0001-01-01 ';

#批量设置特价商品到期时间 where 2020-12-31 is the expiry time
UPDATE Specials SET expires_date = ' 2020-12-31 ';

#批量开启特价商品
UPDATE Specials SET status = ' 1 ';

#批量关闭特价商品
UPDATE Specials SET status = ' 0 ';

#批量设置全站商品免运费
UPDATE products SET product_is_always_free_shipping = ' 1 ';

#批量更改商品添加时间 of which 2011-10-01 for the adding period
UPDATE products SET products_date_added = ' 2011-10-01 ';

#批量更改商品重量 where 500 is the weight value
UPDATE products SET products_weight = ' 500 ';

#批量设置库存数量 where 99 is the quantity in stock
UPDATE ' products ' SET ' products_quantity ' = ' 99 ';

#批量设置特价, where +11.55 refers to an increase of 11.55, if the decrease is-11.55
UPDATE specials SET specials_new_products_price=specials_new_products_price+11.55;
UPDATE Products p,specials s SET p.products_price_sorter = s.specials_new_products_price WHERE p.products_id = s.products _id;

#批量设置原价, where +22.66 refers to an increase of 22.66, if the decrease is-22.66
UPDATE ' products ' SET ' products_price ' = ' products_price ' +22.66;

#批量替换商品描述指定字符
Update products_description Set Products_description=replace (products_description, ' Enter the character you want to find here ', ' enter the character to be replaced here ')

#批量配置图像参数
Update configuration set Configuration_value=replace (Configuration_value, ' original image width or height ', ' width or height of the image you want to modify ');

#按选项关健字段更新配置
Update ' configuration ' SET ' configuration_value ' = ' Updated value ' WHERE ' configuration '. ' Configuration_key ' = ' config Xiang Guanjian field ';

#按选项标题更新具体配置
Update ' configuration ' SET ' configuration_value ' = ' Updated value ' WHERE ' configuration '. ' configuration_title ' = ' config item title ';

#恢复后台管理员密码为 Admin used for password retrieval
UPDATE ' admin ' SET ' admin_pass ' = ' 351683ea4e19efe34874b501fdbf9792:9b ' WHERE ' admin '. ' admin_id ' = 1;

Add a fix by using bulk deletion caused by the background adding commodity properties dropdown Select Blank SQL
For details, see: Resolving Zen Cart Background Properties Option name dropdown blank issue


#==========================
# @ by KIRA
#==========================
INSERT into ' products_options_types ' VALUES (0, ' dropdown ');
INSERT into ' products_options_types ' VALUES (1, ' text ');
INSERT into ' products_options_types ' VALUES (2, ' single selection ');
INSERT into ' Products_options_types ' VALUES (3, ' multiple selection ');
INSERT into ' products_options_types ' VALUES (4, ' file ');
INSERT into ' Products_options_types ' VALUES (5, ' Read Only ');
INSERT into ' products_options_values ' (Products_options_values_id,language_id,products_options_values_name, Products_options_values_sort_order) VALUES (0,1, ' TEXT ', 0);



August 29, 2012 Update:
Fixed garbage attribute information generated after bulk deletion of items, resulting in the inability to use bulk update issues.

Sometimes when a product's original data contains attributes, when a bulk DELETE statement is used,
Causes the property information in the data table to be deleted incompletely,
Therefore, you cannot re-import the product data using the batch table.
Just look at the Ep_debug_log.txt file under the Tempep directory,
Generally there are the following content



Product debris corresponding to the following product_id (s) cannot is deleted by Easypopulate:
3:9d2458150f98263abbde3fa39f5a952c
It is recommended, that's delete this corrupted data using PhpMyAdmin.

Copy a sentence such as 3:9d2458150f98263abbde3fa39f5a952c in a format like
Then replace me with the corresponding junk information in the Fix SQL statement below
Replace with a copy of the above information and then import it via SQL


#=====================================================================
# @ Fix junk attribute information generated after bulk delete to prevent batch update issues from being used
#=====================================================================
DELETE from ' Customers_basket_attributes ' WHERE (
Convert (' customers_basket_attributes_id ' USING UTF8) like ' replace me with corresponding spam '
OR CONVERT (' customers_id ' USING UTF8) like ' replace me with the corresponding junk information '
OR CONVERT (' products_id ' USING UTF8) like ' replace me with the corresponding junk information '
OR CONVERT (' products_options_id ' USING UTF8) like ' replace me with the corresponding junk information '
OR CONVERT (' products_options_value_id ' USING UTF8) like ' replace me with the corresponding junk information '
OR CONVERT (' products_options_value_text ' USING UTF8) like ' replace me with the corresponding junk information '
OR CONVERT (' Products_options_sort_order ' USING UTF8) like ' replace me with the corresponding spam information ');

DELETE from ' Orders_products_attributes ' WHERE (
Convert (' orders_products_attributes_id ' USING UTF8) like ' replace me with corresponding spam '
OR CONVERT (' orders_id ' USING UTF8) like ' replace me with the corresponding junk information '
OR CONVERT (' orders_products_id ' USING UTF8) like ' replace me with the corresponding junk information '
OR CONVERT (' products_options ' USING UTF8) like ' replace me with the corresponding junk information '
OR CONVERT (' products_options_values ' USING UTF8) like ' replace me with the corresponding junk information '
OR CONVERT (' options_values_price ' USING UTF8) like ' replace me with the corresponding junk information '
OR CONVERT (' Price_prefix ' USING UTF8) like ' replace me with the corresponding junk information '
OR CONVERT (' product_attribute_is_free ' USING UTF8) like ' replace me with the corresponding junk information '
OR CONVERT (' products_attributes_weight ' USING UTF8) like ' replace me with the corresponding junk information '
OR CONVERT (' Products_attributes_weight_prefix ' USING UTF8) like ' replace me with the corresponding junk information '
OR CONVERT (' attributes_discounted ' USING UTF8) like ' replace me with the corresponding junk information '
OR CONVERT (' attributes_price_base_included ' USING UTF8) like ' replace me with the corresponding junk information '
OR CONVERT (' attributes_price_onetime ' USING UTF8) like ' replace me with the corresponding junk information '
OR CONVERT (' attributes_price_factor ' USING UTF8) like ' replace me with the corresponding junk information '
OR CONVERT (' attributes_price_factor_offset ' USING UTF8) like ' replace me with the corresponding junk information '
OR CONVERT (' attributes_price_factor_onetime ' USING UTF8) like ' replace me with the corresponding junk information '
OR CONVERT (' attributes_price_factor_onetime_offset ' USING UTF8) like ' replace me with the corresponding junk information '
OR CONVERT (' attributes_qty_prices ' USING UTF8) like ' replace me with the corresponding junk information '
OR CONVERT (' attributes_qty_prices_onetime ' USING UTF8) like ' replace me with the corresponding junk information '
OR CONVERT (' attributes_price_words ' USING UTF8) like ' replace me with the corresponding junk information '
OR CONVERT (' attributes_price_words_free ' USING UTF8) like ' replace me with the corresponding junk information '
OR CONVERT (' attributes_price_letters ' USING UTF8) like ' replace me with the corresponding junk information '
OR CONVERT (' attributes_price_letters_free ' USING UTF8) like ' replace me with the corresponding junk information '
OR CONVERT (' products_options_id ' USING UTF8) like ' replace me with the corresponding junk information '
OR CONVERT (' products_options_values_id ' USING UTF8) like ' replace me with the corresponding junk information '
OR CONVERT (' Products_prid ' USING UTF8) like ' replace me with the corresponding spam information ');




December 26, 2012 Update:
When troubleshooting a local mail environment configuration problem
Zen cart1.5x Background Unable to submit action item involving mail notification


Zen cart15x Program Default background there will be an email notification when you submit a change
When a local debug environment is configured with a problem or unsupported mail-related environment
Will cause the local installation of the Zen cart1.5x program behind the scenes involving mail notification cannot be done
A typical example is the inability to change the background password after the first installation
The following prompt appears when you log in with a temporary password

Email error:could not instantiate mail function. Please check admin->configuration->email options->email Transport.

When the above problem occurs, you can go to the database used by the current program
Submit the following SQL statement to resolve



#=====================================================================
# @ Resolve local Mail environment configuration problem when Zen cart1.5x background cannot submit an action item involving a mail notification
#=====================================================================
#关闭邮件发送
UPDATE ' configuration ' SET ' configuration_value ' = ' false ' WHERE ' configuration '. ' configuration_id ' = 267;

However, it is recommended to enable the Mail sending option after the program has been debugged locally.
Mail-enabled items can be enabled under the message parameters, store configuration, background,
You can also quickly recover directly using the following statement.



#启用邮件发送
UPDATE ' configuration ' SET ' configuration_value ' = ' true ' WHERE ' configuration '. ' configuration_id ' = 267;




December 29, 2012 Update:
Fixed an issue where new language packs were added with information such as items and classifications that could not be edited and saved


When you add a new language pack after the product has been added,
When you edit an existing product or category, you cannot save the newly added language in the background.
This is not the case with newly added goods or classifications.

This problem occurs because there are no corresponding data entries in the database.
While editing the original product, the program only performs the update operation, so it cannot be saved.

The solution to this problem is to re-add the product and the classification information after the multiple languages are added,
or run the following SQL statement to complement the missing multilingual content repair.
It is important to note that when more than one new language pack is added, the
The following fix SQL needs to be individually adjusted for multiple executions,
For specific adjustments, see detailed instructions later.

#=====================================================================
# @ Fix when adding a new language pack, the original to add information such as goods and classifications cannot be edited save problem
#=====================================================================
#设置默认语言的ID
SET @default_lang = 1;
#设置新语言的ID
SET @target_lang = 2;

INSERT into ' products_description ' (products_id, language_id, Products_name, Products_description, Products_url, products_viewed)
SELECT products_id, @target_lang as language_id, Products_name, Products_description, Products_url, products_viewed
From ' Products_description '
WHERE language_id [Email Protected]_lang
and products_id not in (
SELECT products_id from ' products_description '
WHERE language_id [Email Protected]_lang
);

#补齐缺失的语言 (category)
INSERT into ' categories_description ' (categories_id,language_id,categories_name,categories_description)
SELECT categories_id, @target_lang as Language_id,categories_name,categories_description
From ' Categories_description '
WHERE language_id [Email Protected]_lang
and categories_id not in (
SELECT categories_id from ' categories_description '
WHERE language_id [Email Protected]_lang
);

#补齐缺失的语言 (product meta)
INSERT into ' meta_tags_products_description ' (products_id, language_id, Metatags_title, Metatags_keywords, Metatags_ Description)
SELECT products_id, @target_lang as language_id, Metatags_title, Metatags_keywords, metatags_description
From ' Meta_tags_products_description '
WHERE [Email Protected]_lang
and products_id not in (
SELECT products_id from ' meta_tags_products_description '
WHERE language_id [Email Protected]_lang
);

#补齐缺失的语言 (category Meta)
INSERT into ' meta_tags_categories_description ' (categories_id, language_id, Metatags_title, Metatags_keywords, Metatags_description)
SELECT categories_id, @target_lang as language_id, Metatags_title, metatags_keywords,metatags_description
From ' Meta_tags_categories_description '
WHERE [Email Protected]_lang
and categories_id not in (
SELECT categories_id from ' meta_tags_categories_description '
WHERE language_id [Email Protected]_lang
);

The above SQL statement takes note of the following two sentences,


#设置默认语言的ID
SET @default_lang = 1;
#设置新语言的ID
SET @target_lang = 2;

The language IDs in these two SQL statements need to be tailored to the actual situation of their website,
The actual language ID of the current site is obtained by the following method,
Background, interface settings, language code, click on the corresponding language project,
Then look at the address in the address bar of the browser there are such address formats
HTTP//Your domain name/your admin directory name/languages.php?page=1&lid=2
The Red label typeface is the corresponding language ID

Tip: It's a good habit to back up the current database when it comes to bulk and database operations