<

[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x

Simple Vote Counting in PHP and MySQL

Published on
26,155 Points
11,655 Views
10 Endorsements
Last Modified:
Awarded
Popularity Can Be Measured
Sometimes we deal with questions of popularity, and we need a way to collect opinions from our clients.  This article shows a simple teaching example of how we might elect a favorite color by letting our clients vote for it.  Once our tables are created, our entire process is contained in a single PHP script that collects the votes and shows the aggregate results.

Setting Up Our Data Base Tables
The first thing we need to do is create two tables.  One table will contain the color choices.  There will be one row for each color.  The other table will contain the votes.  There will be one row for each vote.  You might be wondering why we don't just add a counter to the color table and accumulate the sum of votes directly in the color row.  We certainly could do that, but data base storage is cheap and we might be interested in knowing more than just how many votes each color received.  If we have each vote recorded separately along with a little information about our client (we keep just the IP address in this example) we would be able to do popularity trend analysis over time or use an IP-to-location service to answer questions like, "Which color is most popular in New York?"  With a little creativity you might extend these concepts to cover other questions and other answers.

Our first script creates these tables.  We connect and select the data base and once we have gotten to line 28 of our script, we know the data base is ready for our use.  When we are testing a script like this, it is useful to be able to wipe out our data base tables and start over each time with a clean test data set.  The code on lines 31-33 provides for that option.  We create the color table (line 36) and the votes table (line 49) and we add the color choices to the color table (line 64).  And we are done with the setup.
 
<?php // RAY_EE_voting_create.php
/**
 * https://www.experts-exchange.com/articles/5256/Simple-Vote-Counting-in-PHP-and-MySQL.html
 * Demonstrate the voting algorithm -- Create the Tables
 */
error_reporting(E_ALL);

// 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 AND SELECT THE DB
$mysqli = new mysqli($db_host, $db_user, $db_word, $db_name);

// DID THE CONNECT/SELECT WORK OR FAIL?
if ($mysqli->connect_errno)
{
    $err
    = "CONNECT FAIL: "
    . $mysqli->connect_errno
    . ' '
    . $mysqli->connect_error
    ;
    trigger_error($err, E_USER_ERROR);
}
// IF WE GOT THIS FAR WE CAN DO QUERIES


// THESE LINES REMOVE EXISTING VERSIONS OF THE TABLES
$mysqli->query("DROP TABLE EE_vote_colors");
$mysqli->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 = $mysqli->query($sql) or trigger_error( "$sql<br/>" . $mysqli->error, E_USER_WARNING );


// 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 = $mysqli->query($sql) or trigger_error( "$sql<br/>" . $mysqli->error, E_USER_WARNING );


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

Open in new window


Data Collection and Reporting
Our second script uses these tables with HTML forms to record the votes.  It also returns the popularity information to the client.  Just as with the first script, we connect and select the data base and once we have gotten to line 28 of our script, we know the data base is ready for our use.

The first significant activity is to get an array with the names of the colors (lines 31-38).  We do this because we want to be sure that any data posted to our script is appropriate to store in the data base, and because we use the array to create the form for voting.  Once we have that array, we check to see if the POST array has data in the color_selections element (line 44).  If so, we take the client's IP address, sanitize the input data, and make a series of INSERT queries to record the votes (lines 46-58).  Note that we insert only the IP address and the color.  The when_voted column is of the TIMESTAMP data type.  The value in the timestamp is set automatically by MySQL as the rows are inserted (or updated) in the table.

Our next step is to show the aggregated voting statistics.  We let our output be guided by our array of colors, using a foreach() iterator to find each of the colors (line 63).  We set a couple of default values (lines 65, 66) in case nobody has voted for some of the colors.  The query on line 67 does several things for us.  It selects all of the rows that match the current color and it orders the rows in descending order by the timestamp column, when_voted.  This means that the most recent vote will be the first record of the results set.  We care about the number of votes for this color (line 69), but we only need one row to find the most recent vote (line 71).  From this row we get the IP address and the timestamp of that vote, and we can prepare our output statement for that color.  The display code is completed at line 88.

Since this is a demonstration script, and we do not care how many times anyone votes, we put up the form every time the script runs.  In a more formal environment we might use a cookie or token to limit the number of times a client can add a vote.

The final step, form creation, is done starting on line 91.  Once again, we use the array of colors to create the form input controls.  Our controls are of the checkbox type which means that any unchecked elements will not be posted to the action script.  And our checkboxes are organized into an array, transmitted to our action script in $_POST["color_selections"].  Because we have our form and action script in the same PHP script file, it is easy to see how the form part of the script and the action part of the script work together.
 
<?php // RAY_EE_voting_vote.php
/**
 * https://www.experts-exchange.com/articles/5256/Simple-Vote-Counting-in-PHP-and-MySQL.html
 * Demonstrate the voting algorithm -- Collect the Votes
 */
error_reporting(E_ALL);

// 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 AND SELECT THE DB
$mysqli = new mysqli($db_host, $db_user, $db_word, $db_name);

// DID THE CONNECT/SELECT WORK OR FAIL?
if ($mysqli->connect_errno)
{
    $err
    = "CONNECT FAIL: "
    . $mysqli->connect_errno
    . ' '
    . $mysqli->connect_error
    ;
    trigger_error($err, E_USER_ERROR);
}
// 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 = $mysqli->query($sql) or trigger_error( "$sql<br/>" . $mysqli->error, E_USER_WARNING );
while ($row = $res->fetch_object())
{
    $colors[] = $row->color;
}
// ACTIVATE THIS TO SEE THE COLORS
// var_dump($colors);


// IF ANYTHING WAS POSTED RECORD THE VOTE
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 = $mysqli->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 = $mysqli->query($sql) or trigger_error( "$sql<br/>" . $mysqli->error, E_USER_WARNING );
    }
}


