Start Free Trial

asked on

SQL Insert Query Help

I am trying to export data from one table to another. I have a table `chambercontacts` that has 0 up to 20 or so rows of contacts for a company. The problem is that I need to import them into the Wordpress table `wp_postmeta` and I need to create a separate row for each field appending the (0-based) index to the end of the field name, and then another row that corresponds to a field_id that is static for each field (firstname, lastname, etc).

The source table is defined as:

CREATE TABLE IF NOT EXISTS `chambercontacts` (
`id` int(10) unsigned NOT NULL COMMENT 'ID',
  `modtimestamp` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Last Modification',
  `deleted` int(1) unsigned DEFAULT '0' COMMENT 'Deleted?',
  `company` int(10) unsigned NOT NULL COMMENT 'Company',
  `firstname` varchar(64) NOT NULL COMMENT 'First Name',
  `lastname` varchar(64) NOT NULL COMMENT 'Last Name',
  `division` varchar(128) DEFAULT NULL COMMENT 'Division',
  `title` varchar(128) DEFAULT NULL COMMENT 'Title',
  `photo` varchar(255) DEFAULT NULL COMMENT 'Photo'
) ENGINE=MyISAM AUTO_INCREMENT=9555 DEFAULT CHARSET=latin1 PACK_KEYS=1;

Open in new window

And the Wordpress table `wp_postmeta` is defined as:

CREATE TABLE IF NOT EXISTS `wp_postmeta` (
`meta_id` bigint(20) unsigned NOT NULL,
  `post_id` bigint(20) unsigned NOT NULL DEFAULT '0',
  `meta_key` varchar(255) COLLATE utf8mb4_unicode_ci DEFAULT NULL,
  `meta_value` longtext COLLATE utf8mb4_unicode_ci
) ENGINE=InnoDB AUTO_INCREMENT=418884 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

Open in new window

I have the following query so far, which checks the company name from 2 other tables, which I am using to give me the ID of the post (post_id) in Wordpress that I need to used to insert into the destination table:

SELECT 
 c.*, 
 p.ID
FROM `chambercontacts` c
 LEFT JOIN `directory` d ON c.company=d.id
 LEFT JOIN `wp_posts` p ON d.companyname LIKE p.post_title
WHERE 
 c.`deleted`=0 AND
 d.`deleted`=0
ORDER BY d.`companyname`

Open in new window

But I am not sure how to format the query so I can import it all because I don't know how many rows there are for a given company and my SQL skills along with Google skills haven't helped much.

What I need is to be able to insert records into the `wp_postmeta` table for the `firstname` , `lastname`, `division` and `title` fields, all other values from the source table do not matter. So something that would look like this:

INSERT INTO `wp_postmeta` (`meta_id`, `post_id`, `meta_key`, `meta_value`) VALUES
(NULL, 564, 'contacts_0_first_name', 'First Name 0'),
(NULL, 564, '_contacts_0_first_name', 'field_5790aac3a7334'),
(NULL, 564, 'contacts_0_last_name', 'Last Name 0'),
(NULL, 564, '_contacts_0_last_name', 'field_5790ab21a7335'),
(NULL, 564, 'contacts_0_division', 'Division 0'),
(NULL, 564, '_contacts_0_division', 'field_5790ab2ba7336'),
(NULL, 564, 'contacts_0_title', 'Title 0'),
(NULL, 564, '_contacts_0_title', 'field_5790ab3da7337'),
(NULL, 564, 'contacts_1_first_name', 'First Name 1'),
(NULL, 564, '_contacts_1_first_name', 'field_5790aac3a7334'),
(NULL, 564, 'contacts_1_last_name', 'Last Name 1'),
(NULL, 564, '_contacts_1_last_name', 'field_5790ab21a7335'),
(NULL, 564, 'contacts_1_division', 'Division 1'),
(NULL, 564, '_contacts_1_division', 'field_5790ab2ba7336'),
(NULL, 564, 'contacts_1_title', 'Title 1'),
(NULL, 564, '_contacts_1_title', 'field_5790ab3da7337');

Open in new window

Any help would be greatly appreciated!

You need to deconstruct (unpivot) the table. So the question is: What SQL engine do you use?
MySQL or SQL Server?

ASKER

MySQL :)

WordPress is only on mysql i think. And the question contains bacticks which is used only by mysql.

MSSQL topic removed.

Could you post some sample data from your tables and expected result.

