Moving Posts from one site to Another.

blog_table_move.sql

rename table wp_79_posts to wp_tmp_posts, wp_79_postmeta to wp_tmp_postmeta, wp_79_terms to wp_tmp_terms, wp_79_term_taxonomy to wp_tmp_term_taxonomy, wp_79_term_relationships to wp_tmp_term_relationships, wp_79_p2p to wp_tmp_p2p, wp_79_p2pmeta to wp_tmp_p2pmeta;

create table wp_79_posts like wp_posts;
INSERT wp_79_posts SELECT * FROM wp_posts; 
create table wp_79_postmeta like wp_postmeta;
INSERT wp_79_postmeta SELECT * FROM wp_postmeta; 
--
create table wp_79_terms like wp_terms;
INSERT wp_79_terms SELECT * FROM wp_terms;
create table wp_79_term_taxonomy like wp_term_taxonomy;
INSERT wp_79_term_taxonomy SELECT * FROM wp_term_taxonomy;
create table wp_79_term_relationships like wp_term_relationships;
INSERT wp_79_term_relationships SELECT * FROM wp_term_relationships;

--
create table wp_79_p2p like wp_p2p;
INSERT wp_79_p2p SELECT * FROM wp_p2p;
create table wp_79_p2pmeta like wp_p2pmeta;
INSERT wp_79_p2pmeta SELECT * FROM wp_p2pmeta;

-- 
rename table wp_event_user to wp_79_event_user;
--

UPDATE wp_usermeta SET meta_key =  'wp_79_capabilities' WHERE meta_key =  'wp_capabilities' AND meta_value LIKE '%event_%';
UPDATE wp_usermeta SET meta_key =  'wp_79_capabilities' WHERE meta_key =  'wp_capabilities' AND meta_value LIKE '%city_%';
insert into wp_usermeta (meta_key, meta_value, user_id) select 'wp_capabilities', 'a:1:{s:10:"subscriber";s:1:"1";}', um.user_id from wp_usermeta um where um.meta_key='wp_79_capabilities';

-- remove event stuff from the main site table

delete from wp_postmeta  where post_id in (select p.id from wp_posts p where p.post_type="smw_event");
delete from wp_postmeta  where post_id in (select p.id from wp_posts p where p.post_type like "smw_%");
delete from wp_posts where post_type like "smw_%";

-- remove posts and pages form wp_79

delete from wp_79_postmeta  where post_id in (select p.id from wp_posts p where p.post_type="post");
delete from wp_79_postmeta  where post_id in (select p.id from wp_posts p where p.post_type="page");
delete from wp_79_posts where post_type in ("page", "post");

Some notes for a future move:

• Only move posts of a certain type (smw_*)
• also need to move postmeta
• user Roles don't transfer over unless the event plugin is turned on.
• Submit event goes into Global Site (not site 79)
• Some of the fields did not get carried over (event category, or event city).
• Also, we need to update the user role when they submit an event.

Get the number of favorites, registered, and attended events.
SELECT COUNT( TYPE ) , TYPE FROM wp_event_user GROUP BY TYPE