// SHOW THE STATISTICS FOR THE COLORS
foreach ($colors as $color)
{
    $ipa = 'none';
    $whn = 'no votes yet';
    $sql = "SELECT ip_address, when_voted FROM EE_vote_votes WHERE color = '$color' ORDER BY when_voted DESC";
    $res = $mysqli->query($sql) or trigger_error( "$sql<br/>" . $mysqli->error, E_USER_WARNING );
    $num = $res->num_rows;
    if ($num) {
        $row = $res->fetch_object();
        $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;
    }
}


// CREATE THE FORM TO RECEIVE THE VOTES
echo '<form method="post">';
echo "<br/>VOTE FOR YOUR FAVORITE COLORS" . 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


The Potential for Interesting Queries
With our vote table organized this way we can do all kinds of interesting statistical things.  We can watch for trends over time by selecting the rows where when_voted falls between two DATETIME values.  We can compute the relative popularity of the colors, giving accurate meaning to statements like, "Blue is twice as popular as green."  We could normalize these popularity scores on a scale of one-to-five and create a bar graph or show stars to indicate popularity.  Since we have a client identifier in the form of the IP address, we could potentially extend the algorithm to show the IP address of people who like purple the best, or show our current client the IP address of other people who voted the same way.  If we used a geo-location algorithm to get map points from the IP addresses, we could draw maps showing where color preferences are strongest.  The possibilities are limited only by imagination.

Another View of the Data
Five-star ratings are a common way of showing popularity.  You've probably seen "rate this item" links on e-commerce web sites.  Those sites take a client's rating number (usually zero-to-five) as input and add a row to the ratings table.  A rating of zero is commonly understood to mean "awful" whereas a rating of five means "excellent."  To report the aggregated ratings, the site make a summation of all of the ratings and divides by the counted number of ratings, giving an arithmetic mean.  Then they normalize this average against a scale of one-to-five and produce a little display of stars that shows the average rating.  We will illustrate that process here.  