This may get you started:

select
    c.id
  , case when cj.n = 1 then 'modtimestamp'
         when cj.n = 2 then 'deleted'
         when cj.n = 3 then 'company'
         when cj.n = 4 then 'firstname'
         when cj.n = 5 then 'lastname'
         when cj.n = 6 then 'division'
         when cj.n = 7 then 'title'
         when cj.n = 8 then 'photo'
     end
  , case when cj.n = 1 then cast(modtimestamp as varchar(255))
         when cj.n = 2 then cast(deleted as varchar(255))
         when cj.n = 3 then cast(company as varchar(255))
         when cj.n = 4 then cast(firstname as varchar(255))
         when cj.n = 5 then cast(lastname as varchar(255))
         when cj.n = 6 then cast(division as varchar(255))
         when cj.n = 7 then cast(title as varchar(255))
         when cj.n = 8 then cast(photo as varchar(255))
     end
from chambercontacts c
cross join (
            select 1 n union all
            select 2 n union all
            select 3 n union all
            select 4 n union all
            select 5 n union all
            select 6 n union all
            select 7 n union all
            select 8 n
           ) cj

Open in new window

when moving different columns into a single column, all data has to suit the data type of that single column, I had issues using cast/convert (not sure why) but below I used concat() which worked for my small trial:

select
    c.id as post_id
  , case when cj.n = 1 then 'modtimestamp'
         when cj.n = 2 then 'deleted'
         when cj.n = 3 then 'company'
         when cj.n = 4 then 'firstname'
         when cj.n = 5 then 'lastname'
         when cj.n = 6 then 'division'
         when cj.n = 7 then 'title'
         when cj.n = 8 then 'photo'
     end as meta_key
  , case when cj.n = 1 then concat(modtimestamp,'')
         when cj.n = 2 then concat(deleted,'')
         when cj.n = 3 then concat(company,'')
         when cj.n = 4 then firstname
         when cj.n = 5 then lastname
         when cj.n = 6 then division
         when cj.n = 7 then title
         when cj.n = 8 then photo
     end as meta_value
from chambercontacts c
cross join (
            select 1 n union all
            select 2 n union all
            select 3 n union all
            select 4 n union all
            select 5 n union all
            select 6 n union all
            select 7 n union all
            select 8 n
           ) cj

Open in new window

the result looks like this:

| id |     meta_key |                          meta_value |
|----|--------------|-------------------------------------|
|  1 | modtimestamp |                 2016-01-01 00:00:00 |
|  1 |      deleted |                                  22 |
|  1 |      company |                                  55 |
|  1 |    firstname |                             firstnm |
|  1 |     lastname |                              lastnm |
|  1 |     division |                              div001 |
|  1 |        title |                                 ceo |
|  1 |        photo | site/folder/sunfolder/file00989.jpg |

see http://sqlfiddle.com/#!9/fe3b03/5

Open in new window

ASKER

@PortletPaul, thank you that is quite helpful. I was able to tweak it and get it almost there. The only change now is I need to prepend a 0 based index for each row to the `meta_key` field. The index would increment with each row from the `chambercontacts` table for a given ID.

Example query where X would be replaced with the index, i.e. 0, 1, 2, etc. of the row

INSERT INTO `wp_postmeta` (`post_id`, `meta_key`, `meta_value`)
SELECT
  p.ID AS post_id,
  CASE
         WHEN cj.n = 1 THEN 'contacts_X_firstname'
         WHEN cj.n = 2 THEN '_contacts_X_firstname'
         WHEN cj.n = 3 THEN 'contacts_X_lastname'
         WHEN cj.n = 4 THEN '_contacts_X_lastname'
         WHEN cj.n = 5 THEN 'contacts_X_division'
         WHEN cj.n = 6 THEN '_contacts_X_division'
         WHEN cj.n = 7 THEN 'contacts_X_title'
         WHEN cj.n = 8 THEN '_contacts_X_title'
  END AS meta_key,
  CASE
         WHEN cj.n = 1 THEN c.firstname
         WHEN cj.n = 2 THEN 'field_5790aac3a7334'
         WHEN cj.n = 3 THEN c.lastname
         WHEN cj.n = 4 THEN 'field_5790ab21a7335'
         WHEN cj.n = 5 THEN c.division
         WHEN cj.n = 6 THEN 'field_5790ab2ba7336'
         WHEN cj.n = 7 THEN c.title
         WHEN cj.n = 8 THEN 'field_5790ab3da7337'
  END AS meta_value
