Last active
August 29, 2015 14:26
-
-
Save daviseford/3b363ffdcd5ecae68c89 to your computer and use it in GitHub Desktop.
multiple wordpress installations, specific post category by date
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 | |
| /* By Davis Ford, 2015 | |
| * http://daviseford.com | |
| * Please feel free to re-use this code with credit. | |
| */ | |
| $servername = "localhost"; | |
| $username_Sample = "your_mysql_username"; | |
| $password_Sample = "your_mysql_password"; | |
| $dbname_Sample = "mysql_database_name"; | |
| $cat_id_Sample = 64; //numeric cat_id of category you want to track | |
| $date_offset = 1; /* not used, just here as an example | |
| * if you set this to 4, | |
| * the MySQL query will grab posts | |
| * from within the last 3 days. | |
| * Set to 1 to grab today's posts. | |
| */ | |
| function typicalSQL($cat_id, $post_offset) { | |
| return "SELECT DISTINCT ID, post_title, post_name, guid, post_date, post_content | |
| FROM wp_posts AS p | |
| INNER JOIN wp_term_relationships AS tr ON ( | |
| p.ID = tr.object_id | |
| ) | |
| INNER JOIN wp_term_taxonomy AS tt ON ( | |
| tr.term_taxonomy_id = tt.term_taxonomy_id | |
| AND taxonomy = 'category' AND tt.term_id | |
| IN ( $cat_id ) | |
| ) | |
| AND DATEDIFF(NOW(), `post_date`) < $post_offset | |
| ORDER BY id DESC"; | |
| } | |
| function sqlConnectionManager($servername, $username, $password, $dbname, $cat_id, $post_offset) { | |
| // Create a new MySQL connection | |
| $conn = new mysqli($servername, $username, $password, $dbname); | |
| // Check connection | |
| if ($conn->connect_error) { | |
| die("Connection failed: " . $conn->connect_error); | |
| } | |
| // This makes use of a typical category-selection statement for MySQL. | |
| // We pass in two variables to control the request. | |
| $result = $conn->query(typicalSQL($cat_id, $post_offset)); | |
| if($result->num_rows === null || $result->num_rows <= 0) { | |
| $result->num_rows = 0; | |
| } | |
| return $result->num_rows; | |
| } | |
| $daily_Sample = sqlConnectionManager($servername, $username_Sample, $password_Sample, $dbname_Sample, $cat_id_Sample, 1); | |
| $weekly_Sample = sqlConnectionManager($servername, $username_Sample, $password_Sample, $dbname_Sample, $cat_id_Sample, 8); | |
| $total_Sample = sqlConnectionManager($servername, $username_Sample, $password_Sample, $dbname_Sample, $cat_id_Sample, 999); | |
| ?> | |
| <!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Transitional//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-transitional.dtd"> | |
| <html xmlns="http://www.w3.org/1999/xhtml"> | |
| <head> | |
| <meta http-equiv="Content-Type" content="text/html; charset=utf-8" /> | |
| <title>Wordpress Category Reporting Dashboard</title> | |
| <!-- Latest compiled and minified CSS --> | |
| <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap.min.css"> | |
| <!-- Optional theme --> | |
| <link rel="stylesheet" href="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/css/bootstrap-theme.min.css"> | |
| <!-- Latest compiled and minified JavaScript --> | |
| <script src="https://maxcdn.bootstrapcdn.com/bootstrap/3.3.5/js/bootstrap.min.js"></script> | |
| </head> | |
| <body> | |
| <div class="container"> | |
| <div class="page-header"> | |
| <h1>Wordpress Category Reporting Dashboard</h1> | |
| <p class="lead">Developed by Davis Ford, this script pulls in category usage stats from multiple Wordpress installations.</p> | |
| </div> | |
| <div class="alert alert-danger" role="alert"> <strong>Don't worry</strong> Pretty much everything on here is work-in-progress </div> | |
| <div class="row"> | |
| <div class="col-md-4"> <!-- Start of 3column area --> | |
| <div class="row"> <!-- Start of row within a column--> | |
| <div class="col-md-2"></div> | |
| <!-- spacer colum --> | |
| <div class="col-md-8"> <!-- Start of new column within that row --> | |
| <center> | |
| <h3>Total</h3> | |
| </center> | |
| <ul class="list-group"> | |
| <li class="list-group-item"> <span class="badge"><?php echo $daily_Sample; ?></span> Today: </li> | |
| <li class="list-group-item"> <span class="badge"><?php echo $weekly_Sample; ?></span> This Week: </li> | |
| <li class="list-group-item"> <span class="badge"><?php echo $total_Sample; ?> </span> All Time: </li> | |
| </ul> | |
| <center> | |
| <em style="font-size:8pt;">combined data</em> | |
| </center> | |
| </div> | |
| <div class="col-md-2"></div> | |
| <!-- spacer colum --> | |
| </div> | |
| <!-- end of row --> | |
| </div> | |
| <!-- end of col-md-4 --> | |
| <div class="col-md-4"> <!-- Start of 3column area --> | |
| <div class="row"> <!-- Start of row within a column--> | |
| <div class="col-md-2"></div> | |
| <!-- spacer colum --> | |
| <div class="col-md-8"> <!-- Start of new column within that row --> | |
| <center> | |
| <h3>SampleSite</h3> | |
| </center> | |
| <ul class="list-group"> | |
| <li class="list-group-item"> <span class="badge"><?php echo $daily_Sample; ?></span> Today: </li> | |
| <li class="list-group-item"> <span class="badge"><?php echo $weekly_Sample; ?></span> This Week: </li> | |
| <li class="list-group-item"> <span class="badge"><?php echo $total_Sample; ?> </span> All Time: </li> | |
| </ul> | |
| <center> | |
| <em style="font-size:8pt;"><a href="http://daviseford.com" target="_blank">daviseford.com</a></em> | |
| </center> | |
| </div> | |
| <div class="col-md-2"></div> | |
| <!-- spacer colum --> | |
| </div> | |
| <!-- end of row --> | |
| </div> | |
| <!-- end of col-md-4 --> | |
| <div class="col-md-4"> | |
| <div class="row"> | |
| <div class="col-md-2"></div> | |
| <div class="col-md-8"></div> | |
| <div class="col-md-2"></div> | |
| </div> | |
| </div> | |
| </div> | |
| </div> | |
| <!-- end of container --> | |
| </body> | |
| </html> |
Sign up for free
to join this conversation on GitHub.
Already have an account?
Sign in to comment