Design and implementation sharing of PHP+MySQL voting system

The system is not big. I divided the process of completing this system into three steps
•Database design
•System framework design
•Front-end beautification

Database design
Design three tables: voting result statistics table (count_voting), voter record table (ip_votes), user table (user)
The voting result statistics table is used to count the final voting records. I made 4 of them. Fields: 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:

Copy code The code is as follows:

 
-- 
 -- 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 the data in the table `user` 
-- 
INSERT INTO `user` (`name`, `passwd`) VALUES 
('ttxi', '700469ca1555900b18c641bf7b0a1fa1'), 
('jitttanwa', 'adac5659956d68bcbc6f40aa5c d00d5c'); 
-- 
-- Restrict exported tables 
-- 
-- 
-- Restrict 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, When inserting data into the ip_votes table, add 1 to the CountVotes field in the count_voting 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:

Copy the code The code is as follows:

 
/**
* Operate 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 For developers, if you use a different database, just change this. 
private $host='localhost'; //Database host name
private $dbName='voting'; //Database used
private $user='voting'; //Database connection username
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"; 
try 
{ 
$this->conn=new PDO($this->dsn,$this->user, $this->passwd);//Initializing a PDO object means creating a database connection object $db 
} 
catch(PDOException $e) 
{ 
die("
Database connection failed (creater PDO Error!): ".$e->getMessage()."
"); 
} 
} 
public function __destruct() 
{ 
$this->pdo = null; 
} 
public function exec($sql) 
{ 
} 
public function query($sql) 
 { 
} 
} 

Encapsulate the information connecting to the database to facilitate subsequent operations.

Copy code The code is as follows:

 
require_once 'OperatorDB.php'; 
class OperatorVotingDB 
{ 
private $odb; 
public function __construct() 
{ 
$this->odb = new OperatorDB(); 
} 
public function __destruct( ) 
{ 
$this->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() 
{ 
$sql = "UPDATE count_voting SET CountVotes = 0;"; 
$this->odb->exec($sql); 
} 
/**
* Vote
* Write information to ip_votes table
* @param type $ip 
* @param type $loc 
* @param type $time 
* @param type $ name 
*/ 
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) 
{ 
$now = date("Y-m-d H:i:s"); 
$subsql = "SELECT to_days('$now');"; 
$stm = $this->odb->query($subsql)->fetch(); 
$time = $stm[0];//Today time calculated using mysql
/ / echo $time."
"; 
// echo $row[0][0]; 
if ($time-$row[0][0]<1)//table Compare the largest time with the current time $time 
{ 
echo "The vote failed, the same IP needs to be voted another day"; 
return; 
} 
} 
// echo $sql; 
echo "Vote successful!"; 
$this->odb->exec($sql); 
} 
/**
* Add row for 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); 
} 
} 
?> 

There are also necessary functions below

Copy the code The code is as follows:

/**
* Page jump function
* Implemented using js
* @param string $url
*/
function goToPgae($url)
{
echo "";
}
function jsFunc($fun, $arg=null)
{
echo "";
}
function jsFunc3($fun, $arg1=null,$arg2=null,$arg3=null)
{
echo "";
//echo $fun."('$arg1','$arg2','$arg3');";
}
function isLoginNow()
{
if ($_COOKIE["user"]=='')
{
return false;
}
return true;
}
function getClientIP()
{
if ($_SERVER["HTTP_X_FORWARDED_FOR"])
{
if ($_SERVER["HTTP_CLIENT_IP"])
{
$proxy = $_SERVER["HTTP_CLIENT_IP"];
}
else
{
$proxy = $_SERVER["REMOTE_ADDR"];
}
$ip = $_SERVER["HTTP_X_FORWARDED_FOR"];
}
else
{
if ($_SERVER["HTTP_CLIENT_IP"])
{
$ip = $_SERVER["HTTP_CLIENT_IP"];
}
else
{
$ip = $_SERVER["REMOTE_ADDR"];
}
}
return $ip;
}
//从123查获取ip
function getIpfrom123cha($ip) {
$url = 'http://www.123cha.com/ip/?q='.$ip;
$content = file_get_contents($url);
$preg = '/(?<=本站主数据:

)(.*)(?=

)/isU';
preg_match_all($preg, $content, $mb);
$str = strip_tags($mb[0][0]);
//$str = str_replace(' ', '', $str);
$address = $str;
if($address == '') {
$address = '未明';
}
return $address;
}
//从百度获取ip所在地
function getIpfromBaidu($ip) {
$url = 'http://www.baidu.com/s?wd='.$ip;
$content = file_get_contents($url);
$preg = '/(?<=

)(.*)(?=

)/isU';
preg_match_all($preg, $content, $mb);
$str = strip_tags($mb[0][1]);
$str = str_replace(' ', '', $str);
$address = substr($str, 7);
if($address == '') {
$address = '未明';
}
return $address;
}
?>

