This article introduces in detail the voting system implementation principles and implementation code. If you need it, please refer to it.
Database Design
Three tables are designed: the voting result statistical table (count_voting), the voter record table (ip_votes), and the user table (user)
The voting result statistical table is used to count the final voting records. I have set four fields for it: the name of the voting item (SelectName) and the LabelName of the voting item (LabelName) number of votes (CountVotes ).
The voter record table is used to register the ip address, Location, VoteTime, and SelectName of the voter ). Then I add an ID to it.
The User table is mainly used by the Administrator, including the user name and password (passwd ).
The SQL script for table generation is as follows:
| The Code is as follows: |
Copy code |
--
-- 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 = 'vote statistics '; ---------------------------------------------------------- --
-- 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 number: auto incrementing ',
'IP' varchar (15) not null comment 'voter ip ',
'Location' varchar (40) not null comment' voter Location ',
'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' logon password MD5 value'
) ENGINE = InnoDB default charset = utf8 COMMENT = 'user table '; --
-- Store the data in the table 'user'
-- Insert into 'user' ('name', 'passwd') VALUES
('Ttxi', '700469ca1555900b18c641bf7b0a1fa1 '),
('Jitttanwa ', 'adac5659956d68bcbc6f40aa5cd00d5c '); --
-- 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; |
From the script, we can see that I have created a trigger. when inserting data into the ip_votes table, I will add 1 TO THE CountVotes FIELD IN THE count_voting table. The last sentence is to set the external Association word.
Framework Design
OperatorDB class is used to operate databases, and OperatorVotingDB class is used to operate specific sets of the system.
To use PDO to operate databases, we can encapsulate the following:
| The Code is as follows: |
Copy code |
/**
* Database operations
* Encapsulate PDO for convenient operations
*/
Class OperatorDB
{
// Basic information for connecting to the database
Private $ dbms = 'mysql'; // database type. for developers, you only need to change this type to use different databases.
Private $ host = 'localhost'; // Database host Name
Private $ dbName = 'voting '; // database used
Private $ user = 'voting'; // database connection username
Private $ passwd = 'voting '; // 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); // initialize a PDO object, is to create a database connection object $ db
}
Catch (PDOException $ e)
{
Die ("<br/> database connection failed (creater PDO Error !) : ". $ E-> getMessage ()." <br/> ");
}
}
Public function _ destruct ()
{
$ This-> pdo = null;
}
Public function exec ($ SQL)
{
}
Public function query ($ SQL)
{
}
} |
Encapsulate the information of the connected database to facilitate subsequent operations.
| The Code is as follows: |
Copy code |
<? Php
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 clear database operations
*/
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 the 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]; // use the today time calculated by mysql
// Echo $ time. "<br> ";
// Echo $ row [0] [0];
If ($ time-$ row [0] [0] <1) // compare the maximum time in the table with the current time $ time
{
Echo "failed to vote. It takes one day to vote for the same ip address ";
Return;
}
}
// Echo $ SQL;
Echo "Vote successful! ";
$ This-> odb-> exec ($ SQL );
} /**
* Add the row of the 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 );
} /**
* Obtain the total voting results sorted by votes
*
* Sort by CountVotes field and 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 );
} /**
* Obtain the voting result, sorted by votes, and grouped by tags
*
* Sort by CountVotes field and group by LabelName field. The count_voting table is returned.
*/
Public function getVotesGroupByLabel ()
{
$ SQL = "SELECT * FROM count_voting ORDER BY LabelName DESC ;";
// Echo $ SQL;
Return $ this-> odb-> query ($ SQL );
}
}
?> |
1 2 3
