Solved

voting/ rating systom

Posted on 2011-03-08
10
250 Views
Last Modified: 2012-05-11
am trying to make a rating system for top products on my site. i would like my users to click a button to like a product and then on the home page it will only show the top 5 liked products.

the voting system will take all votes from all users of the site and then display the top 5 on the home page for every on to see. i dont want it to be like target advertising but just to show what every one is liking across the hole site.

so am trying to think of some php to get this working.

query to get the current number of likes for the product then update the current number of likes by 1.

a way of not allowing users to vote more than once on a product

any help would be great thx.
0
Comment
Question by:helpchrisplz
  • 5
  • 3
  • 2
10 Comments
 
LVL 14

Accepted Solution

by:
EMB01 earned 250 total points
Comment Utility
So, I don't know what your database looks like but it sounds like this:

// vars
$pid = "this is your product id";

// query to get current number of likes for a product
$sql = "SELECT `likes` FROM `products` WHERE `product_id` = $pid";

// query to increment likes for a product
$sql = "UPDATE `products` SET `likes` = `likes` + 1 WHERE `product_id` = $pid";

As for the final request, "not allowing users to vote more than once per product," you could do this two ways:

-  Use javascript to remove the rating DIV element so they litterally can't click it again after they've voted (but this will only work for the current browsing session).

-  Use PHP/ MySQL to keep track of what user voted on a particular product.  Then, before you run your update query, check whether or not that user has voted on the product before.
0
 
LVL 108

Assisted Solution

by:Ray Paseur
Ray Paseur earned 250 total points
Comment Utility
Twice this morning EE has thrown away what I tried to post for you, so I will be brief.  Here is an example that allows clients to vote for their favorite color.  You can see the algorithms in action here:
http://www.laprbass.com/RAY_temp_jvsmooth_vote.php

The first part of the script is the part that creates the tables.
<?php // RAY_temp_jvsmooth_create.php
error_reporting(E_ALL);


// DEMONSTRATE THE VOTING ALGORITHM - CREATE THE TABLES


// CONNECTION AND SELECTION VARIABLES FOR THE DATABASE
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";        // GET THESE FROM YOUR HOSTING COMPANY
$db_user = "??";
$db_word = "??";


// OPEN A CONNECTION TO THE DATA BASE SERVER
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-connect.php
if (!$db_connection = mysql_connect("$db_host", "$db_user", "$db_word"))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB CONNECTION: ";
    echo "<br/> $errmsg <br/>";
}

// SELECT THE MYSQL DATA BASE
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-select-db.php
if (!$db_sel = mysql_select_db($db_name, $db_connection))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB SELECTION: ";
    echo "<br/> $errmsg <br/>";
    die('NO DATA BASE');
}
// IF WE GOT THIS FAR WE CAN DO QUERIES


// REMOVE OLD VERSIONS OF THE TABLES; IGNORE ANY ERRORS (LIKE NO TABLE OF THIS NAME)
// mysql_query("DROP TABLE EE_vote_colors");
// mysql_query("DROP TABLE EE_vote_votes");


// CREATE THE COLOR TABLE
$sql
= "CREATE TABLE EE_vote_colors
( _key  INT         NOT NULL AUTO_INCREMENT PRIMARY KEY
, color VARCHAR(10) NOT NULL DEFAULT ''
)"
;
$res = mysql_query($sql) or die( "$sql<br/>" . mysql_error() );


// CREATE THE VOTES TABLE
$sql
= "CREATE TABLE EE_vote_votes
( _key       INT         NOT NULL AUTO_INCREMENT PRIMARY KEY
, color      VARCHAR(10) NOT NULL DEFAULT ''
, ip_address VARCHAR(16) NOT NULL DEFAULT 'unknown'
, when_voted TIMESTAMP
)"
;
$res = mysql_query($sql) or die( "$sql<br/>" . mysql_error() );