FROM `chambercontacts` c
  LEFT JOIN `directory` d ON c.company=d.id
  LEFT JOIN `wp_posts` p ON d.companyname LIKE p.post_title
  CROSS JOIN (
            SELECT 1 n UNION ALL
            SELECT 2 n UNION ALL
            SELECT 3 n UNION ALL
            SELECT 4 n UNION ALL
            select 5 n UNION ALL
            select 6 n UNION ALL
            SELECT 7 n UNION ALL
            SELECT 8 n
           ) cj
WHERE
  c.deleted=0 AND
  d.deleted=0 AND
  p.ID>=3000;

Open in new window

Mmm ok this will require use of a local variable. I'm not able to write the code while on a train using just a single thumb.

I'll do what I can today to return to this howver monday is always a series of meetings....

Hopefully this will help:

SET @meta_id := 0;

SELECT
    meta_id
  , ID AS post_id
  , CASE
         WHEN cj.n = 1 THEN 'contacts_X_firstname'
         WHEN cj.n = 2 THEN '_contacts_X_firstname'
         WHEN cj.n = 3 THEN 'contacts_X_lastname'
         WHEN cj.n = 4 THEN '_contacts_X_lastname'
         WHEN cj.n = 5 THEN 'contacts_X_division'
         WHEN cj.n = 6 THEN '_contacts_X_division'
         WHEN cj.n = 7 THEN 'contacts_X_title'
         WHEN cj.n = 8 THEN '_contacts_X_title'
    END AS meta_key
  , CASE
         WHEN cj.n = 1 THEN c.firstname
         WHEN cj.n = 2 THEN 'field_5790aac3a7334'
         WHEN cj.n = 3 THEN c.lastname
         WHEN cj.n = 4 THEN 'field_5790ab21a7335'
         WHEN cj.n = 5 THEN c.division
         WHEN cj.n = 6 THEN 'field_5790ab2ba7336'
         WHEN cj.n = 7 THEN c.title
         WHEN cj.n = 8 THEN 'field_5790ab3da7337'
    END AS meta_value 
FROM (
      select  
             @meta_id as meta_id
           , cc.*
           , @meta_id := @meta_id + 1 
      from `chambercontacts` cc
     ) c
  CROSS JOIN (
            SELECT 1 n UNION ALL
            SELECT 2 n UNION ALL
            SELECT 3 n UNION ALL
            SELECT 4 n UNION ALL
            select 5 n UNION ALL
            select 6 n UNION ALL
            SELECT 7 n UNION ALL
            SELECT 8 n
           ) cj
;

Open in new window

ASKER

This is really very close, however I need the meta_id to reset to 0 for each individual p.ID value, so I am assuming that some sort of grouping would be needed to accomplish that. The `chambercontacts` `id` field is also a unique identifier that is 3000 less than the p.ID value if that is helpful. i.e. p.ID of 3001 is cc.id of 1

SET @meta_id := 0;

INSERT INTO `wp_postmeta` (`post_id`, `meta_key`, `meta_value`)
SELECT
  p.ID AS post_id,
  CASE
         WHEN cj.n = 1 THEN CONCAT('contacts_', meta_id, '_firstname')
         WHEN cj.n = 2 THEN CONCAT('_contacts_', meta_id, '_firstname')
         WHEN cj.n = 3 THEN CONCAT('contacts_', meta_id, '_lastname')
         WHEN cj.n = 4 THEN CONCAT('_contacts_', meta_id, '_lastname')
         WHEN cj.n = 5 THEN CONCAT('contacts_', meta_id, '_division')
         WHEN cj.n = 6 THEN CONCAT('_contacts_', meta_id, '_division')
         WHEN cj.n = 7 THEN CONCAT('contacts_', meta_id, '_title')
         WHEN cj.n = 8 THEN CONCAT('_contacts_', meta_id, '_title')
  END AS meta_key,
  CASE
         WHEN cj.n = 1 THEN c.firstname
         WHEN cj.n = 2 THEN 'field_5790aac3a7334'
         WHEN cj.n = 3 THEN c.lastname
         WHEN cj.n = 4 THEN 'field_5790ab21a7335'
         WHEN cj.n = 5 THEN c.division
         WHEN cj.n = 6 THEN 'field_5790ab2ba7336'
         WHEN cj.n = 7 THEN c.title
         WHEN cj.n = 8 THEN 'field_5790ab3da7337'
  END AS meta_value