You may find that some sites give greater weight to more recent ratings.  This makes sense if a vendor is building a new version of a product or constantly improving a product.  It also makes sense if the competition for a product is changing.  Client ratings are subjective and more recent information is probably more useful to current purchasers.  Think of it from the vendor's perspective: "It's still the same teak chair, but now we have a better teak supplier!"  The clients who rate the new teak chair may find their ratings are more influential in the algorithm that creates the five-star display.  Or from the client's perspective, "I bought one of these last year and it was really great, but the one I got this year was not very good."  We won't use a time-driven algorithm like that in this example; we will just show how you might apply a five-star rating to the relative popularity of the colors.

The "voting stars" script starts with all the same things that we did in the "voting vote" script.  The first departure is at line 63.  Instead of immediately creating the output, we do a little data analysis.  We want to compare the relative popularity of the colors, so we need to know the number of votes that each color received.  We can aggregate that information in an associative array, $votes, with the array keys being the colors and the array values being the number of votes. Lines 63-69 create this array.  Smart MySQL experts may find better ways to write these queries, so less work has to be done in PHP!

Next, we need to normalize the total number of votes in two ways.  We need the total to be reduced to a score that is relative to the other scores and exists on a scale from zero to fifty.  And we need the score to be counted by fives, for example, a score of 49 would be rounded to 50 and a score of 46 would be rounded down to 45.  The reason for this kind of normalization is that we can use the normalized scores to create a URL link to one of eleven different images, ranging from zero stars up to five stars.  With a little bit of math (lines 79-81) we get the normalized star rating.  Then we create the URLs of the appropriate images in the $stars array (lines 84-89).  Now we have two associative arrays, both indexed by the name of the color, and we are ready to create the output.  We do that in lines 93-106.  It looks something like the screen shot below this code snippet.
 
<?php // RAY_EE_voting_stars.php
/**
 * https://www.experts-exchange.com/articles/5256/Simple-Vote-Counting-in-PHP-and-MySQL.html
 * Demonstrate the voting algorithm  -- With Rankings up to Five Stars
 */
error_reporting(E_ALL);

// 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 AND SELECT THE DB
$mysqli = new mysqli($db_host, $db_user, $db_word, $db_name);

// DID THE CONNECT/SELECT WORK OR FAIL?
if ($mysqli->connect_errno)
{
    $err
    = "CONNECT FAIL: "
    . $mysqli->connect_errno
    . ' '
    . $mysqli->connect_error
    ;
    trigger_error($err, E_USER_ERROR);
}
// 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 = $mysqli->query($sql) or trigger_error( "$sql<br/>" . $mysqli->error, E_USER_WARNING );
while ($row = $res->fetch_object())
{
    $colors[] = $row->color;
}
// ACTIVATE THIS TO SEE THE COLORS
// var_dump($colors);


// IF ANYTHING WAS POSTED RECORD THE VOTE
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 = $mysqli->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 = $mysqli->query($sql) or trigger_error( "$sql<br/>" . $mysqli->error, E_USER_WARNING );
    }
}


// GET THE COUNT OF VOTES FOR EACH COLOR
$votes = array();
foreach ($colors as $color)
{
    $sql = "SELECT color FROM EE_vote_votes WHERE color = '$color' ";
    $res = $mysqli->query($sql) or trigger_error( "$sql<br/>" . $mysqli->error, E_USER_WARNING );
    $votes[$color] = $res->num_rows;
}

// GET THE MIN AND MAX VALUES FROM OUR ARRAY OF VOTES
$min = min($votes);
$max = max($votes);
$cnt = count($votes);

// NORMALIZE THE VOTE COUNTS BY FIVES ON A SCALE FROM ZERO TO FIFTY
foreach ($votes as $color => $num)
{
    $x = round( ( $num / $max ), 2 );
    $x = round( $x * 10.0 );
    $x = round( ( $x / 2 ), 1 ) * 10.0;

    // CREATE A LINK TO A FIVE-STAR RATINGS IMAGE
    $x = str_pad($x, 2, '0', STR_PAD_LEFT);
    $stars[$color]
    = 'RAY_EE_images/rated'
    . $x
    . '.png'
    ;
}

