Reverse detail from Kakelbont MS 1, a fifteenth-century French Psalter. This image is in the public domain. Daniel Paul O'Donnell

Forward to Navigation

MYSQL Searches for use with Zencart

Posted: Oct 22, 2009 11:10;
Last Modified: Oct 19, 2012 08:10

---

2011 extract badge info

SELECT 
DISTINCT 
o.orders_id,
name.products_options_values, 
affiliation.products_options_values, 
o.customers_email_address
FROM 
orders_products_attributes AS name, 
orders_products_attributes AS affiliation, 
orders_products_attributes AS opa, 
orders AS o, 
orders_products AS op
WHERE 
name.orders_products_id = affiliation.orders_products_id
AND name.products_options = 'Name'
AND affiliation.products_options = 'Affiliation'
AND name.orders_id = o.orders_id
AND op.orders_id = o.orders_id
AND op.products_id = '69'

2011 extract registrations

SELECT o.orders_id, o.date_purchased, o.customers_id,
o.customers_name, op.products_id, op.products_name,
opa.products_options, opa.products_options_values, op.final_price
FROM orders AS o, orders_products AS op
LEFT JOIN orders_products_attributes AS opa 
ON op.orders_products_id = opa.orders_products_id
AND opa.products_options = 'Register for the 2011 TEI Conference'
WHERE op.orders_id = o.orders_id
AND op.products_id = '69'
ORDER BY op.products_id

Extract workshop registrations:

SELECT opa.products_options_id, opa.products_options,
opa.products_options_values, o.customers_name 
FROM orders AS o, orders_products AS op 
LEFT JOIN orders_products_attributes AS opa 
ON op.orders_products_id = opa.orders_products_id 
WHERE o.date_purchased > '2011-07-01 00:00:00' 
AND products_options_id > '27' 
AND op.orders_id = o.orders_id 
AND opa.products_options_values != 'No optional activity'
AND op.products_id='69' 
ORDER BY opa.products_options

Extract all orders on a product by product basis, ordered by product, including the option for year end of subscription if it is present and including any comments about refunds (note: the last two work only because I specifically the options I am looking for have excluded everything else).

SELECT o.orders_id, o.date_purchased, o.customers_id,
o.customers_name, op.products_id, op.products_name,
opa.products_options, opa.products_options_values, op.final_price,
osh.comments 
FROM orders AS o, orders_products AS op 
LEFT JOIN orders_products_attributes AS opa 
ON op.orders_products_id = opa.orders_products_id 
AND opa.products_options = 'Valid until the last day of'
LEFT JOIN orders_status_history AS osh 
ON op.orders_id = osh.orders_id
AND osh.comments LIKE '%REFUND INITIATED%'
WHERE op.orders_id = o.orders_id
AND o.date_purchased > '2011-09-30 23:59:59'
AND o.date_purchased < '2011-11-01 00:00:00'
ORDER BY op.products_id

To build a list of names and affiliations for conference badges (<2010):

SELECT name.products_options_values, affiliation.products_options_values
FROM orders_products_attributes AS name, 
orders_products_attributes AS affiliation
WHERE name.orders_products_id = affiliation.orders_products_id
AND name.products_options = 'Name'
AND affiliation.products_options = 'Affiliation';

Same thing, with email addresses:

SELECT name.products_options_values, 
affiliation.products_options_values, 
customers.customers_email_address
FROM orders_products_attributes AS name, 
orders_products_attributes AS affiliation, orders AS customers
WHERE name.orders_products_id = affiliation.orders_products_id
AND name.products_options = 'Name'
AND affiliation.products_options = 'Affiliation'
AND name.orders_id = customers.orders_id

The gets a list of subscribers:

SELECT customers.customers_name, customers.customers_email_address, 
workshop.products_options_values
FROM orders AS customers, orders_products_attributes AS workshop
WHERE customers.orders_id = workshop.orders_id 
AND (workshop.products_options_values = '2009'
OR workshop.products_options_values = '2010'
OR workshop.products_options_values = '2011'
OR workshop.products_options_values = '2012'
OR workshop.products_options_values = '2013')

