VbzCart/tables/cust names - Revision history

Woozle: useful query

2019-07-03T01:05:03Z

useful query

← Older revision		Revision as of 01:05, 3 July 2019
Line 20:		Line 20:
	)		)
	ENGINE = InnoDB;</source>		ENGINE = InnoDB;</source>
			==Utility==
			SQL query to get a list of cust records with multiple names:
			<source lang=mysql>SELECT
			g.*, n.Name
			FROM
			(SELECT
			c.ID, COUNT(n.ID) AS NameCount, GROUP_CONCAT(n.ID) AS NameIDs
			FROM
			cust_names AS n
			LEFT JOIN cust AS c ON n.ID_Cust = c.ID
			GROUP BY c.ID
			HAVING COUNT(n.ID) > 1) AS g
			LEFT JOIN
			cust_names AS n ON g.ID = n.ID_Cust
			WHERE
			n.ID IS NOT NULL
			ORDER BY g.ID
			</source>

Woozle at 00:34, 3 July 2019

2019-07-03T00:34:01Z

← Older revision		Revision as of 00:34, 3 July 2019
Line 7:		Line 7:
	*** WhenCnf (when confirmed) -- stamp whenever a new order is received that contains this email		*** WhenCnf (when confirmed) -- stamp whenever a new order is received that contains this email
	*** WhenEdt (when edited) -- stamp whenever an admin saves the record		*** WhenEdt (when edited) -- stamp whenever an admin saves the record
	**: same for {{~~vbzcart~~/table\|cust_phones}} and {{~~vbzcart~~/table\|cust_emails}}		**: same for {{l/vc/table\|cust_phones}} and {{l/vc/table\|cust_emails}}
	==SQL==		==SQL==
	<~~section begin~~=~~sql /><~~mysql>CREATE TABLE `cust_names` (		<source lang=mysql>CREATE TABLE `cust_names` (
	`ID` INT NOT NULL AUTO_INCREMENT,		`ID` INT NOT NULL AUTO_INCREMENT,
	`WhenEnt` DATETIME NOT NULL COMMENT "date when record was first created",		`WhenEnt` DATETIME NOT NULL COMMENT "date when record was first created",
Line 19:		Line 19:
	PRIMARY KEY(`ID`)		PRIMARY KEY(`ID`)
	)		)
	ENGINE = ~~MYISAM~~;</~~mysql>~~		ENGINE = InnoDB;</source>
	~~<section end=sql /~~>

Woozle: note about WhenUpd

2011-11-23T13:20:25Z

note about WhenUpd

← Older revision		Revision as of 13:20, 23 November 2011
Line 3:		Line 3:
	** '''2009-07-09''' moved to separate wiki page; no design changes		** '''2009-07-09''' moved to separate wiki page; no design changes
	** '''2011-11-23''' added WhenEnt, WhenUpd		** '''2011-11-23''' added WhenEnt, WhenUpd
			* '''Notes''':
			** (2011-11-23) Possibly WhenUpd should be removed and replaced with one or both of:
			*** WhenCnf (when confirmed) -- stamp whenever a new order is received that contains this email
			*** WhenEdt (when edited) -- stamp whenever an admin saves the record
			**: same for {{vbzcart/table\|cust_phones}} and {{vbzcart/table\|cust_emails}}
	==SQL==		==SQL==
	<section begin=sql /><mysql>CREATE TABLE `cust_names` (		<section begin=sql /><mysql>CREATE TABLE `cust_names` (

Woozle: added WhenEnt, WhenUpd

2011-11-23T13:04:50Z

added WhenEnt, WhenUpd

← Older revision		Revision as of 13:04, 23 November 2011
Line 2:		Line 2:
	* '''History''':		* '''History''':
	** '''2009-07-09''' moved to separate wiki page; no design changes		** '''2009-07-09''' moved to separate wiki page; no design changes
			** '''2011-11-23''' added WhenEnt, WhenUpd
	==SQL==		==SQL==
	<section begin=sql /><mysql>CREATE TABLE `cust_names` (		<section begin=sql /><mysql>CREATE TABLE `cust_names` (
	`ID` INT NOT NULL AUTO_INCREMENT,		`ID` INT NOT NULL AUTO_INCREMENT,
			`WhenEnt` DATETIME NOT NULL COMMENT "date when record was first created",
			`WhenUpd` DATETIME DEFAULT NULL COMMENT "date when record was last updated",
	`ID_Cust` INT COMMENT "core_custs.ID",		`ID_Cust` INT COMMENT "core_custs.ID",
	`Name` VARCHAR(127) COMMENT "customer's name, for shipping label",		`Name` VARCHAR(127) COMMENT "customer's name, for shipping label",

Woozle: moved from "customer tables" page

2009-07-09T19:36:18Z

moved from "customer tables" page

New page

==About==
* '''History''':
** '''2009-07-09''' moved to separate wiki page; no design changes
==SQL==
<section begin=sql /><mysql>CREATE TABLE `cust_names` (
`ID` INT NOT NULL AUTO_INCREMENT,
`ID_Cust` INT COMMENT "core_custs.ID",
`Name` VARCHAR(127) COMMENT "customer's name, for shipping label",
`NameSrch` VARCHAR(127) COMMENT "name with delimiters removed, for matching",
`isActive` BOOL DEFAULT FALSE COMMENT "NO=wrong spelling or name; use for searching",
PRIMARY KEY(`ID`)
)
ENGINE = MYISAM;</mysql>
<section end=sql />