Skip to content

Instantly share code, notes, and snippets.

@prinsss
Last active February 1, 2016 16:30
Show Gist options
  • Save prinsss/3a72cf916a6689c7a665 to your computer and use it in GitHub Desktop.
Save prinsss/3a72cf916a6689c7a665 to your computer and use it in GitHub Desktop.
Convert data of WP-PostViews to fit custom page counter of Ghost
<?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