scriptygoddess

Recently I added a Slashdot like moderation system to my home made weblogging software. The motivation was to add a simple way of offering your opinion on an entry without requiring a comment when you don't have one. This grabbed the attention of Etan over at http://www.toomuchsexy.org/; who desired an implementation for Movable Type. After some work last night and this morning I believe I have perfected the Movable Type implementation.

What you need:

A Movable Type weblog, obviously.
Access to a MySQL management system, such as phpMyAdmin.
A web server running PHP.

These instructions are based on a MT installation using MySQL and with a .php file extension. If you use a .html extension adding the following line to a .htaccess file in your web root folder will make the .html files be parsed by the PHP parser.

AddType application/x-httpd-php .html

See http://www.akamarketing.com/htaccess.html; and http://www.buildwebsite4u.com/advanced/htaccess-file.shtml; for more information on the .htaccess file.

Step 1: Creating the ratings table in MySQL

In phpMyAdmin select the MySQL database that contains your MT tables.
Choose SQL from the menu at the top and paste the following into the text field and submit it.

CREATE TABLE `ratings` (
`id` bigint(20) NOT NULL auto_increment,
`uid` bigint(20) NOT NULL default '0',
`rate` varchar(4) NOT NULL default '0',
`reason` varchar(50) NOT NULL default ",
PRIMARY KEY (`id`)
) TYPE=MyISAM AUTO_INCREMENT=1

That is it for adding things to the MySQL database. Now on to step 2.

Step 2: The PHP Add Script

This step involves creating a php script file that will handle adding user mods to the database created in step 1.

Open a text editor and copy and paste the following code into a new file.

<?php
$usr = "SQLUSERNAME";
$pwd = "SQLPASSWORD";
$db = "SQLDBNAME";
$host = "localhost";
$connection_id=mysql_connect($host,$usr,$pwd)
mysql_select_db($db);
$original_post_id=$_POST["uid"];
$rating=$_POST["rating"];
$reason=htmlentities(strip_tags($_POST["reason"]));
$SQL="INSERT INTO `ratings` VALUES (", '$original_post_id', '$rating', '$reason');";
if (!mysql_query($SQL, $connection_id)) { echo(mysql_error()); }
mysql_close($connection_id);
header("Location: ".$_SERVER['HTTP_REFERER']);
?>

Replace SQLUSERNAME with the username used to access the MySQL database.

Replace SQLPASSWORD with the password used to access the MySQL database.

Replace SQLDBNAME with the name of MySQL database that houses the table created in Step 1, should be the same one as your MT database.

Save this file as addrating.php and save it in the same directory as your MovableType weblog. IE your weblog url is http://server.com/mt/index.php save it in http://server.com/mt/.

No special permissions are needed for this file. This step is done, now to Step 3.

Step 3: Creating the Rating Form

This step involves creating a Movable Type template module that contains the form code used to add a moderation to the database.

From the MT weblog administration panel choose "Templates" from the left side menu. scroll down to "Template Modules" and click on "Create a new template module" to create a new module.

Name the module "ratingform" and paste the following code into the "Module Body" field.

<form method="post" action="<$MTBlogURL$>addrating.php">
<p><select id="rating" size="1" name="rating">
<option label="+1" value="+1">+1</option>
<option label="0" value="0" selected="selected">0</option>
<option label="-1" value="-1">-1</option>
</select>
Reason: <input type="text" id="reason" name="reason" size="10" maxlength="50" />
<input type="hidden" name="uid" value="<$MTEntryID$>" /><input type="hidden" name="func" value="Rating" />
<input type="submit" name="Submit" value="Rate Me" title="Send Rating"/></p>
</form>

Save the module and go back to the templates page, there is no need to rebuild your site yet.

Step 4: Creating the "Calculate Rating" Module

This step involves creating a template module that caluculates the total +/- rating for the entry.
Create a new template module and name it "calcrating" and paste the following code into the Module Body.