// SHOW THE RATINGS WITH THE VOTES
foreach ($colors as $color)
{
    $str = '<br/>'
    . '<img src="'
    . $stars[$color]
    . '" /> '
    . $color
    . ' HAS RECEIVED '
    . $votes[$color]
    . ' VOTES '
    . PHP_EOL;

    echo $str;
}


// CREATE THE FORM TO RECEIVE THE VOTES
echo '<form method="post">';
echo "<br/>VOTE FOR YOUR FAVORITE COLORS" . 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


A Couple of Interesting Images
What a screen shot might look like, showing the relative popularity of the colors with five-star ratings.
A screen shot showing the five-star ratingsHere is a Photoshop file with the five-star rating images in named layers.  You can use this file to create your own images with names like "ratedXX.png" -- just copy each layer to a separate file and save it as a transparent PNG with the appropriate name.  As you can see, the unearned stars are gray and half-transparent.  This means the finished PNG images will work well on almost any color background.
rate-template.psd

Summary
In a real world example, we might carry other information in these tables, and we could make the algorithms more efficient by improving our queries and indexing the tables, but at the heart of things, this is all you need to do to allow clients to vote for different choices, and to show them how everyone has voted.

Resources for AJAX Automation
Star ratings seem to be popularly implemented in AJAX.  In this design pattern the star ratings are initially hidden, and the client is asked to vote first in order to see the community consensus expressed in the star ratings.  This link may be helpful if you want to try making the ratings into an AJAX automation.
http://www.codexworld.com/star-rating-system-with-jquery-ajax-php/

Please give us your feedback!
If you found this article helpful, please click the "thumb's up" button below. Doing so lets the E-E community know what is valuable for E-E members and helps provide direction for future articles.  If you have questions or comments, please add them.  Thanks!
 
10
Comment
Author:Ray Paseur
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
7 Comments
 
LVL 60

Expert Comment

by:Kevin Cross
Very nice, Ray! Pretty cool contribution here. Much appreciated. I like the stars addition. You have my Yes vote above.
0
 
LVL 111

Author Comment

by:Ray Paseur
The images used to make the stars shown in the screen shot are available here.  In my example, I resized them from 2000x400 pixels down to 80x16 pixels no stars one half star one star one and one half stars two stars two and one half stars three stars three and one half stars four stars four and one half stars five stars
0
 

Expert Comment

by:wakawaka500
Thanks, this was very useful.
0
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

 

Expert Comment

by:only1wizard
thanks for your help very helpful!
0
 
LVL 54

Expert Comment

by:Scott Fell, EE MVE
Very helpful article
0
 

Expert Comment

by:Rishab tata
Getting this error:

Notice: Undefined variable: mysql in C:\xampp\htdocs\boot\RAY_EE_voting_create.php on line 32

Fatal error: Call to a member function query() on null in C:\xampp\htdocs\boot\RAY_EE_voting_create.php on line 32
1
 
LVL 111

Author Comment

by:Ray Paseur
@Rishab: Thanks for the heads-up.  Those lines are commented out in the sample script, so they didn't throw any errors when I tested.  Try changing $mysql to $mysqli if you want to use them.  I'll update the scripts right now. There were some other "code rot" things that needed refactoring, as well.  Glad you caught this!
0

Featured Post

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Join & Write a Comment

Any person in technology especially those working for big companies should at least know about the basics of web accessibility. Believe it or not there are even laws in place that require businesses to provide such means for the disabled and aging p…
The is a quite short video tutorial. In this video, I'm going to show you how to create self-host WordPress blog with free hosting service.
Suggested Courses

Keep in touch with Experts Exchange

Tech news and trends delivered to your inbox every month