Home > Article > Backend Development > Design and implementation sharing of PHP plus MySQL voting system
Design and implementation sharing of PHP plus MySQL voting system
- 不言Original
- 2018-04-09 16:35:063428browse
This article shares with you the design and implementation of the PHP plus MySQL voting system. Friends in need can refer to the design and implementation of the
php voting system. Friends in need can refer to the code below. With the database result design, Script House finally provided the source code for download
The system is not big, so 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 have 4 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 user name (name) and password (passwd).
The sql script to generate the table is as follows:
Copy code The code is as follows:
-- -- 表的结构 `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;
As can be seen 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:
Copy the code The code is as follows:
/**
* 操作数据库
* 封装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("<br/>数据库连接失败(creater PDO Error！): ".$e->getMessage()."<br/>");
}
}
public function __destruct()
{
$this->pdo = null;
}
public function exec($sql)
{
}
public function query($sql)
{
}
}
Put the information connecting to the database Encapsulate it to facilitate subsequent operations.
Copy code The code is as follows:
<?php
require_once 'OperatorDB.php';
class OperatorVotingDB
{
private $odb;
public function __construct()
{
$this->odb = new OperatorDB();
}
public function __destruct()
{
$this->odb = null;
}
/**
* 清空Voting数据中的所有表
*
* 调用数据库操作类，执行clear数据库的操作
*/
public function clearTables()
{
$sqls = array("TRUNCATE ip_votes;","TRUNCATE count_voting;");
$this->odb->exec($sqls[0]);
$this->odb->exec($sqls[1]);
}
/**
* 重置count_voting表中的CountValues字段为0
*
*/
public function resetCountValues()
{
$sql = "UPDATE count_voting SET CountVotes = 0;";
$this->odb->exec($sql);
}
/**
* 投票
* 将信息写入ip_votes表
* @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];//使用mysql计算出的today时间
// echo $time."<br>";
// echo $row[0][0];
if ($time-$row[0][0]<1)//表中最大的时间和现在的时间$time比较
{
echo "投票失败，相同ip需要隔一天才能投票";
return;
}
}
// echo $sql;
echo "投票成功!";
$this->odb->exec($sql);
}
/**
* 添加SelectName字段的行
*
* @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);
}
/**
* 获取总投票情况,按票数排序的结果
*
* 按CountVotes字段排序，返回count_voting表
*
* @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);
}
/**
* 获取投票情况,按票数排序并按标签分组的结果
*
* 按CountVotes字段排序并按LabelName字段分组，返回count_voting表
*/
public function getVotesGroupByLabel()
{
$sql = "SELECT * FROM count_voting ORDER BY LabelName DESC;";
// echo $sql;
return $this->odb->query($sql);
}
}
?>
下面还有需要的函数
复制代码 代码如下:
<?php
/**
* 页面跳转函数
* 使用js实现
* @param string $url
*/
function goToPgae($url)
{
echo "<script language='javascript' type='text/javascript'>";
echo "window.location.href='$url'";
echo "</script>";
}
function jsFunc($fun, $arg=null)
{
echo "<script language='javascript' type='text/javascript'>";
echo $fun."('$arg');";
echo "</script>";
}
function jsFunc3($fun, $arg1=null,$arg2=null,$arg3=null)
{
echo "<script language='javascript' type='text/javascript'>";
echo $fun."('$arg1','$arg2','$arg3');";
echo "</script>";
//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 = '/(?<=本站主数据:<\/li><li style=\"width:450px;\">)(.*)(?=<\/li>)/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 = '/(?<=<p class=\"op_ip_detail\">)(.*)(?=<\/p>)/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;
}
?>
Then there is how to do the operations of the background administrator, mainly the function of adding voting items, the operation The database has been implemented above. The following is basically how to set up the page, which is related to js. The page for adding voting items is dynamic, as follows:
Copy code The code is as follows:
function addVote()
{
right.innerHTML="<h2>添加投票项</h2>";
right.innerHTML+="<label>投票项标签<label>";
addInput("right","cLabelName","地区名");
right.innerHTML+="<br><label>投票项名称<label>";
addInput("right","cSelectName","学校名");
right.innerHTML+="<br>";
var args = '\'./add.php\',\'cSelectName\',\'cLabelName\'';
var str = '<input type=button value="\u6dfb加" onclick="goToPage('+args+');"/>';
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);
}Effect:
Clearing the voting items is similar. The download is as follows:
To add voting items, variables are passed to the add.php page through the url.
Copy code The code is as follows:
<?php
require_once '../api/func.php';
if (!isLoginNow())
{
goToPgae("./index.php");
}
$name = $_GET["cSelectName"];
$label = $_GET["cLabelName"];
//echo $name."<br>".$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=添加成功");
?>The following are two functions to jump to the page, js (the jump page function in func.php above 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 the voting items and number of votes, and then displays them (beautify the frame interface through css p), and finally clicks the voting button to submit the form and jump to vote.php page.
I always copy css from the Internet. The effect I got is as follows:
This thing can be regarded as a very small information management system. I have put the source code of this thing on github (https: //github.com/hanxi/voting) is uploaded. You can download and modify it at will or download it 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: Hanxi (Hanxi’s technology blog - Blog Park)
Weibo: t.qq.com/hanxi1203
Source: hanxi.cnblogs.com
Related Recommended:
php configure php-fpm startup parameters and configuration details
Debugging summary for PHP programmers
The above is the detailed content of Design and implementation sharing of PHP plus MySQL voting system. For more information, please follow other related articles on the PHP Chinese website!