然后就是后台管理员的操作怎么弄了，主要是添加投票项的功能，操作数据库上面已经实现。后面的基本上是页面怎么设置，关系到js。添加投票项的页面是动态的，如下：

复制代码代码如下:

function addVote()
{
right.innerHTML="

添加投票项

";
right.innerHTML+="投票项标签";
addInput("right","cLabelName","地区名");
right.innerHTML+="
投票项名称";
addInput("right","cSelectName","学校名");
right.innerHTML+="
";
var args = ''./add.php','cSelectName','cLabelName'';
var str = '';
right.innerHTML+=str;
}
//添加文本框
function addInput(parent,id,pla)
{
//创建input
var input = document.createElement("input");
input.type = "text";
input.id = id;
input.placeholder = pla;
document.getElementById(parent).appendChild(input);
}

效果：

清空投票项也差不多，下过如下：

添加投票项是通过url传递变量到add.php页面的。

复制代码代码如下:

 
require_once '../api/func.php'; 

if (!isLoginNow()) 
{ 
goToPgae("./ index.php"); 
} 

$name = $_GET["cSelectName"]; 
$label = $_GET["cLabelName"]; 
//echo $name. "
".$label; 
require_once '../api/OperatorVotingDB.php'; 
$ovdb=new OperatorVotingDB(); 
$ovdb->addSelectName($name,$ label); 
require './header.htm'; 
goToPgae("./admin.php?page=add&auto="."$label"."&id=cLabelName&foc=cSelectName&msg=Added successfully"); 
?> 

The following are two functions to jump to the page, js (the jump page function in func.php above is also implemented through js).

Copy code The code is as follows:

 
//js 
function goToPage(url,arg1,arg2) 
 { 
var a = document.getElementById(arg1).value; 
var b = document.getElementById(arg2).value; 
url += '?'+arg1+'='+a; 
url += '&'+arg2+'='+b; 
window.location.href=url; 
} 

function goToPage1(url) 
{ 
window. location.href=url; 
} 

The modification and deletion function is not implemented yet. I probably won’t be able to implement that, it’s almost the same as adding functionality in js.

There are many imitations on the Internet about the login module. Just submit the form, search the database, and return the results. If successful, a cookie is set, and each page in the background is added with the function of detecting cookies.

Front-end beautification
The index.php page first operates the database to obtain voting items and votes, and then displays them (beautify the frame interface through css+div), and finally clicks the voting button to submit the form and jump Go to the vote.php page.

For css, I copied it from the Internet. The effect I got is as follows:

This thing is considered a very small information management system. I have put the source code of this thing on github (https://github.com/hanxi/voting). You can feel free to do so. Download and modification can also be downloaded from Script Home (click to download). Readers are welcome to reply and communicate. This aspect is not my strong point. I have many shortcomings and I hope you can give me some advice.

Author: Han Xi (Han Xi’s Technology Blog - Blog Park)
Weibo: t.qq.com/hanxi1203
Source: hanxi.cnblogs.com

Design and implementation sharing of PHP+MySQL voting system_PHP tutorial

添加投票项