Last active
February 1, 2016 16:30
-
-
Save prinsss/3a72cf916a6689c7a665 to your computer and use it in GitHub Desktop.
Convert data of WP-PostViews to fit custom page counter of Ghost
This file contains 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 | |
/** | |
* @Author: printempw | |
* @Date: 2016-02-01 20:37:43 | |
* @Last Modified by: prpr | |
* @Last Modified time: 2016-02-01 22:10:06 | |
* | |
* Configure your database connection info here. | |
* $wp_conn for WordPress installation database, | |
* $ghost_conn for which you place `post_views` table in. | |
*/ | |
$wp_conn = new mysqli('localhost', 'root', 'root', 'test'); | |
$ghost_conn = new mysqli('localhost', 'root', 'root', 'ghost'); | |
if ($wp_conn->connect_error) { | |
die($wp_conn->connect_error); | |
} | |
if ($ghost_conn->connect_error) { | |
die($ghost_conn->connect_error); | |
} | |
/** | |
* Taverse wp_postmeta | |
* Be careful if you have a BIG table | |
* Do not run this script twice | |
*/ | |
$sql = "SELECT * FROM `wp_postmeta` WHERE `meta_key`='views'"; | |
$result = $wp_conn->query($sql); | |
while ($row = $result->fetch_array()) { | |
$id = $row['post_id']; | |
$view = $row['meta_value']; | |
// Get slug from wp_posts table | |
$sql = "SELECT * FROM `wp_posts` WHERE `id`='$id'"; | |
$slug = $wp_conn->query($sql)->fetch_array()['post_name']; | |
if (updateRecord($slug, $view, $ghost_conn)) { | |
echo "Importing: { slug: ".$slug.", pv:".$view." }<br />"; | |
} else { | |
echo "Ignored: { slug: ".$slug.", pv:".$view." }<br />"; | |
} | |
} | |
function updateRecord($slug, $view, $conn) { | |
$result = $conn->query("SELECT * FROM post_views WHERE slug='$slug'"); | |
if ($result->num_rows != 0) { | |
// If slug exists in ghost post_views, update it | |
$update_sql = "UPDATE post_views SET pv=pv+$view WHERE slug='$slug'"; | |
$conn->query($update_sql); | |
return true; | |
} else { | |
$result = $conn->query("SELECT * FROM posts WHERE slug='$slug'"); | |
if ($result->num_rows != 0) { | |
// If imported slug doesn't exist in ghost page_views table, | |
// but in posts, then insert it. | |
$sql = "INSERT INTO post_views(slug, pv) VALUES ('$slug', 1)"; | |
$conn->query($sql); | |
// Recursion | |
updateRecord($slug, $view, $conn); | |
} | |
// If imported slug is already removed | |
return false; | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment