Feature Suggest App w/ PHP, MySQL & jQuery
Listening to what your visitors have to say, is always beneficial when planning new features or changes in your website. For a long time we’ve been limited to just setting up a contact form and hoping that quality feedback will follow, which unfortunately is not always the case.
Today we are taking things up a notch – we are applying the same social principles that have brought success to sharing sites such as Digg and delicious, and encourage visitors to suggest and vote on features that they want implemented on your website.
The XHTML
Starting with the new HTML5 doctype, we define the opening and closing head and title tags, and include the main stylesheet of the app – styles.css, in the document.
suggestions.php
<!DOCTYPE html>
<html>
<head>
<meta http-equiv="Content-Type" content="text/html; charset=utf-8" />
<title>Feature Suggest w/ PHP, jQuery & MySQL | Tutorialzine Demo</title>
<link rel="stylesheet" type="text/css" href="styles.css" />
</head>
<body>
<div id="page">
<div id="heading" class="rounded">
<h1>Feature Suggest<i>for Tutorialzine.com</i></h1>
</div>
<!-- The generated suggestion list comes here -->
<form id="suggest" action="" method="post">
<p>
<input type="text" id="suggestionText" class="rounded" />
<input type="submit" value="Submit" id="submitSuggestion" />
</p>
</form>
</div>
<script src="http://ajax.googleapis.com/ajax/libs/jquery/1.4.2/jquery.min.js"></script>
<script src="script.js"></script>
</body>
</html>
After this comes the body tag and the #page div, which is the main container element. It holds the heading, the unordered list with all the suggestions (which is generated by PHP, as you will see in a moment), and the submit form.
Lastly we include the jQuery library from Google’s AJAX Library CDN, and our own script.js file, which is discussed in detail in the last section of this tutorial.
Feature Suggest App w/ jQuery, PHP, MySQL
The Table Schema
The app uses two MySQL tables to store data. Suggestions and Suggestions_votes. The first table contains the text of the suggestion and data such as rating and the number of votes the item has received. The second table keeps record of the IPs of the voters and prevents more than one vote to be cast in a single day per IP.
Suggestion Table Schema
To speed up the selection queries, an index is defined on the rating field. This helps when showing the suggestions ordered by popularity.
The suggestion votes table has a primary key consisting of three fields – the suggestion_id, the IP of the voter, and the date of the vote. And because primary keys do not allow for duplicate rows, we can be sure that users can vote only once per day by just checking the value of the affected_rows variable after the insert.
Suggestions_Votes Schema
The PHP
Before delving into the generation of the suggestion items and the AJAX interactions, first we have to take a look at the suggestion PHP class. It uses two PHP magic methods (apart from the constructor) to provide rich functionality to our code. When generating the front page, PHP runs a MySQL select query against the database, and creates an object of this class for every table row. The columns of the row are added as properties to the object.
suggestion.class.php
class Suggestion
{
private $data = array();
public function __construct($arr = array())
{
if(!empty($arr)){
// The $arr array is passed only when we manually
// create an object of this class in ajax.php
$this->data = $arr;
}
}
public function __get($property){
// This is a magic method that is called if we
// access a property that does not exist.
if(array_key_exists($property,$this->data)){
return $this->data[$property];
}
return NULL;
}
public function __toString()
{
// This is a magic method which is called when
// converting the object to string:
return '
<li id="s'.$this->id.'">
<div class="vote '.($this->have_voted ? 'inactive' : 'active').'">
<span class="up"></span>
<span class="down"></span>
</div>
<div class="text">'.$this->suggestion.'</div>
<div class="rating">'.(int)$this->rating.'</div>
</li>';
}
}
The __toString() method is used to create a string representation of the object. With its help we can build the HTML markup, complete with the suggestion title and number of votes.
The __get() method is used to route the access to undefined properties of the class to the $data array. This means that if we access $obj->suggestion, and this property is undefined, it is going to be fetched from the $data array, and returned to us as if it existed. This way we can just pass an array to the constructor, instead of setting up all the properties. We are using this when creating an object in ajax.php.
Now lets proceed with the generation of the unordered list on the front page.
suggestions.php
require "connect.php";
require "suggestion.class.php";
// Converting the IP to a number. This is a more effective way
// to store it in the database:
$ip = sprintf('%u',ip2long($_SERVER['REMOTE_ADDR']));
// The following query uses a left join to select
// all the suggestions and in the same time determine
// whether the user has voted on them.
$result = $mysqli->query("
SELECT s.*, if (v.ip IS NULL,0,1) AS have_voted
FROM suggestions AS s
LEFT JOIN suggestions_votes AS v
ON(
s.id = v.suggestion_id
AND v.day = CURRENT_DATE
AND v.ip = $ip
)
ORDER BY s.rating DESC, s.id DESC
");
$str = '';
if(!$mysqli->error)
{
// Generating the UL
$str = '<ul class="suggestions">';
// Using MySQLi's fetch_object method to create a new
// object and populate it with the columns of the result query:
while($suggestion = $result->fetch_object('Suggestion')){
$str.= $suggestion; // Uses the __toString() magic method.
}
$str .='</ul>';
}
After running the query, we use the fetch_object() method of the $result object. This method creates an object of the given class for every row in the result, and assigns the columns of that row to the object as public properties.
PHP also manages the AJAX requests sent by jQuery. This is done in ajax.php. To distinguish one AJAX action from another, the script takes a $_GET['action'] parameter, which can have one of two values – ‘vote‘ or ‘submit‘.
ajax.php
require "connect.php";
require "suggestion.class.php";
// If the request did not come from AJAX, exit:
if($_SERVER['HTTP_X_REQUESTED_WITH'] !='XMLHttpRequest'){
exit;
}
// Converting the IP to a number. This is a more effective way
// to store it in the database:
$ip = sprintf('%u',ip2long($_SERVER['REMOTE_ADDR']));
if($_GET['action'] == 'vote'){
$v = (int)$_GET['vote'];
$id = (int)$_GET['id'];
if($v != -1 && $v != 1){
exit;
}
// Checking to see whether such a suggest item id exists:
if(!$mysqli->query("SELECT 1 FROM suggestions WHERE id = $id")->num_rows){
exit;
}
// The id, ip and day fields are set as a primary key.
// The query will fail if we try to insert a duplicate key,
// which means that a visitor can vote only once per day.
$mysqli->query("
INSERT INTO suggestions_votes (suggestion_id,ip,day,vote)
VALUES (
$id,
$ip,
CURRENT_DATE,
$v
)
");
if($mysqli->affected_rows == 1)
{
$mysqli->query("
UPDATE suggestions SET
".($v == 1 ? 'votes_up = votes_up + 1' : 'votes_down = votes_down + 1').",
rating = rating + $v
WHERE id = $id
");
}
}
else if($_GET['action'] == 'submit'){
// Stripping the content
$_GET['content'] = htmlspecialchars(strip_tags($_GET['content']));
if(mb_strlen($_GET['content'],'utf-8')<3){
exit;
}
$mysqli->query("INSERT INTO suggestions SET suggestion = '".$mysqli->real_escape_string($_GET['content'])."'");
// Outputting the HTML of the newly created suggestion in a JSON format.
// We are using (string) to trigger the magic __toString() method.
echo json_encode(array(
'html' => (string)(new Suggestion(array(
'id' => $mysqli->insert_id,
'suggestion' => $_GET['content']
)))
));
}
When jQuery fires the ‘vote‘ request, it does not expect any return values, so the script does not output any. In the ‘submit‘ action, however, jQuery expects a JSON object to be returned, containing the HTML markup of the suggestion that was just inserted. This is where we create a new Suggestion object for the sole purpose of using its __toString() magic method and converting it with the inbuilt json_encode() function.
Suggest & Vote on Features
The jQuery
All of the jQuery code resides in script.js. It listens for click events on the green and red arrows. But as suggestions can be inserted at any point, we are using the live() jQuery method, so we can listen for the event even on elements that are not yet created.
script.js
$(document).ready(function(){
var ul = $('ul.suggestions');
// Listening of a click on a UP or DOWN arrow:
$('div.vote span').live('click',function(){
var elem = $(this),
parent = elem.parent(),
li = elem.closest('li'),
ratingDiv = li.find('.rating'),
id = li.attr('id').replace('s',''),
v = 1;
// If the user's already voted:
if(parent.hasClass('inactive')){
return false;
}
parent.removeClass('active').addClass('inactive');
if(elem.hasClass('down')){
v = -1;
}
// Incrementing the counter on the right:
ratingDiv.text(v + +ratingDiv.text());
// Turning all the LI elements into an array
// and sorting it on the number of votes:
var arr = $.makeArray(ul.find('li')).sort(function(l,r){
return +$('.rating',r).text() - +$('.rating',l).text();
});
// Adding the sorted LIs to the UL
ul.html(arr);
// Sending an AJAX request
$.get('ajax.php',{action:'vote',vote:v,'id':id});
});
$('#suggest').submit(function(){
var form = $(this),
textField = $('#suggestionText');
// Preventing double submits:
if(form.hasClass('working') || textField.val().length<3){
return false;
}
form.addClass('working');
$.getJSON('ajax.php',{action:'submit',content:textField.val()},function(msg){
textField.val('');
form.removeClass('working');
if(msg.html){
// Appending the markup of the newly created LI to the page:
$(msg.html).hide().appendTo(ul).slideDown();
}
});
return false;
});
});
When a click on one of those arrows occurs, jQuery determines whether the ‘inactive’ class is present on the LI element. This class is only assigned to the suggestion, if the user has voted during the last day, and, if present, the script will ignore any click events.
Notice how $.makeArray is used to turn the jQuery objects, containing the LI elements, into a true array. This is done, so we can use the array.sort() method and pass it a custom sort function, which takes two LIs at the same time and outputs a negative integer, zero or a positive integer depending on which of the two elements has a grater rating. This array is later inserted into the unordered list.
The CSS
Now that we have all the markup generated, we can move on with the styling. As the styling is pretty much trivial, I only want to show you the class that rounds the top-left and bottom-right corners of the elements that it is applied to. You can see the rest of the CSS rules in styles.css.
styles.css
.rounded,
#suggest,
.suggestions li{
-moz-border-radius-topleft:12px;
-moz-border-radius-bottomright:12px;
-webkit-border-top-left-radius:12px;
-webkit-border-bottom-right-radius:12px;
border-top-left-radius:12px;
border-bottom-right-radius:12px;
}
Notice that the Mozilla syntax differs from the standard in the way it targets the different corners of the element. Keeping that in mind, we can apply this class to pretty much every element, as you can see from the demonstration.
With this our Feature Suggest App is complete!
Conclusion
If you plan to set up this script on your own server, you would need to create the two suggestion tables by running the code found in tables.sql in the SQL tab of phpMyAdmin. Also remember to fill in your database connection details in connect.php.
You can use this script to gather precious feedback from your visitors. You can also disable the option for users to add new suggestions, and use it as a kind of an advanced poll system.
Be sure to share your thoughts in your comment section below.