FROM (
  SELECT
    @meta_id as meta_id,
    cc.*,
    @meta_id := @meta_id + 1
  FROM `chambercontacts` cc
) c 
  LEFT JOIN `directory` d ON c.company=d.id
  LEFT JOIN `wp_posts` p ON d.companyname LIKE p.post_title
  CROSS JOIN (
            SELECT 1 n UNION ALL
            SELECT 2 n UNION ALL
            SELECT 3 n UNION ALL
            SELECT 4 n UNION ALL
            select 5 n UNION ALL
            select 6 n UNION ALL
            SELECT 7 n UNION ALL
            SELECT 8 n
           ) cj
WHERE
  c.deleted=0 AND
  d.deleted=0 AND
  p.ID>=3000;

Open in new window

please provide sample data and the expected result, I don't follow your last post

only a small amount of sample data is needed
the expected result should relate to that sample

ASKER

No problem, see below

CREATE TABLE IF NOT EXISTS `chambercontacts` (
`id` int(10) unsigned NOT NULL COMMENT 'ID',
  `modtimestamp` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Last Modification',
  `deleted` int(1) unsigned DEFAULT '0' COMMENT 'Deleted?',
  `company` int(10) unsigned NOT NULL COMMENT 'Company',
  `firstname` varchar(64) NOT NULL COMMENT 'First Name',
  `lastname` varchar(64) NOT NULL COMMENT 'Last Name',
  `division` varchar(128) DEFAULT NULL COMMENT 'Division',
  `title` varchar(128) DEFAULT NULL COMMENT 'Title',
  `photo` varchar(255) DEFAULT NULL COMMENT 'Photo'
) ENGINE=MyISAM AUTO_INCREMENT=9555 DEFAULT CHARSET=latin1 PACK_KEYS=1;

--
-- Dumping data for table `chambercontacts`
--

INSERT INTO `chambercontacts` (`id`, `modtimestamp`, `deleted`, `company`, `firstname`, `lastname`, `division`, `title`, `photo`) VALUES
(2082, NULL, 0, 1, 'Troy', 'Mulgrew', 'General Manager', '', ''),
(3482, '2016-06-21 20:34:01', 0, 1, 'Pete', 'Charboneau ', NULL, 'Human Resources Director', ''),
(3492, NULL, 0, 1, 'Marlys', 'Hauser', '', 'Nurse', ''),
(968, NULL, 0, 1, 'Connie', 'Ashe', '', 'Controller', ''),
(967, NULL, 0, 1, 'Janelle', 'Avise', 'Purchasing Manager', '', ''),
(977, NULL, 0, 3, 'Rod', 'Lorenzen', 'Manager', '', ''),
(762, NULL, 0, 5, 'Bill', 'Casady', '', 'Co-Owner', ''),
(2070, NULL, 0, 5, 'Todd', 'Casady', '', 'Co-Owner', ''),
(6162, '2015-08-11 19:26:46', 0, 8, 'Roger', 'Kaput', NULL, 'Financial Consultant', ''),
(6152, '2015-08-11 19:25:48', 0, 8, 'Jeff', 'Isgrig', NULL, 'First Vice President-Investments', ''),
(3322, '2015-08-11 19:25:13', 0, 8, 'James', 'Hunt', NULL, 'Financial Consultant', ''),
(6182, '2015-08-11 19:27:05', 0, 8, 'Deb', 'Oetker', NULL, 'First Vice President-Investments', '');

Open in new window

CREATE TABLE IF NOT EXISTS `wp_posts` (
`ID` bigint(20) unsigned NOT NULL,
  `post_author` bigint(20) unsigned NOT NULL DEFAULT '0',
  `post_date` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_date_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_content` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `post_title` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `post_excerpt` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `post_status` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'publish',
  `comment_status` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'open',
  `ping_status` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'open',
  `post_password` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `post_name` varchar(200) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `to_ping` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `pinged` text COLLATE utf8mb4_unicode_ci NOT NULL,
  `post_modified` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_modified_gmt` datetime NOT NULL DEFAULT '0000-00-00 00:00:00',
  `post_content_filtered` longtext COLLATE utf8mb4_unicode_ci NOT NULL,
  `post_parent` bigint(20) unsigned NOT NULL DEFAULT '0',
  `guid` varchar(255) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `menu_order` int(11) NOT NULL DEFAULT '0',
  `post_type` varchar(20) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT 'post',
  `post_mime_type` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT '',
  `comment_count` bigint(20) NOT NULL DEFAULT '0'
) ENGINE=InnoDB AUTO_INCREMENT=53044 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;

