Table query builder

From Zen Cart(tm) Wiki
Revision as of 13:16, 10 September 2006 by Allcanadiansearch.ca (talk | contribs)
(diff) ← Older revision | Latest revision (diff) | Newer revision → (diff)
Jump to: navigation, search

Database Version

1.3.5


Description

Help.gif


Table Details

Primary Key

query_id

Indexed Fields

None.


Columns

query_id Primary Key

A unique, auto incremented value, to identify each query builder record.
Type: int(11)
Null: No
Default:
Extra: auto_increment


query_category

Help.gif
Type: varchar(40)
Null: No
Default:


query_name

Help.gif
Type: varchar(80)
Null: No
Default:


query_description

Help.gif
Type: text
Null: No
Default:


query_string

Help.gif
Type: text
Null: No
Default:


query_keys_list

Help.gif
Type: text
Null: No
Default:


Default Entries

query_id: 1
query_category: email
query_name: All Customers
query_description: Returns all customers name and email address for sending mass emails (ie: for newsletters, coupons, GV's, messages, etc).
query_string: select customers_email_address, customers_firstname, customers_lastname from TABLE_CUSTOMERS order by customers_lastname, customers_firstname, customers_email_address
query_keys_list:


query_id: 2
query_category: email,newsletters
query_name: All Newsletter Subscribers
query_description: Returns name and email address of newsletter subscribers
query_string: select customers_firstname, customers_lastname, customers_email_address from TABLE_CUSTOMERS where customers_newsletter = '1'
query_keys_list:


query_id: 3
query_category: email,newsletters
query_name: Dormant Customers (>3months) (Subscribers)
query_description: Subscribers who HAVE purchased something, but have NOT purchased for at least three months.
query_string: select o.date_purchased, c.customers_email_address, c.customers_lastname, c.customers_firstname from TABLE_CUSTOMERS c, TABLE_ORDERS o WHERE c.customers_id = o.customers_id AND c.customers_newsletter = 1 GROUP BY c.customers_email_address HAVING max(o.date_purchased) <= subdate(now(),INTERVAL 3 MONTH) ORDER BY c.customers_lastname, c.customers_firstname ASC
query_keys_list:


query_id: 4
query_category: email,newsletters
query_name: Active customers in past 3 months (Subscribers)
query_description: Newsletter subscribers who are also active customers (purchased something) in last 3 months.
query_string: select c.customers_email_address, c.customers_lastname, c.customers_firstname from TABLE_CUSTOMERS c, TABLE_ORDERS o where c.customers_newsletter = '1' AND c.customers_id = o.customers_id and o.date_purchased > subdate(now(),INTERVAL 3 MONTH) GROUP BY c.customers_email_address order by c.customers_lastname, c.customers_firstname ASC
query_keys_list:


query_id: 5
query_category: email,newsletters
query_name: Active customers in past 3 months (Regardless of subscription status)
query_description: All active customers (purchased something) in last 3 months, ignoring newsletter-subscription status.
query_string: select c.customers_email_address, c.customers_lastname, c.customers_firstname from TABLE_CUSTOMERS c, TABLE_ORDERS o WHERE c.customers_id = o.customers_id and o.date_purchased > subdate(now(),INTERVAL 3 MONTH) GROUP BY c.customers_email_address order by c.customers_lastname, c.customers_firstname ASC
query_keys_list:


query_id: 6
query_category: email,newsletters
query_name: Administrator
query_description: Just the email account of the current administrator
query_string: select 'ADMIN' as customers_firstname, admin_name as customers_lastname, admin_email as customers_email_address from TABLE_ADMIN where admin_id = $SESSION:admin_id
query_keys_list: