mysql 中计数器_PHP和MySQL中的简单投票计数

mysql 中计数器

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.

有时，我们会处理受欢迎程度的问题，因此我们需要一种收集客户意见的方法。 本文显示了一个简单的教学示例，该示例说明了如何让客户投票选择喜欢的颜色。 创建表格后，整个过程将包含在一个PHP脚本中，该脚本收集投票并显示汇总结果。

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.

我们需要做的第一件事是创建两个表。 一张桌子将包含颜色选择。 每种颜色将有一行。 另一个表将包含投票。 每一票将有一行。 您可能想知道为什么我们不只是在颜色表中添加一个计数器并直接在颜色行中累积票数之和。 我们当然可以这样做，但是数据库存储很便宜，我们可能想知道的不仅仅是每种颜色获得多少票。 如果我们分别记录了每个投票以及关于客户的一些信息（在此示例中仅保留IP地址），我们将能够进行一段时间的流行趋势分析或使用IP定位服务来回答诸如，“哪种颜色在纽约最受欢迎？” 稍加创造力，您就可以将这些概念扩展到其他问题和其他答案。

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.

我们的第一个脚本创建这些表。 我们连接并选择了数据库，一旦到达脚本的第28行，我们就知道该数据库已经可以使用。 当我们测试这样的脚本时，能够擦除数据库表并每次使用干净的测试数据集重新开始都是很有用的。 第31-33行的代码提供了该选项。 我们创建颜色表（第36行）和投票表（第49行），并将颜色选择添加到颜色表（第64行）。 设置完成。

<?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 ( &#