Category: MySQL

MySQL Select with Where on a View calculated column results in Illegal mix of collations

Woocommerce, creating a view that wraps HTML around post_status, so post_status is now calculated column (yes stupid wordpress is ignoring newlines in the pasted SQL snippet) ,concat( case when coalesce(mecha_note, ”) <> ” then ” else ” end, case when post_status like ‘%cancel%’ or post_status like ‘%refund%’ then concat(”, post_status, ”) when post_status like ‘%completed%’ […]

Woocommerce -> MySQL -> vVariationMetaData vProductVariation

20181220 Product variations (attribute = length only currently) alter view vVariationMetaData as — 20181220 LSR — Product Variations meta data — Note is selected metadata only (not quite all) select po.post_parent, pm.post_id ,max(case when meta_key = ‘attribute_length’ then meta_value else null end) as pvm_attribute_length ,max(case when meta_key = ‘_sku’ then meta_value else null end) as […]

MFM Purchases

Initial versions 20181117 — Keep track of purchases ‘ Snapshot in case ever lost CREATE TABLE `m4m_wpProducts` (`wp_post_id` INT ,`wp_post_title` TEXT ,`wp_sku` TEXT , KEY `iPostID` (`wp_post_id`) USING BTREE ); insert into m4m_wpProducts (`wp_post_id`, `wp_post_title`, `wp_sku`) select ID, post_title, pm_sku from vProductMetaData; Purchases CREATE TABLE `m4m_purchases` (id INT NOT NULL AUTO_INCREMENT PRIMARY KEY ,`wp_post_id` INT […]

Woocommerce – Editing Orders

Paypal orders are sorta locked xx — wp1_postmeta update wp1_postmeta set meta_value = ‘0.00’ where post_id = 3387 and meta_key = ‘_order_shipping’; update wp1_postmeta set meta_value = ‘0.00’ where post_id = 3387 and meta_key = ‘_order_shipping_tax’; update wp1_postmeta set meta_value = ‘10.36’ where post_id = 3387 and meta_key = ‘_order_total’; — wp1_woocommerce_order_itemmeta update wp1_woocommerce_order_itemmeta oim […]

Woocommerce -> MySql -> vTaxonomyChained vTaxonomyPaths vProductCatsSummary

Product categories have parents… Products can be in more than 1 category xx create view vProductCatsSummary as select ID ,count(*) as cat_cnt ,min(tt.term_taxonomy_id) as cat_min ,max(tt.term_taxonomy_id) as cat_max ,( select group_concat( tex.name separator ‘ | ‘ ) from wp1_term_relationships trx JOIN wp1_term_taxonomy ttx ON trx.term_taxonomy_id = ttx.term_taxonomy_id JOIN wp1_terms tex ON ttx.term_id = tex.term_id where […]

Woocommerce -> MySQL -> VOrdersList

xx alter view vOrdersList as — 20180807 LSR — Added posthaste edit A link — 20180802 1019 select concat(‘<a href=”invoice.php?order=’,ID,’&copyinvoice=N” target=”_blank”>OL’, ID, ‘</a>’) as NewInv ,concat(‘<a href=”invoiceold.php?order=’,ID,'” target=”_blank”>OL’, ID, ‘</a>’) as OldInv ,post_date ,order_total ,payment_method_title as pay_method ,post_status ,concat(‘<a href=”mailto:’ ,coalesce(billing_email, ‘xxx@xxx.xxx’) ,’?Subject=MMMMM FFFF MMMM Order #’ — Company name here ,ID ,’&body=Thank%20you%20very%20much%20for%20your%20order%20which%20is%20with%20Post%20Haste.’ ,’%20%20An%20invoice%20is%20included%20in%20the%20package%0D%0A%0D%0A’ ,coalesce(ph_httpurlencoded, […]

Woocommerce -> MySql -> vOrderAddresses

xx Todo: change fromĀ  br to p tags alter view vOrderAddresses as — 20180822 LSR — Added Post_excerpt first 20 chars(Customer comment added at checkout) — 20180807 LSR — Added maint <a> link — 20180805 LSR — Original select ID ,concat(‘<a href=”addressedit.php?order=’,ID,'” target=”_blank”>OL’, ID, ‘</a>’) as edit_shipping_href ,case when concat( coalesce(billing_first_name, ”) ,coalesce(billing_last_name, ”) ,coalesce(billing_company, […]