Last active
September 15, 2018 14:38
-
-
Save prinsss/a1018424b3bf03b0072f to your computer and use it in GitHub Desktop.
Ghost page view counter, support both MySQL and SQLite.
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-01-31 17:08:29 | |
* @Last Modified by: prpr | |
* @Last Modified time: 2016-02-02 16:31:55 | |
* | |
* Database type, `mysql` or `sqlite` | |
*/ | |
define('DB_TYPE', 'mysql'); | |
/* Configure this if you use mysql */ | |
/* Database name should be same with which you set for ghost */ | |
define('DB_NAME', 'ghost'); | |
define('DB_USER', 'root'); | |
define('DB_PASSWD', 'root'); | |
define('DB_HOST', 'localhost'); | |
/* For SQLite users, please configure database path */ | |
define('DB_DIR', 'ghost-dev.db'); | |
/* Allowed time cap between each addition request in seconds */ | |
define('TIME_CAP', 5); | |
header('Access-Control-Allow-Origin: *'); | |
header("Content-type: text/json"); | |
session_start(); | |
if (isset($_GET['action'])) { | |
// Simple SQL injection prevention | |
$action = stripslashes(trim($_GET['action'])); | |
$slug = isset($_GET['slug']) ? stripslashes(trim($_GET['slug'])) : ""; | |
// Instantiate a database object | |
$db = new database(DB_TYPE); | |
if ($action == "get") { | |
$count = getCount($slug); | |
echo $count ? toJson($count) : toJson(0, "No such slug."); | |
} else if ($action == "add") { | |
// Simple prevention for evil post | |
if (!isset($_SESSION['last_post'])) { | |
$_SESSION['last_post'] = time(); | |
} else { | |
$post_time_cap = time() - $_SESSION['last_post']; | |
if ($post_time_cap < TIME_CAP) { | |
die(toJson(0, 'Wow you so faaaaaaaaast! Wait for some secs, man.')); | |
} | |
} | |
$count = addCount($slug); | |
echo $count ? toJson($count) : toJson(0, "No such slug."); | |
$_SESSION['last_post'] = time(); | |
} else if ($action == "order") { | |
// Check input | |
$limit = (isset($_GET['limit']) && is_numeric($_GET['limit'])) ? $_GET['limit'] : 10; | |
echo getPopularSlug($limit); | |
} | |
// Dont forget to close the connection :) | |
$db->close(); | |
} else { | |
echo toJson(0, 'Invalid parameters.'); | |
} | |
function addCount($slug) { | |
global $db; | |
$result = $db->query("SELECT * FROM posts WHERE slug='$slug'"); | |
if (checkSlugExist($slug)) { | |
$db->query("UPDATE post_views SET pv=pv+1 WHERE slug='$slug'"); | |
return getCount($slug); | |
} else { | |
return false; | |
} | |
} | |
function getCount($slug) { | |
global $db; | |
if (checkSlugExist($slug)) { | |
// Use custom method to support both mysql and sqlite | |
$result = $db->query("SELECT * FROM post_views WHERE slug='$slug'"); | |
$count = $db->fetchArray($result)['pv']; | |
return $count; | |
} else { | |
return false; | |
} | |
} | |
function getPopularSlug($limit) { | |
global $db; | |
$order_sql = "SELECT * FROM `post_views` ORDER BY `pv` DESC LIMIT $limit"; | |
$title_sql = "SELECT * FROM `posts` WHERE `slug`="; | |
$popular_posts = []; | |
$result = $db->query($order_sql); | |
while ($row = $db->fetchArray($result)) { | |
$slug = $row['slug']; | |
$title_result = $db->query($title_sql."'$slug'"); | |
$title = $db->fetchArray($title_result)['title']; | |
$popular_posts[$slug] = [$title, $row['pv']]; | |
} | |
echo json_encode($popular_posts); | |
} | |
function checkSlugExist($slug) { | |
global $db; | |
$sql = "SELECT * FROM post_views WHERE slug='$slug'"; | |
if ($db->checkRecordExist($sql)) { | |
return true; | |
} else { | |
$sql = "SELECT * FROM posts WHERE slug='$slug'"; | |
/** | |
* If requested slug doesnt exist in `post_views` but exist in `posts`, | |
* then insert a record. | |
*/ | |
if ($db->checkRecordExist($sql)) { | |
return insertRecord($slug); | |
} else { | |
// Non-existent slug | |
return false; | |
} | |
} | |
} | |
function insertRecord($slug) { | |
global $db; | |
$sql = "INSERT INTO post_views(slug, pv) VALUES ('$slug', 1)"; | |
return $db->query($sql) ? true : false; | |
} | |
function toJson($count, $msg="") { | |
return $msg != "" ? | |
json_encode(array("count" => $count, "msg" => $msg)) : json_encode(array("count" => $count)); | |
} | |
/** | |
* Custom Database Class | |
* | |
* Will provide universal methods for both MySQL and SQLite | |
*/ | |
class database | |
{ | |
private $connection = null; | |
// 0 for sqlite, 1 for mysql | |
private $type = 1; | |
function __construct($db_type) { | |
if ($db_type == "mysql") { | |
$this->type = 1; | |
} else if ($db_type == "sqlite") { | |
$this->type = 0; | |
} | |
$this->connect(); | |
// Dont mind this UGLY line wrap :( | |
$sql = ($this->type == 1) ? "SELECT table_name FROM | |
`INFORMATION_SCHEMA`.`TABLES` WHERE table_name ='post_views' | |
AND TABLE_SCHEMA='ghost'" : "SELECT count(*) FROM sqlite_master | |
WHERE type='table' AND name='post_views'"; | |
// Create `post_views` table if not exists | |
if (!$this->checkRecordExist($sql)) $this->createTable(); | |
} | |
public function connect() { | |
if ($this->type == 1) { | |
$this->connection = new mysqli(DB_HOST, DB_USER, DB_PASSWD, DB_NAME); | |
if ($error = $this->connection->connect_error) { | |
die($error); | |
} | |
$this->connection->query("SET names UTF8"); | |
} else { | |
$this->connection = new SQLite3(DB_DIR); | |
if ($this->connection->lastErrorCode()) { | |
die($this->connection->lastErrorMsg()); | |
} | |
} | |
} | |
public function query($sql) { | |
return $this->connection->query($sql); | |
} | |
public function fetchArray($result) { | |
if ($this->type == 1) { | |
return $result->fetch_array(); | |
} else { | |
return $result->fetchArray(); | |
} | |
} | |
public function checkRecordExist($sql) { | |
$result = $this->query($sql); | |
if ($this->type == 1) { | |
return ($result->num_rows != 0) ? true : false; | |
} else { | |
return ($result->fetchArray()) ? true : false; | |
} | |
} | |
public function createTable() { | |
if ($this->type == 1) { | |
if ($this->query("CREATE TABLE IF NOT EXISTS `post_views` ( | |
`id` int(10) NOT NULL AUTO_INCREMENT, | |
`slug` varchar(150) NOT NULL, | |
`pv` int(10) NOT NULL, | |
PRIMARY KEY (`id`) | |
) ENGINE=InnoDB DEFAULT CHARSET=utf8;")) | |
{ | |
return true; | |
} | |
die($this->connection->connect_error); | |
} else { | |
if ($this->query("CREATE TABLE IF NOT EXISTS `post_views` ( | |
`id` INTEGER PRIMARY KEY NOT NULL, | |
`slug` CHAR(150) NOT NULL, | |
`pv` INT NOT NULL | |
);")) | |
{ | |
return true; | |
} | |
die($this->connection->lastErrorMsg()); | |
} | |
} | |
public function close() { | |
return $this->connection->close(); | |
} | |
} |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment