Advertisement
Not a member of Pastebin yet?
Sign Up,
it unlocks many cool features!
- CREATE TABLE `wpDusers` (
- `ID` bigint(20) UNSIGNED NOT NULL,
- `user_email` varchar(100) COLLATE utf8mb4_unicode_ci NOT NULL DEFAULT ''
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
- CREATE TABLE `wpDusermeta` (
- `umeta_id` bigint(20) UNSIGNED NOT NULL,
- `user_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 DEFAULT CHARSET=utf8mb4 COLLATE=utf8mb4_unicode_ci;
- CREATE TABLE `ST_users` (
- `wpid` int(11) NOT NULL,
- `email` varchar(100) NOT NULL,
- `wp_role` varchar(100) NOT NULL,
- `role` varchar(100) AS (MID(`wp_role`, LOCATE('"', `wp_role`)+1, LOCATE('"', MID(`wp_role`, LOCATE('"', `wp_role`)+1 , 50))-1)) PERSISTENT,
- `role_id` int(11) DEFAULT '1'
- ) ENGINE=InnoDB DEFAULT CHARSET=utf8;
- ALTER TABLE `ST_users`
- ADD PRIMARY KEY (`wpid`),
- ADD KEY `wpid` (`wpid`);
- ALTER TABLE `ST_users`
- MODIFY `wpid` int(11) NOT NULL AUTO_INCREMENT;
- ALTER TABLE `ST_users`
- MODIFY `wpid` int(11) NOT NULL AUTO_INCREMENT;
- ALTER TABLE `wpDusers`
- ADD PRIMARY KEY (`ID`),
- ADD KEY `user_email` (`user_email`);
- ALTER TABLE `wpDusers`
- MODIFY `ID` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;
- ALTER TABLE `wpDusers`
- MODIFY `ID` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;
- ALTER TABLE `wpDusermeta`
- ADD PRIMARY KEY (`umeta_id`),
- ADD KEY `user_id` (`user_id`),
- ADD KEY `meta_key` (`meta_key`(191));
- ALTER TABLE `wpDusermeta`
- MODIFY `umeta_id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;
- ALTER TABLE `wpDusermeta`
- MODIFY `umeta_id` bigint(20) UNSIGNED NOT NULL AUTO_INCREMENT;
- -- TRIGGER
- DELIMITER $$
- CREATE TRIGGER `ST_copy_users_v2` AFTER INSERT ON `wpDusers` FOR EACH ROW BEGIN
- DECLARE id_i int(11);
- DECLARE email_i varchar(100);
- DECLARE role_i varchar(100);
- SELECT NEW.ID, NEW.user_email,
- wpDusermeta.meta_value into id_i, email_i, role_i
- FROM wpDusers
- INNER JOIN wpDusermeta ON wpDusers.ID = wpDusermeta.user_id
- WHERE wpDusermeta.meta_key = 'wpDcapabilities'
- order by wpDusers.ID desc limit 0 , 1;
- INSERT INTO ST_users (wpid, email, wp_role) VALUES (id_i, email_i, role_i);
- END
- $$
- DELIMITER ;
- -- INSERT USERS
- INSERT INTO wpdusers (ID, user_email) VALUES (1, 'email@email.com');
- INSERT INTO wpDusermeta (umeta_id, user_id, meta_key, meta_value)
- VALUES(1, 1, 'wpDcapabilities', '_{_administrator_}_');
- INSERT INTO wpDusers (ID, user_email)
- VALUES(2, 'email2@email.com');
- INSERT INTO wpDusermeta (umeta_id, user_id, meta_key, meta_value)
- VALUES(2, 2, 'wpDcapabilities', '_{_student_}_');
- INSERT INTO wpDusers (ID, user_email)
- VALUES(3, 'email2@email.com');
- INSERT INTO wpDusermeta (umeta_id, user_id, meta_key, meta_value)
- VALUES(3, 3, 'wpDcapabilities', '_{_pupil_}_');
- -- Return Wordpress data
- SELECT wpDusers.ID, wpDusers.user_email, wpDusermeta.meta_value
- FROM wpDusers
- INNER JOIN wpDusermeta ON wpDusers.ID = wpDusermeta.user_id
- WHERE wpDusermeta.meta_key = 'wpDcapabilities'
- order by wpDusers.ID desc limit 1;
- -- Return ST data
- SELECT * FROM ST_users
Advertisement
Add Comment
Please, Sign In to add comment
Advertisement