--
-- Dumping data for table `wp_posts`
--

INSERT INTO `wp_posts` (`ID`, `post_author`, `post_date`, `post_date_gmt`, `post_content`, `post_title`, `post_excerpt`, `post_status`, `comment_status`, `ping_status`, `post_password`, `post_name`, `to_ping`, `pinged`, `post_modified`, `post_modified_gmt`, `post_content_filtered`, `post_parent`, `guid`, `menu_order`, `post_type`, `post_mime_type`, `comment_count`) VALUES
(3001, 1, '2015-10-16 12:02:21', '2015-10-16 12:02:21', '', 'JBS Swift & Co.', '', 'publish', 'closed', 'open', '', 'jbs-swift-co', '', '', '2016-09-28 19:05:02', '2016-09-28 19:05:02', '', 0, '', 0, 'directories', '', 0),
(3003, 1, '2015-10-14 14:14:28', '2015-10-14 14:14:28', '', 'Theisen''s Home Farm Auto', '', 'publish', 'closed', 'open', '', 'theisen''s-home-farm-auto', '', '', '2015-10-14 14:14:28', '2015-10-14 14:14:28', '', 0, '', 0, 'directories', '', 0),
(3005, 1, '2015-10-14 14:12:30', '2015-10-14 14:12:30', '', 'Central Iowa Farm Store', '', 'publish', 'closed', 'open', '', 'central-iowa-farm-store', '', '', '2015-10-14 14:12:30', '2015-10-14 14:12:30', '', 0, '', 0, 'directories', '', 0),
(3008, 1, '2015-10-14 14:49:14', '2015-10-14 14:49:14', '', 'Wells Fargo Advisors, LLC.', '', 'publish', 'closed', 'open', '', 'wells-fargo-advisors-llc.', '', '', '2015-10-14 14:49:14', '2015-10-14 14:49:14', '', 0, '', 0, 'directories', '', 0);

Open in new window

directory table:

CREATE TABLE IF NOT EXISTS `directory` (
`id` int(10) unsigned NOT NULL COMMENT 'ID',
  `modtimestamp` timestamp NULL DEFAULT CURRENT_TIMESTAMP ON UPDATE CURRENT_TIMESTAMP COMMENT 'Last Modification',
  `deleted` int(1) unsigned DEFAULT '0' COMMENT 'Deleted?',
  `companyname` varchar(100) NOT NULL DEFAULT '' COMMENT 'Company Name',
  `category` int(10) unsigned NOT NULL COMMENT 'Category 1',
  `category2` int(10) unsigned DEFAULT NULL COMMENT 'Category 2',
  `category3` int(10) unsigned DEFAULT NULL COMMENT 'Category 3',
  `category4` int(10) unsigned DEFAULT NULL COMMENT 'Category 4',
  `address1` varchar(32) NOT NULL DEFAULT '' COMMENT 'Address',
  `address2` varchar(32) DEFAULT NULL COMMENT 'Address 2',
  `city` varchar(32) NOT NULL DEFAULT 'Marshalltown' COMMENT 'City',
  `state` varchar(32) NOT NULL DEFAULT 'IA' COMMENT 'State',
  `zip` varchar(32) NOT NULL DEFAULT '50158' COMMENT 'Zip',
  `phone` varchar(64) NOT NULL DEFAULT '' COMMENT 'Phone',
  `fax` varchar(32) DEFAULT NULL COMMENT 'Fax',
  `email` varchar(128) DEFAULT NULL COMMENT 'Email Address',
  `url` varchar(100) DEFAULT NULL COMMENT 'Website',
  `facebook` varchar(128) DEFAULT NULL COMMENT 'Facebook Page',
  `twitter` varchar(128) DEFAULT NULL COMMENT 'Twitter Page',
  `linkedin` varchar(128) DEFAULT NULL COMMENT 'LinkedIn Page',
  `photo1` varchar(255) DEFAULT NULL COMMENT 'Logo/Photo 1',
  `photo2` varchar(255) DEFAULT NULL COMMENT 'Photo 2',
  `photo3` varchar(255) DEFAULT NULL COMMENT 'Photo 3',
  `hours` text COMMENT 'Hours',
  `directions` longtext COMMENT 'Directions',
  `description` longtext COMMENT 'Description',
  `keyword` varchar(500) DEFAULT NULL COMMENT 'Keywords'
) ENGINE=MyISAM AUTO_INCREMENT=3335 DEFAULT CHARSET=latin1;

