Last active
November 30, 2016 05:33
-
-
Save jonathanstegall/437dc7af0d501587fd2a2b42435cb805 to your computer and use it in GitHub Desktop.
This version is an attempt to migrate orders from Easy Digital Downloads to WooCommerce
This file contains hidden or bidirectional Unicode text that may be interpreted or compiled differently than what appears below. To review, open the file in an editor that reveals hidden Unicode characters.
Learn more about bidirectional Unicode characters
| <?php | |
| // Migrates Easy Digital Download orders into WooCommerce | |
| // I use this on my local machine - loading both db's up there side by side | |
| // could easily adjust the connect strings to connect elsewhere if needed. | |
| // will change order ids | |
| // My use case for this is when I've got a staging/test version of a site with new posts/products/pages etc, that needs | |
| // to go live without the loss of any orders placed on the site site since we copied it to the staging site. | |
| // names of source and target dbs | |
| define('NEW_DB', 'target'); | |
| define('OLD_DB', 'source'); | |
| define('NEW_PREFIX', 'wp_'); | |
| define('OLD_PREFIX', 'wp_'); | |
| // if you wanted to connect somewhere else, do it here | |
| $new_conn = mysqli_connect('127.0.0.1', 'root', ''); | |
| $old_conn = mysqli_connect('127.0.0.1', 'root', ''); | |
| mysqli_select_db($new_conn, NEW_DB); | |
| mysqli_select_db($old_conn, OLD_DB); | |
| // template for inserting the shop_order posts | |
| $post_sql_template = "INSERT INTO `" . NEW_PREFIX . "_posts` ( | |
| `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 ( | |
| %s, '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', '%s', %s, '%s', %s, '%s', '%s', %s);"; | |
| // template for inserting the order_items | |
| $item_sql_template = "INSERT INTO `" . NEW_PREFIX . "_woocommerce_order_items` (`order_item_name`, `order_item_type`, `order_id`) VALUES ('%s', '%s', '%s')"; | |
| // If you want to remove all orders in the target db, and replace with those from source, uncomment this block. | |
| // I don't usually do this as leaving it commented, orders common to both systems will be skipped and so | |
| // retain their original ids | |
| // $sql = "DELETE FROM " . NEW_PREFIX . "_woocommerce_order_itemmeta"; | |
| // mysqli_query($new_conn, $sql); | |
| // $sql = "DELETE FROM " . NEW_PREFIX . "_woocommerce_order_items"; | |
| // mysqli_query($new_conn, $sql); | |
| // $sql = "DELETE FROM " . NEW_PREFIX . "_posts WHERE post_type = 'shop_order'"; | |
| // mysqli_query($new_conn, $sql); | |
| // $sql = "SELECT * FROM " . OLD_PREFIX . "_posts WHERE post_type = 'shop_order' "; | |
| $sql = "SELECT | |
| p.id `ID`, | |
| 1 `post_author`, | |
| p.post_date `post_date`, | |
| p.post_date_gmt `post_date_gmt`, | |
| '' `post_content`, | |
| CONCAT('Order – ', DATE_FORMAT(p.post_date, '%M %e, %Y @ %h:%i %p' ) ) `post_title`, | |
| 0 `post_category`, | |
| '' `post_excerpt`, | |
| CONCAT('wc-migrated-', p.post_status) `post_status`, | |
| 'closed' `comment_status`, | |
| 'closed' `ping_status`, | |
| CONCAT('order_', SUBSTRING(MD5(RAND()) FROM 1 FOR 13)) `post_password`, | |
| LOWER( CONCAT( 'order-', DATE_FORMAT(p.post_date, '%b-%d-%Y-%h%i-%p' ) ) ) `post_name`, | |
| '' `to_ping`, | |
| '' `pinged`, | |
| p.post_modified `post_modified`, | |
| p.post_modified_gmt `post_modified_gmt`, | |
| '' `post_content_filtered`, | |
| 0 `post_parent`, | |
| CONCAT('http://homebrewedchristianity.com/?post_type=shop_order&p=',p.id) `gid`, | |
| 0 `menu_order`, | |
| 'shop_order' `post_type`, | |
| '' `post_mime_type`, | |
| 0 `comment_count` | |
| FROM " . OLD_PREFIX . "_posts p | |
| WHERE p.post_type = 'edd_payment'"; | |
| echo $sql; | |
| $order_res = mysqli_query($old_conn, $sql); | |
| while ($row = mysqli_fetch_assoc($order_res)) { | |
| $old_id = $row['ID']; | |
| // check to see if a shop_order post with this id exists - if so, skip | |
| // (see note on deleting above - this won't happen if that block is uncommented) | |
| $sql = "SELECT * FROM " . NEW_PREFIX . "_posts WHERE post_type = 'shop_order' AND ID = " . $old_id; | |
| $count_res = mysqli_query($new_conn, $sql); | |
| if (mysqli_num_rows($count_res)) { | |
| echo "Skipping " . $old_id . "..." . PHP_EOL; | |
| continue; | |
| } | |
| echo "Processing " . $old_id . "..." . PHP_EOL; | |
| // add the post | |
| $post_sql = sprintf($post_sql_template, | |
| $row['post_author'], | |
| $row['post_date'], | |
| $row['post_date_gmt'], | |
| $row['post_content'], | |
| $row['post_title'], | |
| $row['post_excerpt'], | |
| $row['post_status'], | |
| $row['comment_status'], | |
| $row['ping_status'], | |
| $row['post_password'], | |
| $row['post_name'], | |
| $row['to_ping'], | |
| $row['pinged'], | |
| $row['post_modified'], | |
| $row['post_modified_gmt'], | |
| $row['post_content_filtered'], | |
| $row['post_parent'], | |
| $row['guid'], | |
| $row['menu_order'], | |
| $row['post_type'], | |
| $row['post_mime_type'], | |
| $row['comment_count'] | |
| ); | |
| // insert new post for order | |
| //$insert_post_res = mysqli_query($new_conn, $post_sql); | |
| //$new_id = mysqli_insert_id($new_conn); | |
| // and the postmeta | |
| $get = "SELECT meta_key, meta_value FROM " . OLD_DB . "." . OLD_PREFIX . "_postmeta old WHERE old.post_id = " . $old_id; | |
| $get_old_data = mysqli_query($old_conn, $get); | |
| while ($get_old_data_row = mysqli_fetch_assoc($get_old_data)) { | |
| $sql = "INSERT INTO " . NEW_DB . "." . OLD_PREFIX . "_postmeta (post_id, meta_key, meta_value) VALUES ('$new_id', '" . $get_old_data_row['meta_key'] . "', '" . $get_old_data_row['meta_value'] . "' )"; | |
| //mysqli_query($new_conn, $sql); | |
| } | |
| // and term relationships (stores order status) | |
| $get = "SELECT " . $new_id . ", term_taxonomy_id, term_order FROM " . OLD_DB . "." . OLD_PREFIX . "_term_relationships old WHERE old.object_id = " . $old_id; | |
| $get_old_data = mysqli_query($old_conn, $get); | |
| while ($get_old_data_row = mysqli_fetch_assoc($get_old_data)) { | |
| $sql = "INSERT INTO " . NEW_DB . "." . OLD_PREFIX . "_term_relationships (object_id, term_taxonomy_id, term_order) VALUES ('$new_id', '" . $get_old_data_row['term_taxonomy_id'] . "', '" . $get_old_data_row['term_order'] . "' )"; | |
| //mysqli_query($new_conn, $sql); | |
| } | |
| // and the comments - which store order nots | |
| $get = "SELECT $new_id, `comment_author`, `comment_author_email`, `comment_author_url`, `comment_author_IP`, `comment_date`, `comment_date_gmt`, `comment_content`, `comment_karma`, `comment_approved`, `comment_agent`, `comment_type`, `comment_parent`, `user_id` FROM " . OLD_DB . "." . OLD_PREFIX . "_comments old WHERE old.comment_post_ID = " . $old_id; | |
| $get_old_data = mysqli_query($old_conn, $get); | |
| while ($get_old_data_row = mysqli_fetch_assoc($get_old_data)) { | |
| $sql = "INSERT INTO " . NEW_DB . "." . OLD_PREFIX . "_comments (`comment_post_ID`, `comment_author`, `comment_author_email`, `comment_author_url`, `comment_author_IP`, `comment_date`, `comment_date_gmt`, `comment_content`, `comment_karma`, `comment_approved`, `comment_agent`, `comment_type`, `comment_parent`, `user_id`) VALUES ('$new_id', '" . $get_old_data_row['comment_author'] . "', '" . $get_old_data_row['comment_author_email'] . "', '" . $get_old_data_row['comment_author_url'] . "', '" . $get_old_data_row['comment_author_IP'] . "', '" . $get_old_data_row['comment_date'] . "', '" . $get_old_data_row['comment_date_gmt'] . "', '" . $get_old_data_row['comment_content'] . "', '" . $get_old_data_row['comment_karma'] . "', '" . $get_old_data_row['comment_approved'] . "', '" . $get_old_data_row['comment_agent'] . "', '" . $get_old_data_row['comment_type'] . "', '" . $get_old_data_row['comment_parent'] . "', '" . $get_old_data_row['user_id'] . "' )"; | |
| //mysqli_query($new_conn, $sql); | |
| } | |
| // and then order items and order item meta | |
| $sql = sprintf("SELECT * FROM " . OLD_PREFIX . "_woocommerce_order_items WHERE order_id = %s", $old_id); | |
| $item_res = mysqli_query($old_conn, $sql); | |
| while ($item_row = mysqli_fetch_assoc($item_res)) { | |
| $old_item_id = $item_row['order_item_id']; | |
| $item_sql = sprintf($item_sql_template, | |
| $item_row['order_item_name'], | |
| $item_row['order_item_type'], | |
| $new_id | |
| ); | |
| //mysqli_query($new_conn, $item_sql); | |
| //$new_item_id = mysqli_insert_id($new_conn); | |
| $get = "SELECT meta_key, meta_value FROM " . OLD_DB . "." . OLD_PREFIX . "_woocommerce_order_itemmeta old WHERE old.order_item_id = " . $old_item_id; | |
| $get_old_data = mysqli_query($old_conn, $get); | |
| while ($get_old_data_row = mysqli_fetch_assoc($get_old_data)) { | |
| $sql = "INSERT INTO " . NEW_DB . "." . OLD_PREFIX . "_woocommerce_order_itemmeta (order_item_id, meta_key, meta_value) VALUES ('$new_id', '" . $get_old_data_row['meta_key'] . "', '" . $get_old_data_row['meta_value'] . "')"; | |
| //mysqli_query($new_conn, $sql); | |
| } | |
| } | |
| } |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment