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.
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.
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.
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.
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.
Bootstrap Studio
The revolutionary web design tool for creating responsive websites and apps.
Learn more
Awesome!! As Always!! Best tutorials from the net!!
Good Job!
Nice :) thats what i actually wanted (y)
Totally awesome!
very very nice work thanks a lot
I love it!
I would really love a way to moderate the new feature suggestions as I think people are going to add alot of spam.
Make it visible to the guy who adds it, but its not visible to others until it has been moderated.
Quality tutorials, as always.
@schiwe: i agree 100%
@ Brad
Yes, spam could become a big problem. One way you could deal with it, is holding all the suggestions for moderation (but there is a big chance that you will get tired of having to moderate every single suggestion).
I think a more elegant solution would be to implement Akismet (the same anti-spam that guards WordPress blogs). It is free, and if you own a blog, you probably already have an API key.
It would be a great material for a write up (or a microtut), but I will come round to doing it after I release Tzine's redesign next week (yep, that suggestion is actually real and I am happy that it held up in the Top 5).
Works great and thanks for sharing the tutorial. But is a app like GetSatisfaction.com not more useful? There is no real interaction with your users this way.
Is it possible to make a To Do-App out of this? would be perfect.
Very good material!
However, when trying this on my server, the returned suggestion title was displayed with all the single quotes escaped.
To fix this I added:
just before the line with the
appendTo(...)call.Thank you!
Pretty cool idea and very well implemented..
always delivers top class tutorials.Thank you..
You know, I was wondered, how you guys distributed all these quality stuff for FREE while others are charging for this!
What a wonder usages of jQuery! Am just loving it.
BTW, is there any way to control the row being displayed. Like, if we don't have much space, then we would love to display only latest 5-10 rows. And also there must be a scrolling function added to this so that we can scroll down and see the old posts.
Any help?
awesome plugin. I have seen same type of system in Google support forum.
nice Tutorials Thank
Awesome!... :)
thank you
Awesome stuff guys. Being a web developer, I follow this blog regularly. Starting from fancy 404 page to latest python tutorials , everything here is well documented and very well explained.
Hats off to the tutorialzine team !
Thanks for sharing such a nice stuff. :)
Please keep on sharing.
How would I go about adding just one thing that might say, "Do you like this website design?" with the thumbs up/thumbs down but no suggestions. Just the one I put in.
Thanks for any help.
Great tutorials.
Can you (or anyone) explain this section of suggestions.php:
$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 ");I understand the AS statements for s and v, but I'm not following what "(v.ip IS NULL,0,1) AS have_voted" is doing. Perhaps if someone could redescribe this code without the the AS statements I would find it clearer. Thank you for this wonderful tutorial.
The code you pointed is actually an if statement:
When v.ip IS NULL evaluates to true (no ip address found, the user hasn't voted), the if statement returns 0 (the second argument), otherwise the return value is 1 (the third argument). The AS statement, or alias, turns this value into a have_voted field, which will be available to our PHP code.
This is a great tutorial. I really like how mine turned out. It works great!
Hi Martin , first of all , great tutorial . Im gonna use it in my website, but, i want to modify one option, i want, the user vote one option, like a poll, i hope you can tell me how i can modify it, thanks.
What should I add if I want to include the name?
It won't work after I upgrade to latest Firefox version, I get a security warning whenever someone tries to add a suggestion:
uncaught exception: [Exception... "Security error" code: "1000" nsresult: "0x805303e8 (NS_ERROR_DOM_SECURITYERR)" location: "http://maroc.voxpopolo.com/js/scripts.js Line: 717"]
https://apis.google.com//apps-static/_/js/gapi/gcm_ppb,googleapis_client,plusone/rt=j/ver=OGnRrX9QXk8.fr./sv=1/am=!w4GKF13ZQiaI-RAuJg/d=1/cb=gapi.loaded0
Line 138
It works fine on IE though.. --"
Hi, great tutorial, i was just wondering, which parts of the code would i have to edit to make this work with older mysql. I know mysqli is better, but im using a user login system currently that doesnt use mysql, and they dont seem to work well together
thanks
Hi Martin, I've been going through your tutorials for a while now.. definitely learning a lot, thanks for having this site. :)
I'm having some issues with this one though, not really sure what the problem is but I can't get it to run. I get a blank page when I open suggestions.php on my localhost.
I've been googling around for solutions for the past few days, no luck. I tried enabling PHP error tracking, went though all the files checking for missing semicolons, braces etc. still no luck.
What am I missing? Any help would be greatly appreciated.
A blank screen usually happens if there is an error present but errors are suppressed. Check your error log files, you should find an error description there.
Thanks Martin. Had to enable the 'mysqli' extension in the php.ini file. All good now. :)
Hi. It loads on my site and I can make suggestions but as soon as i refresh the page they are gone. Any ideas?
Great script! Thank you for sharing. Can this be modified so that only one suggestion can be voted for, then all the other voting buttons become inactive? I've tried changing the javascript in the event listener when a vote is cast, but I don't know how to loop through all the LI elements to change the class from active to inactive.
very nice work thanks, Awesome!!
I love it!