--
-- Dumping data for table `directory`
--

INSERT INTO `directory` (`id`, `modtimestamp`, `deleted`, `companyname`, `category`, `category2`, `category3`, `category4`, `address1`, `address2`, `city`, `state`, `zip`, `phone`, `fax`, `email`, `url`, `facebook`, `twitter`, `linkedin`, `photo1`, `photo2`, `photo3`, `hours`, `directions`, `description`, `keyword`) VALUES
(5, '2015-10-14 19:12:30', 0, 'Central Iowa Farm Store', 464, 0, 0, 0, '203 Iowa Ave West', 'PO Box 561', 'Marshalltown', 'IA', '50158', '(641) 753-3996', '(641) 753-7452', '', 'www.centraliowafarmstore.com', NULL, NULL, NULL, '', '', '', NULL, NULL, NULL, 'Central Iowa Farm Store'),
(1, '2015-10-16 17:02:21', 0, 'JBS Swift & Co.', 684, 0, 0, 0, '402 North 10th Avenue', 'PO Box 280', 'Marshalltown', 'IA', '50158', '(641) 752-7131', '(641) 752-8497', NULL, 'www.jbsswift.com', NULL, NULL, NULL, '', '', '', NULL, NULL, NULL, 'Swift & Co.'),
(3, '2016-09-28 20:06:33', 0, 'Theisen''s Home Farm Auto', 464, 0, 0, 0, '602 Iowa Avenue West', NULL, 'Marshalltown', 'IA', '50158', '(641) 844-0129', '(641) 752-5898', NULL, 'www.theisens.com', NULL, NULL, NULL, '', '', '', NULL, NULL, NULL, 'Theisen Home Farm Auto'),
(8, '2015-10-14 19:49:14', 0, 'Wells Fargo Advisors, LLC.', 504, 0, 0, 0, '14 East Southridge Road', 'PO Box 778', 'Marshalltown', 'IA', '50158', '(641) 752-5401', NULL, '', 'www.wfadvisors.com', NULL, NULL, NULL, '', '', '', NULL, NULL, NULL, 'A.G. Edwards & Sons, Inc., Wachovia Securities, Wells Fargo Advisors, LLC.');

Open in new window

Then the output would look something like this:

post_id	meta_key			meta_value
3001 	contacts_0_firstname 	Connie
3001 	_contacts_0_firstname 	field_5790aac3a7334
3001 	contacts_1_firstname 	Troy
3001 	_contacts_1_firstname 	field_5790aac3a7334
3001 	contacts_2_firstname 	Pete
3001 	_contacts_2_firstname 	field_5790aac3a7334
3001 	contacts_3_firstname 	Marlys
3001 	_contacts_3_firstname 	field_5790aac3a7334
3001 	contacts_4_firstname 	Janelle
3001 	_contacts_4_firstname 	field_5790aac3a7334
3001 	contacts_0_lastname 	Ashe
3001 	_contacts_0_lastname 	field_5790ab21a7335
3001 	contacts_1_lastname 	Mulgrew
3001 	_contacts_1_lastname 	field_5790ab21a7335
3001 	contacts_2_lastname 	Charboneau
3001 	_contacts_2_lastname 	field_5790ab21a7335
3001 	contacts_3_lastname 	Hauser
3001 	_contacts_3_lastname 	field_5790ab21a7335
3001 	contacts_4_lastname 	Avise
3001 	_contacts_4_lastname 	field_5790ab21a7335
3001 	contacts_0_division 	
3001 	_contacts_0_division 	field_5790ab2ba7336
3001 	contacts_1_division 	General Manager
3001 	_contacts_1_division 	field_5790ab2ba7336
3001 	contacts_3_division 	
3001 	_contacts_3_division 	field_5790ab2ba7336
3001 	contacts_2_division 	NULL
3001 	_contacts_2_division 	field_5790ab2ba7336
3001 	contacts_4_division 	Purchasing Manager
3001 	_contacts_4_division 	field_5790ab2ba7336
3001 	contacts_0_title 	Controller
3001 	_contacts_0_title 	field_5790ab3da7337
3001 	contacts_1_title 	
3001 	_contacts_1_title 	field_5790ab3da7337
3001 	contacts_2_title 	Human Resources Director
3001 	_contacts_2_title 	field_5790ab3da7337
3001 	contacts_3_title 	Nurse
3001 	_contacts_3_title 	field_5790ab3da7337
3001 	contacts_4_title 	
3001 	_contacts_4_title 	field_5790ab3da7337

