Solved

voting/ rating systom

Posted on 2011-03-08
10
253 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
ID: 35068902
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 109

Assisted Solution

by:Ray Paseur
Ray Paseur earned 250 total points
ID: 35068914
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 109

Expert Comment

by:Ray Paseur
ID: 35068921
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
Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

 
LVL 109

Expert Comment

by:Ray Paseur
ID: 35068947
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
ID: 35070965
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
 
LVL 1

Author Comment

by:helpchrisplz
ID: 35070988
sorry that is another problem i have
0
 
LVL 14

Expert Comment

by:EMB01
ID: 35071419
I think it needs to be:

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

Expert Comment

by:Ray Paseur
ID: 35077069
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
ID: 35125766
this is great thx i have been AFK for a bit and only getting this sorted now but thx for this help
0
 
LVL 109

Expert Comment

by:Ray Paseur
ID: 35127541
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

Announcing the Most Valuable Experts of 2016

MVEs are more concerned with the satisfaction of those they help than with the considerable points they can earn. They are the types of people you feel privileged to call colleagues. Join us in honoring this amazing group of Experts.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Generating table dynamically is the most common issue faced by php developers.... So it seems there is a need of an article that explains the basic concept of generating tables dynamically. It just requires a basic knowledge of html and little maths…
Build an array called $myWeek which will hold the array elements Today, Yesterday and then builds up the rest of the week by the name of the day going back 1 week.   (CODE) (CODE) Then you just need to pass your date to the function. If i…
Explain concepts important to validation of email addresses with regular expressions. Applies to most languages/tools that uses regular expressions. Consider email address RFCs: Look at HTML5 form input element (with type=email) regex pattern: T…
The viewer will learn how to dynamically set the form action using jQuery.

815 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

12 Experts available now in Live!

Get 1:1 Help Now