PHP voting system simple implementation source code (1/3)

This article introduces in detail the implementation principle and implementation code of the voting system. Friends in need can refer to it.

Database design
Design three tables: voting result statistics table (count_voting), voter record table (ip_votes), user table (user)

voting result statistics The table is used to count the final voting records. I have four fields for it: the name of the voted item (SelectName), the label name of the voted item (LabelName) (which plays a role in classification), and the number of votes (CountVotes).

The voter record table is used to register the voter’s IP (IP), geographical location (Location), voting time (VoteTime), and the name of the voted item (SelectName). Then I also add an ID to it.

The user table is mainly used for administrators, including username (name) and password (passwd).

The sql script to generate the table is as follows:

The code is as follows

Copy code

代码如下

复制代码

--
-- 表的结构 `count_voting`
--

DROP TABLE IF EXISTS `count_voting`;
CREATE TABLE IF NOT EXISTS `count_voting` (
`SelectName` varchar(40) NOT NULL,
`LabelName` varchar(40) NOT NULL,
`CountVotes` bigint(20) unsigned NOT NULL,
UNIQUE KEY `SelectName` (`SelectName`),
KEY `CountVotes` (`CountVotes`),
KEY `CountVotes_2` (`CountVotes`),
KEY `CountVotes_3` (`CountVotes`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='投票统计表';

-- --------------------------------------------------------

--
-- 表的结构 `ip_votes`
--

DROP TABLE IF EXISTS `ip_votes`;
CREATE TABLE IF NOT EXISTS `ip_votes` (
`ID` bigint(20) unsigned NOT NULL auto_increment COMMENT '投票人序号：自增',
`IP` varchar(15) NOT NULL COMMENT '投票人IP',
`Location` varchar(40) NOT NULL COMMENT '投票人位置',
`VoteTime` datetime NOT NULL,
`SelectName` varchar(40) NOT NULL,
PRIMARY KEY (`ID`),
KEY `ID` (`ID`),
KEY `SelectName` (`SelectName`)
) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;

--
-- 触发器 `ip_votes`
--
DROP TRIGGER IF EXISTS `vote_count_after_insert_tr`;
DELIMITER //
CREATE TRIGGER `vote_count_after_insert_tr` AFTER INSERT ON `ip_votes`
FOR EACH ROW UPDATE count_voting SET CountVotes = CountVotes + 1 WHERE SelectName = NEW.SelectName
//
DELIMITER ;

-- --------------------------------------------------------

--
-- 表的结构 `user`
--

DROP TABLE IF EXISTS `user`;
CREATE TABLE IF NOT EXISTS `user` (
`name` varchar(10) NOT NULL COMMENT '管理员用户名',
`passwd` char(32) NOT NULL COMMENT '登录密码MD5值'
) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='用户表';

--
-- 转存表中的数据 `user`
--

INSERT INTO `user` (`name`, `passwd`) VALUES
('ttxi', '700469ca1555900b18c641bf7b0a1fa1'),
('jitttanwa', 'adac5659956d68bcbc6f40aa5cd00d5c');

--
-- 限制导出的表
--

--
-- 限制表 `ip_votes`
--
ALTER TABLE `ip_votes`
ADD CONSTRAINT `ip_votes_ibfk_1` FOREIGN KEY (`SelectName`) REFERENCES `count_voting` (`SelectName`) ON DELETE CASCADE ON UPDATE CASCADE;

---- Table structure `count_voting`-- DROP TABLE IF EXISTS `count_voting`;CREATE TABLE IF NOT EXISTS `count_voting` ( `SelectName` varchar(40) NOT NULL, `LabelName` varchar(40) NOT NULL, ` CountVotes` bigint(20) unsigned NOT NULL, UNIQUE KEY `SelectName` (`SelectName`), KEY `CountVotes` (`CountVotes`), KEY `CountVotes_2` (`CountVotes`), KEY `CountVotes_3` (`CountVotes`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='Voting statistics table';-- ----------- ---------------------------------------------------- Table structure `ip_votes`--DROP TABLE IF EXISTS `ip_votes`;CREATE TABLE IF NOT EXISTS `ip_votes` ( `ID ` bigint(20) unsigned NOT NULL auto_increment COMMENT 'Voter serial number: auto-increment', `IP` varchar(15) NOT NULL COMMENT 'Voter IP', `Location` varchar(40) NOT NULL COMMENT 'Voter position', `VoteTime` datetime NOT NULL, `SelectName` varchar(40) NOT NULL, PRIMARY KEY (`ID`), KEY `ID` (` ID`), KEY `SelectName` (`SelectName`)) ENGINE=InnoDB DEFAULT CHARSET=utf8 AUTO_INCREMENT=4 ;---- Trigger `ip_votes`--DROP TRIGGER IF EXISTS `vote_count_after_insert_tr`;DELIMITER //CREATE TRIGGER `vote_count_after_insert_tr` AFTER INSERT ON `ip_votes` FOR EACH ROW UPDATE count_voting SET CountVotes = CountVotes + 1 WHERE SelectName = NEW.SelectName//DELIMITER ;---------------------------- --------------------------------- Table structure `user`--DROP TABLE IF EXISTS `user`;CREATE TABLE IF NOT EXISTS `user` ( `name` varchar(10) NOT NULL COMMENT 'Administrator username', `passwd` char(32) NOT NULL COMMENT 'Login password MD5 value') ENGINE=InnoDB DEFAULT CHARSET=utf8 COMMENT='User table';---- Transfer Data in the table `user`--INSERT INTO `user` (`name`, `passwd`) VALUES('ttxi', '700469ca1555900b18c641bf7b0a1fa1'), ('jitttanwa', 'adac5659956d68bcbc6f40aa5cd00d5c');---- Limit the exported table------ Limit the table `ip_votes` --ALTER TABLE `ip_votes` ADD CONSTRAINT `ip_votes_ibfk_1` FOREIGN KEY (`SelectName`) REFERENCES `count_voting` (`SelectName`) ON DELETE CASCADE ON UPDATE CASCADE;

As you can see from the script, I created a trigger that adds 1 to the CountVotes field in the count_voting table when data is inserted into the ip_votes table. The last sentence can also be used to set external related words.

Framework design
The OperatorDB class is used to operate the database, and the OperatorVotingDB class is used for the system-specific set of operations.
Use PDO to operate the database, let me simply encapsulate it:

The code is as follows

Copy code

代码如下

复制代码

/**
* 操作数据库
* 封装PDO，使其方便自己的操作
*/
class OperatorDB
{
    //连接数据库的基本信息
    private $dbms='mysql';       //数据库类型,对于开发者来说，使用不同的数据库，只要改这个.
    private $host='localhost';       //数据库主机名
    private $dbName='voting';     //使用的数据库
    private $user='voting';       //数据库连接用户名
    private $passwd='voting';     //对应的密码
    private $pdo=null;

    public function __construct()
    {
        //dl("php_pdo.dll");
        //dl("php_pdo_mysql.dll");
        $this->dsn="$this->dbms:host=$this->host;dbname=$this->dbName";
        try
        {
            $this->conn=new PDO($this->dsn,$this->user,$this->passwd);//初始化一个PDO对象，就是创建了数据库连接对象$db
        }
        catch(PDOException $e)
        {
            die("
数据库连接失败(creater PDO Error！): ".$e->getMessage()."
");
        }
    }
    public function __destruct()
    {
        $this->pdo = null;
    }
    public function exec($sql)
    {
    }
    public function query($sql)
    {
    }
}

/** * Operation database * Encapsulate PDO to make it convenient for your own operation */class OperatorDB{ // Basic information for connecting to the database private $dbms='mysql'; //Database type. For developers, if they use different databases, just change this. private $host='localhost'; //Database Host name private $dbName='voting'; //Database used private $user='voting'; //Database connection user name private $passwd='voting'; //Corresponding Password private $pdo=null; public function __construct() { //dl("php_pdo.dll"); //dl("php_pdo_mysql.dll "); $this->dsn="$this->dbms:host=$this->host;dbname=$this->dbName" > $this->conn=new PDO($this->dsn,$this->user,$this->passwd);//Initializing a PDO object means creating a database connection object $db ): ".$e->getMessage()."
"); } } public function __destruct() { $this-> ;pdo = null; } public function exec($sql) { } public function query($sql) { }}

Encapsulate the database connection information to facilitate subsequent operations.

The code is as follows

Copy code

require_once 'OperatorDB .php';
class OperatorVotingDB
{
private $odb;

public function __construct()
{
$this->odb = new OperatorDB();
}
public function __destruct()
{
odb = null;
}

/**
* Clear all tables in the voting data
*
* Call the database operation class to perform the clear database operation
*/
public function clearTables()
{
$sqls = array("TRUNCATE ip_votes;","TRUNCATE count_voting;");
$this->odb->exec($sqls[0]);
$this->odb->exec($sqls[1]);
}

/**
* Reset the CountValues field in the count_voting table to 0
*
*/
public function resetCountValues()
{
use using with using using SET CountVotes = 0; " >*/
public function vote($ip,$loc,$name)
{

$sql = "INSERT INTO ip_votes VALUES (NULL, '$ip', '$loc', NOW() , '$name')";

$subsql = "SELECT MAX(to_days(VoteTime)) FROM ip_votes WHERE IP='$ip'";
$stm = $this->odb->query ($subsql);
If (count($row=$stm->fetchAll())==1)
{

$subsql = "SELECT to_days('$now');"; = $ STM [0]; // TODAY Time
// Echo $ Time. "& LT; Br & GT;";
// Echo $ Row [0];
If ($ Time-$ Row [0] [0] & lt; 1) // The largest time in the table and the current time $ Time compare
{
Echo "to vote. ";
>exec($sql );
}

    /**
* Add the row of SelectName field
*
* @param string $name
* @param string $label
* @param int $count
*/
    public function addSelectName($name, $label, $count=0)
    {
        $sql = "INSERT INTO count_voting VALUES ('$name', '$label', $count);";
        $this->odb->exec($sql);
    }

    /**
* Get the total votes, sort the results by the number of votes
*
* Sort by the CountVotes field, return the count_voting table
*
* @param int $n
*
*/
    public function getVotesSortByCount($n=-1)
    {
        $sql = "SELECT * FROM count_voting ORDER BY CountVotes DESC LIMIT 0 , $n;";
        if (-1 == $n)
        {
            $sql = "SELECT * FROM count_voting ORDER BY CountVotes DESC;";
        }
//        echo $sql;
        return $this->odb->query($sql);
    }

    /**
* Get the voting status, sort by the number of votes and group the results by label
*
* Sort by the CountVotes field and group by the LabelName field, return the count_voting table
*/
    public function getVotesGroupByLabel()
    {
        $sql = "SELECT * FROM count_voting ORDER BY LabelName DESC;";
//        echo $sql;
        return $this->odb->query($sql);
    }
}
?>

1 2 3

PHP voting system simple implementation source code (1/3)_PHP tutorial