3003 	contacts_0_firstname 	Rod
3003 	_contacts_0_firstname 	field_5790aac3a7334
3003 	contacts_0_lastname 	Lorenzen
3003 	_contacts_0_lastname 	field_5790ab21a7335
3003 	contacts_0_division 	Manager
3003 	_contacts_0_division 	field_5790ab2ba7336
3003 	contacts_0_title 	
3003 	_contacts_0_title 	field_5790ab3da7337

3005 	contacts_0_firstname 	Bill
3005 	_contacts_0_firstname 	field_5790aac3a7334
3005 	contacts_1_firstname 	Todd
3005 	_contacts_1_firstname 	field_5790aac3a7334
3005 	contacts_0_lastname 	Casady
3005 	_contacts_0_lastname 	field_5790ab21a7335
3005 	contacts_1_lastname 	Casady
3005 	_contacts_1_lastname 	field_5790ab21a7335
3005 	contacts_0_division 	
3005 	_contacts_0_division 	field_5790ab2ba7336
3005 	contacts_1_division 	
3005 	_contacts_1_division 	field_5790ab2ba7336
3005 	contacts_0_title 	Co-Owner
3005 	_contacts_0_title 	field_5790ab3da7337
3005 	contacts_1_title 	Co-Owner
3005 	_contacts_1_title 	field_5790ab3da7337

3008 	contacts_0_firstname 	Deb
3008 	_contacts_0_firstname 	field_5790aac3a7334
3008 	contacts_1_firstname 	Jeff
3008 	_contacts_1_firstname 	field_5790aac3a7334
3008 	contacts_2_firstname 	Roger
3008 	_contacts_2_firstname 	field_5790aac3a7334
3008 	contacts_3_firstname 	James
3008 	_contacts_3_firstname 	field_5790aac3a7334
3008 	contacts_0_lastname 	Oetker
3008 	_contacts_0_lastname 	field_5790ab21a7335
3008 	contacts_1_lastname 	Isgrig
3008 	_contacts_1_lastname 	field_5790ab21a7335
3008 	contacts_2_lastname 	Kaput
3008 	_contacts_2_lastname 	field_5790ab21a7335
3008 	contacts_3_lastname 	Hunt
3008 	_contacts_3_lastname 	field_5790ab21a7335
3008 	contacts_0_division 	NULL
3008 	_contacts_0_division 	field_5790ab2ba7336
3008 	contacts_1_division 	NULL
3008 	_contacts_1_division 	field_5790ab2ba7336
3008 	contacts_2_division 	NULL
3008 	_contacts_2_division 	field_5790ab2ba7336
3008 	contacts_3_division 	NULL
3008 	_contacts_3_division 	field_5790ab2ba7336
3008 	contacts_0_title 	First Vice President-Investments
3008 	_contacts_0_title 	field_5790ab3da7337
3008 	contacts_1_title 	First Vice President-Investments
3008 	_contacts_1_title 	field_5790ab3da7337
3008 	contacts_2_title 	Financial Consultant
3008 	_contacts_2_title 	field_5790ab3da7337
3008 	contacts_3_title 	Financial Consultant
3008 	_contacts_3_title 	field_5790ab3da7337

Open in new window

Thanks for the help on this.

What information in chambercontacts is available to produce the post_Id? I'm afraid I'm still not understanding how I can know the structure of that post only from the contacts table

post_id      meta_key                  meta_value
3001       contacts_0_firstname       Connie
3001       _contacts_0_firstname       field_5790aac3a7334
3001       contacts_1_firstname       Troy
3001       _contacts_1_firstname       field_5790aac3a7334
3001       contacts_2_firstname       Pete
...
3001       _contacts_3_title       field_5790ab3da7337
3001       contacts_4_title
3001       _contacts_4_title       field_5790ab3da7337

