prefix)) {
$prefix = $wpdb->prefix;
} else {
$prefix = $table_prefix;
}
$comment_table = $prefix . "comments";
$ratings_cats_table = $prefix . "dgrs_cats";
$ratings_table = $prefix . "dgrs_ratings";
$options_table = $prefix . "dgrs_options";
//Check if plugin tables exist yet
if($wpdb->get_var("SHOW TABLES LIKE '$ratings_table'") != $ratings_table) {
//Not installed, add the necessary database tables and rows
dgrs_install();
} else {
//Is installed, look up current settings
$options = dgrs_get_options();
}
//Adds the necessary tables
function dgrs_install() {
global $ratings_cats_table, $ratings_table, $options_table;
//Table holds categories each post can be rated on
$sql = "CREATE TABLE `" . $ratings_cats_table . "` ("
. "`id` INT PRIMARY KEY NOT NULL AUTO_INCREMENT, "
. "`cat` VARCHAR(255), "
. "`display_order` INT, "
. "`scale_min` INT, "
. "`scale_max` INT)";
mysql_query($sql);
//Table holds the submitted rating values
$sql = "CREATE TABLE `" . $ratings_table . "` ("
. "`comment_id` INT, "
. "`rating_cat_id` INT, "
. "`rating_value` INT, "
. "PRIMARY KEY (`comment_id`, `rating_cat_id`))";
mysql_query($sql);
//Table holds plugin-wide settings, mostly output formatting
$sql = "CREATE TABLE `" . $options_table . "` ("
. "`option` VARCHAR(255), "
. "`value` TEXT, "
. "PRIMARY KEY (`option`))";
mysql_query($sql);
//Inserting default values
$sql = "INSERT INTO `" . $ratings_cats_table . "` VALUES ("
. "1, 'Overall Rating', 1, 1, 10)";
mysql_query($sql);
$sql = "INSERT INTO `" . $options_table . "` VALUES ("
. "'auto_embed_posts', '1')";
mysql_query($sql);
$sql = "INSERT INTO `" . $options_table . "` VALUES ("
. "'auto_embed_comments', '1')";
mysql_query($sql);
$sql = "INSERT INTO `" . $options_table . "` VALUES ("
. "'input_before_all', '')";
mysql_query($sql);
$sql = "INSERT INTO `" . $options_table . "` VALUES ("
. "'input_after_all', '
')";
mysql_query($sql);
$sql = "INSERT INTO `" . $options_table . "` VALUES ("
. "'display_before_all', '')";
mysql_query($sql);
$sql = "INSERT INTO `" . $options_table . "` VALUES ("
. "'display_after_all', '
')";
mysql_query($sql);
$sql = "INSERT INTO `" . $options_table . "` VALUES ("
. "'input_before_label', '| ')";
mysql_query($sql);
$sql = "INSERT INTO `" . $options_table . "` VALUES ("
. "'input_after_label', ' | ')";
mysql_query($sql);
$sql = "INSERT INTO `" . $options_table . "` VALUES ("
. "'input_before_select', '')";
mysql_query($sql);
$sql = "INSERT INTO `" . $options_table . "` VALUES ("
. "'input_after_select', ' |
')";
mysql_query($sql);
$sql = "INSERT INTO `" . $options_table . "` VALUES ("
. "'display_before_label', ': ')";
mysql_query($sql);
$sql = "INSERT INTO `" . $options_table . "` VALUES ("
. "'display_before_rating', '')";
mysql_query($sql);
$sql = "INSERT INTO `" . $options_table . "` VALUES ("
. "'display_after_rating', '')";
mysql_query($sql);
}
//Record ratings when a comment is posted
function dgrs_comment_posted($comment_id) {
global $ratings_table;
if (isset($_POST['dgrs_rating'])) {
foreach ($_POST['dgrs_rating'] as $key => $val) {
$insert_sql = "INSERT INTO `" . $ratings_table . "` VALUES ("
. $comment_id . ", "
. $key . ", "
. $val . ")";
mysql_query($insert_sql);
}
}
}
function dgrs_get_options() {
global $options_table;
$result = mysql_query("SELECT * FROM `" . $options_table . "`");
while ($row = mysql_fetch_assoc($result)) {
$options[$row['option']] = $row['value'];
}
return $options;
}
//Returns HTML for inputting ratings when commenting
function dgrs_get_rating_fields() {
global $ratings_cats_table, $options;
$sql = "SELECT `id`, `cat`, `scale_min`, `scale_max` FROM `"
. $ratings_cats_table . "` ORDER BY `display_order`";
$sql_result = mysql_query($sql);
$html = $options['input_before_all'];
while ($row = mysql_fetch_assoc($sql_result)) {
$html .= $options['input_before_label'];
$html .= $row['cat'];
$html .= $options['input_after_label'];
$html .= $options['input_before_select'];
$html .= '';
$html .= $options['input_after_select'];
}
$html .= $options['input_after_all'];
return $html;
}
//Returns formatted ratings to show with a comment
function dgrs_get_comment_ratings($comment_id) {
global $ratings_cats_table, $ratings_table, $options;
$sql = "SELECT `cat`, `rating_value` FROM `" . $ratings_cats_table . "` "
. "INNER JOIN `" . $ratings_table . "` "
. "ON (`" . $ratings_cats_table . "`.`id` = `" . $ratings_table . "`.`rating_cat_id`) "
. "WHERE `" . $ratings_table . "`.`comment_id` = " . $comment_id . " "
. "ORDER BY `" . $ratings_cats_table . "`.`display_order`";
$sql_result = mysql_query($sql) or die($sql);
$html = $options['display_before_all'];
while ($row = mysql_fetch_assoc($sql_result)) {
$html .= $options['display_before_label'];
$html .= $row['cat'];
$html .= $options['display_after_label'];
$html .= $options['display_before_rating'];
$html .= $row['rating_value'];
$html .= $options['display_after_rating'];
$html .= "\n";
}
$html .= $options['display_after_all'];
return $html;
}
//Returns number of overall ratings above 50% for a post
function dgrs_get_post_positive_ratings($post_id) {
global $ratings_table, $ratings_cats_table, $comment_table;
$sql = "SELECT COUNT(*) FROM `" . $ratings_table . "` "
. "INNER JOIN `" . $comment_table . "` "
. "ON (`" . $ratings_table . "`.`comment_id` = `" . $comment_table . "`.`comment_ID`) "
. "INNER JOIN `" . $ratings_cats_table . "` "
. "ON (`" . $ratings_table . "`.`rating_cat_id` = `" . $ratings_cats_table . "`.`id`) "
. "WHERE `" . $comment_table . "`.`comment_post_ID` = " . $post_id . " "
. "AND `" . $ratings_table . "`.`rating_cat_id` = 1 "
. "AND `" . $ratings_table . "`.`rating_value` > "
. "FLOOR((`" . $ratings_cats_table . "`.`scale_max` - "
. "`" . $ratings_cats_table . "`.`scale_min`) / 2)";
$sql_result = mysql_query($sql);
return mysql_result($sql_result, 0);
}
//Returns number of overall ratings below 50% for a post
function dgrs_get_post_negative_ratings($post_id) {
global $ratings_table, $ratings_cats_table, $comment_table;
$sql = "SELECT COUNT(*) FROM `" . $ratings_table . "` "
. "INNER JOIN `" . $comment_table . "` "
. "ON (`" . $ratings_table . "`.`comment_id` = `" . $comment_table . "`.`comment_ID`) "
. "INNER JOIN `" . $ratings_cats_table . "` "
. "ON (`" . $ratings_table . "`.`rating_cat_id` = `" . $ratings_cats_table . "`.`id`) "
. "WHERE `" . $comment_table . "`.`comment_post_ID` = " . $post_id . " "
. "AND `" . $ratings_table . "`.`rating_cat_id` = 1 "
. "AND `" . $ratings_table . "`.`rating_value` <= "
. "FLOOR((`" . $ratings_cats_table . "`.`scale_max` - "
. "`" . $ratings_cats_table . "`.`scale_min`) / 2)";
$sql_result = mysql_query($sql);
return mysql_result($sql_result, 0);
}
//Returns all ratings as an array
function dgrs_get_post_ratings($post_id) {
global $ratings_table, $ratings_cats_table, $comment_table;
$sql = "SELECT `" . $ratings_cats_table . "`.`cat`, "
. "AVG(`" . $ratings_table . "`.`rating_value`) AS `avg` "
. "FROM `" . $ratings_table . "` "
. "INNER JOIN `" . $comment_table . "` "
. "ON (`" . $ratings_table . "`.`comment_id` = `" . $comment_table . "`.`comment_ID`) "
. "INNER JOIN `" . $ratings_cats_table . "` "
. "ON (`" . $ratings_table . "`.`rating_cat_id` = `" . $ratings_cats_table . "`.`id`) "
. "WHERE `" . $comment_table . "`.`comment_post_ID` = " . $post_id . " "
. "GROUP BY `" . $ratings_cats_table . "`.`id` "
. "ORDER BY `" . $ratings_cats_table . "`.`display_order`";
$sql_result = mysql_query($sql);
while ($row = mysql_fetch_assoc($sql_result)) {
$ratings[$row['cat']] = $row['avg'];
}
return $ratings;
}
//Returns all ratings as HTML
function dgrs_display_post_ratings($post_id) {
global $options;
$ratings = dgrs_get_post_ratings($post_id);
$html = $options['display_before_all'];
if (isset($ratings) && count($ratings) > 0) {
foreach ($ratings as $cat => $rating) {
$html .= $options['display_before_label'];
$html .= $cat;
$html .= $options['display_after_label'];
$html .= $options['display_before_rating'];
$html .= number_format($rating, 1, '.', '');
$html .= $options['display_after_rating'];
}
}
$html .= $options['display_after_all'];
return $html;
}
//Adds the Review Site subpanel to the admin options panel
function dgrs_add_options_subpanel() {
if (function_exists('add_options_page')) {
add_options_page('Review Site', 'Review Site', 10, basename(__FILE__), 'dgrs_options_panel');
}
}
//Displays the Review Site subpanel
function dgrs_options_panel() {
global $options_table, $ratings_cats_table, $options;
if (isset($_POST['info_update'])) {
//Record the categories
foreach ($_POST['id'] as $id) {
if (isset($_POST['delete'][$id])) {
$sql = "DELETE FROM `" . $ratings_cats_table . "` WHERE id = " . $id;
} else {
$sql = "UPDATE `" . $ratings_cats_table . "` "
. "SET cat = '" . mysql_real_escape_string($_POST['cat'][$id]) . "', "
. "scale_min = " . (int)$_POST['min'][$id] . ", "
. "scale_max = " . (int)$_POST['max'][$id] . ", "
. "display_order = " . (int)$_POST['ord'][$id] . " "
. "WHERE id = " . $id;
}
mysql_query($sql);
}
if (!empty($_POST['new_cat'])) {
$sql = "INSERT INTO `" . $ratings_cats_table . "` VALUES ("
. "NULL, '" . mysql_real_escape_string($_POST['new_cat']) . "', "
. (int)$_POST['new_ord'] . ", "
. (int)$_POST['new_min'] . ", "
. (int)$_POST['new_max'] . ")";
mysql_query($sql);
}
//Record the display settings
foreach (array_keys($options) as $key) {
$sql = "UPDATE `" . $options_table . "` "
. "SET `value` = '" . mysql_real_escape_string($_POST[$key]) . "' "
. "WHERE `option` = '" . $key . "'";
mysql_query($sql);
}
echo 'Your settings have been saved.