<?php
$usr = "SQLUSERNAME";
$pwd = "SQLPASSWORD";
$db = "SQLDBNAME";
$host = "localhost";
$connection_id=mysql_connect($host,$usr,$pwd);
mysql_select_db($db);
$RateSQL="SELECT `id` FROM `ratings` WHERE `uid` = <$MTEntryID$>";
$num_of_rates=mysql_num_rows(mysql_query($RateSQL, $connection_id));
if ($num_of_rates != 0 )
{
$Plus1RateSQL="SELECT `id` FROM `ratings` WHERE `uid` = <$MTEntryID$> AND `rate` = '+1'";
$num_of_plus1rates=mysql_num_rows(mysql_query($Plus1RateSQL, $connection_id));
$Min1RateSQL="SELECT `id` FROM `ratings` WHERE `uid` = <$MTEntryID$> AND `rate` = '-1'";
$num_of_min1rates=mysql_num_rows(mysql_query($Min1RateSQL, $connection_id));
$cur_rate=($num_of_plus1rates – $num_of_min1rates);
if ($num_of_plus1rates > $num_of_min1rates) {$cur_rate="+".$cur_rate;}
}
elseif ($num_of_rates==0) { $cur_rate=0;}
echo $cur_rate;
?>

Replace SQLUSERNAME with the username used to access the MySQL database.

Replace SQLPASSWORD with the password used to access the MySQL database.

Replace SQLDBNAME with the name of MySQL database that houses the table created in Step 1, should be the same one as your MT database.

Save the module and go back to the templates page, there is no need to rebuild your site yet.

Step 5: Creating the "Rating List" Module

This step involves creating a template module that builds a list of the ratings and the reason given for the rating, in other words the moderation.
Create a new template module and name it "ratinglist" and paste the following code into the Module Body.

<?php
$usr = "SQLUSERNAME";
$pwd = "SQLPASSWORD";
$db = "SQLDBNAME";
$host = "localhost";
$connection_id=mysql_connect($host,$usr,$pwd);
mysql_select_db($db);
$RateSQL="SELECT * FROM `ratings` WHERE `uid` = <$MTEntryID$> ORDER BY `id` DESC";
$raw_rate_data=mysql_query($RateSQL, $connection_id);
while ($row=mysql_fetch_array($raw_rate_data))
{
$rate_rate=$row['rate'];
$rate_reason=$row['reason'];
$rate_data.="<li>$rate_rate: $rate_reason</li>\n";
}
$rate_data="<ul>\n".$rate_data."</ul>\n";
echo $rate_data;
?>

Replace SQLUSERNAME with the username used to access the MySQL database.

Replace SQLPASSWORD with the password used to access the MySQL database.

Replace SQLDBNAME with the name of MySQL database that houses the table created in Step 1, should be the same one as your MT database.

Save the module and go back to the templates page, there is no need to rebuild your site yet.

Step 6: Adding It To Your Templates

Now that we've gone through and created all these modules it's time to put them to use. Let's start with an example usage of the "calcrating" module. A default MT weblog has a byline that displays Author, Time, Comments Count and Trackback Coount. This is a perfect place to add a rating, as would be next to the title.

To get this paste this one line <$MTInclude module="calcrating"$> into the Main Index template after the </MTEntryIfAllowPings> code. Save the template and rebuild your site. Now in the byline the total rating (+3, -1, etc) will be displayed right along with the other info.

Use <$MTInclude module="calcrating"$> anytime you want to show the totla rating for an entry.

To display the ratings form just add <$MTInclude module="ratingform"$> to the individual archive page in a place that makes sense, above or below the comments for example.

To display the list of ratings and reasons add this line <$MTInclude module="ratinglist"$> to where it makes sense, a side bar, or right above or below the ratings form.

Notes:
If you already use PHP with your MT weblog to interact with the MySQL backend and have a connection file you can use that and adjust the variables used above.

For Step 5 you can easily edit the formatting of the output if you don't want it as a list. Delete this line $rate_data="<ul>\n".$rate_data."</ul>\n"; and change <li> and </li> to the tags you want to use.