3003       contacts_0_firstname       Rod
3003       _contacts_0_firstname       field_5790aac3a7334

ASKER

In the chambercontacts table the post_id is what is in the company column if you add 3000 to that value. The post_id matches what is in the wp_posts table as the ID (p.ID on the SELECT statement)

ASKER CERTIFIED SOLUTION

membership

This solution is only available to members.

To access this solution, you must be a member of Experts Exchange.

Start Free Trial

ASKER

It was throwing an error about p.id being unknown in the select clause so I used c.company as the key to differentiate on. The variable for meta_id wass resetting to 0 too frequently because the chambercontacts table is not sorted by the c.company field, so I added a sort by on that value and it looks to be working perfectly now (code below).

SET @meta_id := 0;

INSERT INTO `wp_postmeta` (`post_id`, `meta_key`, `meta_value`)
SELECT
  p.ID AS post_id,
  CASE
         WHEN cj.n = 1 THEN CONCAT('contacts_', meta_id, '_first_name')
         WHEN cj.n = 2 THEN CONCAT('_contacts_', meta_id, '_first_name')
         WHEN cj.n = 3 THEN CONCAT('contacts_', meta_id, '_last_name')
         WHEN cj.n = 4 THEN CONCAT('_contacts_', meta_id, '_last_name')
         WHEN cj.n = 5 THEN CONCAT('contacts_', meta_id, '_division')
         WHEN cj.n = 6 THEN CONCAT('_contacts_', meta_id, '_division')
         WHEN cj.n = 7 THEN CONCAT('contacts_', meta_id, '_title')
         WHEN cj.n = 8 THEN CONCAT('_contacts_', meta_id, '_title')
  END AS meta_key,
  CASE
         WHEN cj.n = 1 THEN c.firstname
         WHEN cj.n = 2 THEN 'field_5790aac3a7334'
         WHEN cj.n = 3 THEN c.lastname
         WHEN cj.n = 4 THEN 'field_5790ab21a7335'
         WHEN cj.n = 5 THEN c.division
         WHEN cj.n = 6 THEN 'field_5790ab2ba7336'
         WHEN cj.n = 7 THEN c.title
         WHEN cj.n = 8 THEN 'field_5790ab3da7337'
  END AS meta_value
FROM (
  SELECT
    @meta_id :=IF(@prev_value=cc.company,@meta_id+1,0) as meta_id,
    cc.*,
    @prev_value := cc.company
  FROM `chambercontacts` cc
    CROSS JOIN (
      SELECT @meta_id :=0,  @prev_value :=0
    ) vars
  ORDER BY cc.company
) c 
  LEFT JOIN `directory` d ON c.company=d.id
  LEFT JOIN `wp_posts` p ON d.companyname LIKE p.post_title
  CROSS JOIN (
            SELECT 1 n UNION ALL
            SELECT 2 n UNION ALL
            SELECT 3 n UNION ALL
            SELECT 4 n UNION ALL
            select 5 n UNION ALL
            select 6 n UNION ALL
            SELECT 7 n UNION ALL
            SELECT 8 n
           ) cj
WHERE
  c.deleted=0 AND
  d.deleted=0 AND
  p.ID>=3000;

Open in new window

I have another issue now however that I will post as a new topic, because although related to this schema it's a new issue that needs to be resolved as well for this to work. There are 2 additional rows that need to be added to wp_posts that have a count for the number of contacts. One row will have a meta_key of 'contacts' and the meta_value will be the count. The 2nd row meta_key will be '_contacts' and meta_value will always be 'field_5790aaada7333'. The post_id will be the same for both rows. So for example, using the sample data above, the rows would look like the following:

post_id 	meta_key 	meta_value
3001 	contacts 	5
3001 	_contacts 	field_5790aaada7333
3003 	contacts 	1
3003 	_contacts 	field_5790aaada7333
3005 	contacts 	2
3005 	_contacts 	field_5790aaada7333
3008 	contacts 	4
3008 	_contacts 	field_5790aaada7333

Open in new window

The other issue with this is that the rows may exist for a given already, and if so only the first row need to be updated with the actual number of contacts. My thought was to count the number of rows where the meta_key is like one of the meta_keys that is static, i.e. field_5790aac3a7334, for a given post_id, however I am unsure of how to either update the value of a row or insert 2 new rows if the rows do not exist.

The link to the new question is: here