I maintain a small database that prints out UPC labels and tracks serial numbers. It does an OK job at this, but the code is starting to get a little crusty.<\/p>\n
I got a wild hair today and decided that I’d had enough with a really annoying update process. Here’s the table I was working with: <\/p>\n
<\/p>\n
\r\nCREATE TABLE operational_part_number (\r\n id INT AUTO_INCREMENT NOT NULL,\r\n operational_part_number VARCHAR(20) NOT NULL,\r\n style_id INT10) NOT NULL,\r\n drilling_set_id INT(10) NOT NULL,\r\n color_id INT(10) NOT NULL,\r\n is_front TINYINT(1) NOT NULL,\r\n has_slotted_holes TINYINT(1) NOT NULL,\r\n image_location VARCHAR(255),\r\n primary key (ID)\r\n);\r\n<\/pre>\nSo, for every drilling, color and style, I have to create a set of data to insert.
\nThere were 400+ rows in this table, and I’d just been asked to add another 100 rows. Of course, the GUI doesn’t include any admin support. So, I do this with a spreadsheet and awk every time. <\/p>\nToday, I ran this query: <\/p>\n
\r\nSELECT LEFT(operational_part_number, 3) AS result, \r\n style_id, \r\n is_front, \r\n has_slotted_holes \r\n FROM operational_part_number_old \r\n GROUP BY result;\r\n<\/pre>\nThe result was 12 rows. I discovered that the operational_part_number<\/b> was made up of three things: a prefix, the drilling_set, and the color. And there were only 12 prefixes.<\/p>\n
Some rework was in order:<\/p>\n
\r\nCREATE TABLE opn_prefix (\r\n opn_prefix VARCHAR(20) NOT NULL,\r\n style_id INT(10) NOT NULL,\r\n is_front TINYINT(1) NOT NULL,\r\n has_slotted_holes TINYINT(1) NOT NULL,\r\n image_location VARCHAR(255),\r\n primary key (opn_prefix)\r\n);\r\n<\/pre>\nAhhh. That’s better. Now, the magic comes in with a VIEW to make the web front-end happy:<\/p>\n
\r\n CREATE or REPLACE VIEW operational_part_number AS\r\n SELECT CONCAT( opn_prefix.opn_prefix,drilling_set.holes_count, color.code )\r\n AS operational_part_number,\r\n style_id,\r\n drilling_set.id AS drilling_set_id,\r\n color.id AS color_id,\r\n is_front, has_slotted_holes, image_location\r\n FROM opn_prefix, drilling_set, color;\r\n<\/pre>\nI put that all in place, and everything worked.<\/p>\n
The end result was that I added only one row to the opn_prefix table, and future updates to part numbers will be just as easy. Tomorrow I am tackling the finished goods part number mapping to UPCs. I’m not sure I’ll be able to automate the process as well.<\/p>\n","protected":false},"excerpt":{"rendered":"
I maintain a small database that prints out UPC labels and tracks serial numbers. It does an OK job at this, but the code is starting to get a little crusty. I got a wild hair today and decided that … Continue reading →<\/a><\/p>\n","protected":false},"author":1,"featured_media":0,"comment_status":"closed","ping_status":"closed","sticky":false,"template":"","format":"standard","meta":{"footnotes":""},"categories":[17],"tags":[],"class_list":["post-138","post","type-post","status-publish","format-standard","hentry","category-mysql"],"_links":{"self":[{"href":"https:\/\/www.chesnok.com\/daily\/wp-json\/wp\/v2\/posts\/138","targetHints":{"allow":["GET"]}}],"collection":[{"href":"https:\/\/www.chesnok.com\/daily\/wp-json\/wp\/v2\/posts"}],"about":[{"href":"https:\/\/www.chesnok.com\/daily\/wp-json\/wp\/v2\/types\/post"}],"author":[{"embeddable":true,"href":"https:\/\/www.chesnok.com\/daily\/wp-json\/wp\/v2\/users\/1"}],"replies":[{"embeddable":true,"href":"https:\/\/www.chesnok.com\/daily\/wp-json\/wp\/v2\/comments?post=138"}],"version-history":[{"count":0,"href":"https:\/\/www.chesnok.com\/daily\/wp-json\/wp\/v2\/posts\/138\/revisions"}],"wp:attachment":[{"href":"https:\/\/www.chesnok.com\/daily\/wp-json\/wp\/v2\/media?parent=138"}],"wp:term":[{"taxonomy":"category","embeddable":true,"href":"https:\/\/www.chesnok.com\/daily\/wp-json\/wp\/v2\/categories?post=138"},{"taxonomy":"post_tag","embeddable":true,"href":"https:\/\/www.chesnok.com\/daily\/wp-json\/wp\/v2\/tags?post=138"}],"curies":[{"name":"wp","href":"https:\/\/api.w.org\/{rel}","templated":true}]}}