This search pulls all the registrations for a specific workshop plus the number of orders in each case:

SELECT customers.customers_name, customers.customers_email_address, 
workshop.products_options_values, orders.products_quantity 
FROM orders AS customers, orders_products_attributes AS workshop, 
orders_products AS orders 
WHERE orders.products_name = 'Register for a Preconference Workshop' 
AND orders.orders_id = customers.orders_id 
AND orders.orders_id = workshop.orders_id 
AND workshop.products_options_values = 
'A TEI-based Publishing Workflow, Nov. 11, 15:00-18:00.'

This one builds a list of all invoices:

SELECT 
orders.lp_trans_num AS 1stData_Invoice_No, 
lp.transaction_reference_number AS 1stData_Reference_No,
orders.orders_id AS Zencart_Invoice_No,
orders.date_purchased AS Purchase_Date,
orders.billing_name AS Invoice_Name,
orders.order_total AS Invoice_Tot
FROM
orders,
linkpoint_api AS lp
WHERE 
orders.lp_trans_num > 0
AND
lp.lp_trans_num = orders.lp_trans_num;

This breaks the invoices down by item. It has one weakness: it can’t accommodate discount information from the table orders_total: this is because I need to do some kind of self-join on orders_total before using it: the table has 2-3 rows for each order (1 for sub-total, 1 for discounts (if any), potentially one more for coupons, and 1 for total), all in the same column.

SELECT 
orders.lp_trans_num AS 1stData_Invoice_No, 
lp.transaction_reference_number AS 1stData_Reference_No,
orders.orders_id AS Zencart_Invoice_No,
orders.date_purchased AS Purchase_Date,
orders.billing_name AS Invoice_Name,
orders.order_total AS Invoice_Tot,
products.products_name AS Item,
CONCAT(attributes.products_options,': ',attributes.products_options_values) AS Options,
products.final_price AS Unit_Price,
products.products_quantity AS Quantity,
products.final_price * products.products_quantity AS Item_SubTotal
FROM
orders,
orders_products AS products,
orders_products_attributes AS attributes,
linkpoint_api AS lp
WHERE 
orders.lp_trans_num > 0
AND
orders.orders_id = products.orders_id
AND
products.orders_id = attributes.orders_id
AND
products.orders_products_id = attributes.orders_products_id
AND
lp.lp_trans_num = orders.lp_trans_num
AND
(attributes.products_options = 'Valid until the last day of'
OR
attributes.products_options = 'Registration Category'
OR
attributes.products_options = 'Pre-conference Workshop')
;
----  

Comment [2]

  1. dan (Sat Nov 6, 2010 (08:01:11)) [PermLink]:

    Limited by order type: SELECT name.products_options_values, affiliation.products_options_values
    FROM orders_products_attributes AS name,
    orders_products_attributes AS affiliation, orders AS date, orders_products as product
    WHERE name.orders_products_id = affiliation.orders_products_id
    AND name.orders_id = date.orders_id
    AND product.orders_products_id = name.orders_products_id
    AND product.products_id = 55
    AND date.orders_id > 151
    AND name.products_options = ‘Name’
    AND affiliation.products_options = ‘Affiliation’
    LIMIT 0,100;

  2. dan (Sat Nov 6, 2010 (08:44:28)) [PermLink]:

    This one extracts all current subscriptions after the cut-off order date:

    SELECT customers.customers_name, customers.customers_email_address,
    date.products_options_values
    FROM orders AS customers, orders_products_attributes AS date
    WHERE customers.orders_id = date.orders_id
    AND date.orders_id > 151
    AND products_options_values > 2009
    LIMIT 0,100;

:
:

:

Textile help

Back to content

Search my site

Sections

Current teaching

Recent changes to this site

Tags

anglo-saxon studies, caedmon, citation, citation practice, citations, composition, computers, digital humanities, digital pedagogy, exercises, grammar, history, moodle, old english, pedagogy, research, student employees, students, study tips, teaching, tips, tutorials, unessay, universities, university of lethbridge

See all...

Follow me on Twitter

At the dpod blog