// LOAD THE STANDARD ROY-G-BIV DATA INTO THE COLOR TABLE
$res = mysql_query( "INSERT INTO EE_vote_colors ( color ) VALUES ( 'Red'    )" );
$res = mysql_query( "INSERT INTO EE_vote_colors ( color ) VALUES ( 'Orange' )" );
$res = mysql_query( "INSERT INTO EE_vote_colors ( color ) VALUES ( 'Yellow' )" );
$res = mysql_query( "INSERT INTO EE_vote_colors ( color ) VALUES ( 'Green'  )" );
$res = mysql_query( "INSERT INTO EE_vote_colors ( color ) VALUES ( 'Blue'   )" );
$res = mysql_query( "INSERT INTO EE_vote_colors ( color ) VALUES ( 'Indigo' )" );
$res = mysql_query( "INSERT INTO EE_vote_colors ( color ) VALUES ( 'Violet' )" );

Open in new window

0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
The second part receives and aggregates the votes, and reports the most popular colors.
<?php // RAY_temp_jvsmooth_vote.php
error_reporting(E_ALL);


// DEMONSTRATE THE VOTING ALGORITHM


// CONNECTION AND SELECTION VARIABLES FOR THE DATABASE
$db_host = "localhost"; // PROBABLY THIS IS OK
$db_name = "??";        // GET THESE FROM YOUR HOSTING COMPANY
$db_user = "??";
$db_word = "??";


// OPEN A CONNECTION TO THE DATA BASE SERVER
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-connect.php
if (!$db_connection = mysql_connect("$db_host", "$db_user", "$db_word"))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB CONNECTION: ";
    echo "<br/> $errmsg <br/>";
}

// SELECT THE MYSQL DATA BASE
// MAN PAGE: http://us2.php.net/manual/en/function.mysql-select-db.php
if (!$db_sel = mysql_select_db($db_name, $db_connection))
{
    $errmsg = mysql_errno() . ' ' . mysql_error();
    echo "<br/>NO DB SELECTION: ";
    echo "<br/> $errmsg <br/>";
    die('NO DATA BASE');
}
// IF WE GOT THIS FAR WE CAN DO QUERIES


// GET THE ARRAY OF COLORS FROM THE DATA BASE
$colors = array();
$sql = "SELECT color FROM EE_vote_colors";
$res = mysql_query($sql) or die( "$sql<br/>" . mysql_error() );
while ($row = mysql_fetch_assoc($res))
{
    $colors[] = $row["color"];
}
// ACTIVATE THIS TO SEE THE COLORS
// var_dump($colors);


// IF ANYTHING WAS POSTED
if (!empty($_POST["color_selections"]))
{
    $ipa = (!empty($_SERVER["REMOTE_ADDR"])) ? $_SERVER["REMOTE_ADDR"] : 'unknown';
    foreach($_POST["color_selections"] as $color => $nothing)
    {
        // NORMALIZE THE POST DATA
        $rgb = mysql_real_escape_string(ucfirst(strtolower(trim($color))));

        // SKIP FIELDS THAT ARE NOT PART OF OUR COLOR SET (POSSIBLE ATTACK?)
        if (!in_array($rgb, $colors)) continue;

        // RECORD A VOTE FOR THIS COLOR
        $sql = "INSERT INTO EE_vote_votes ( color, ip_address ) VALUES ( '$rgb', '$ipa' )";
        $res = mysql_query($sql) or die( "$sql<br/>" . mysql_error() );
    }

    // SHOW THE STATS FOR THE COLORS
    foreach ($colors as $color)
    {
        $sql = "SELECT ip_address, when_voted FROM EE_vote_votes WHERE color = '$color' ORDER BY when_voted DESC";
        $res = mysql_query($sql) or die( "$sql<br/>" . mysql_error() );
        $num = mysql_num_rows($res);
        $row = mysql_fetch_assoc($res);
        $ipa = $row["ip_address"];
        $whn = $row["when_voted"];
        echo "<br/>";
        echo number_format($num);
        echo " VOTES FOR ";
        echo $color;
        if ($num)
        {
            echo " MOST RECENTLY ";
            echo $whn;
            echo " FROM IP ";
            echo $ipa;
            echo PHP_EOL;
        }
    }
    echo "<br/>" . PHP_EOL;
}


// CREATE THE FORM TO RECEIVE THE VOTES
echo '<form method="post">';
echo "VOTE FOR YOUR FAVORITE COLOR" . PHP_EOL;
foreach ($colors as $color)
{
    echo "<br/>";
    echo '<input type="checkbox" name="color_selections[';
    echo "$color";
    echo ']" />';
    echo $color;
    echo PHP_EOL;
}
echo '<br/><input type="submit" value="VOTE NOW" />' . PHP_EOL;
echo '</form>';

Open in new window

0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
Why use a vote table instead of a vote counter?  Because it will allow you to do geographical and time-sensitive analysis, sort of like "trending topics" on Twitter.  Older votes can go into a summary table where only the counts matter.

a way of not allowing users to vote more than once on a product
That would usually be done with a persistent browser cookie, however you need to be aware that clients can and do lose their cookies, sometimes deliberately.  You might also record their IP address, but this is problematic because clients can and do vote from different machines.   And some servers aggregate the IP address for (for example) everyone in an office.  And AOL, plus a few other ISP companies may provide unpredictable IP addresses.

If you go the cookie route, you would set the cookie at the time you received the vote.  PHP has the setcookie() function to help you do this.

HTH, and please post back with any specific questions, ~Ray
0
 
LVL 1

Author Comment

by:helpchrisplz
Comment Utility
please can you tell me what is wrong with my update query here:

$sql = "UPDATE members SET (UserName, Password, UserEmail, age, Location, Gender, Language) = ('$UserName', '$Password', '$UserEmail', '$Age', '$Location', '$Gender', '$Language') WHERE 'MemberID' = $MemberID";
0
How your wiki can always stay up-to-date

Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
- Increase transparency
- Onboard new hires faster
- Access from mobile/offline

 
LVL 1

Author Comment

by:helpchrisplz
Comment Utility
sorry that is another problem i have
0
 
LVL 14

Expert Comment

by:EMB01
Comment Utility
I think it needs to be:

$sql = "UPDATE members SET UserName = '$UserName', Password = '$Password', etc...
0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
In addition to getting the query syntax right, you might want to avoid the use of MySQL reserved words for column names.  That is one way you can be sure that catastrophe is not left to chance!
0
 
LVL 1

Author Closing Comment

by:helpchrisplz
Comment Utility
this is great thx i have been AFK for a bit and only getting this sorted now but thx for this help
0
 
LVL 108

Expert Comment

by:Ray Paseur
Comment Utility
Thanks for the points.  If you decide to use a pair of queries as described at ID:35068902 (SELECT + UPDATE) you will want to learn about LOCK TABLES.  A better way might be to do the UPDATE first, then do the SELECT to get the results after the vote.  Otherwise script racing may cause a loss of data.
0

Featured Post

How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

Join & Write a Comment

Deprecated and Headed for the Dustbin By now, you have probably heard that some PHP features, while convenient, can also cause PHP security problems.  This article discusses one of those, called register_globals.  It is a thing you do not want.  …
I imagine that there are some, like me, who require a way of getting currency exchange rates for implementation in web project from time to time, so I thought I would share a solution that I have developed for this purpose. It turns out that Yaho…
The viewer will learn how to count occurrences of each item in an array.
The viewer will learn how to create a basic form using some HTML5 and PHP for later processing. Set up your basic HTML file. Open your form tag and set the method and action attributes.: (CODE) Set up your first few inputs one for the name and …

771 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

16 Experts available now in Live!

Get 1:1 Help Now