The design of the database
Design Three tables: poll results tables (count_voting), Voter Records (ip_votes), User table (users)
The results of the poll were used to count the final voting records, and I gave it 4 fields: the name of the item (SELECTNAME), the voting label name (LabelName), the number of votes (countvotes).
The Voter record table is used to register the voter's IP (IP), geographic location (Location), polling time (Votetime), and the name of the voting item (selectname). Then I added an ID to it.
User tables are used primarily for administrators, including user name (name) and password (passwd).
The SQL script that generates the table is as follows:
| The code is as follows |
Copy Code |
|
--
--The structure of the table ' count_voting '
--
DROP TABLE IF EXISTS ' count_voting ';
CREATE TABLE IF not EXISTS ' count_voting ' (
' selectname ' varchar not NULL,
' labelname ' varchar not NULL,
' Countvotes ' bigint 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 tables ';
-- --------------------------------------------------------
--
--The structure of the table ' Ip_votes '
--
DROP TABLE IF EXISTS ' ip_votes ';
CREATE TABLE IF not EXISTS ' ip_votes ' (
' ID ' bigint (unsigned not NULL auto_increment COMMENT ' voter serial number: self-increasing ',
' IP ' varchar not NULL COMMENT ' voter IP ',
' Location ' varchar not NULL COMMENT ' voter position ',
' Votetime ' datetime not NULL,
' selectname ' varchar 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 inserts on ' Ip_votes '
For each ROW UPDATE count_voting SET countvotes = countvotes + 1 WHERE selectname = NEW. Selectname
//
DELIMITER;
-- --------------------------------------------------------
--
--The structure of the table ' user '
--
DROP TABLE IF EXISTS ' user ';
CREATE TABLE IF not EXISTS ' user ' (
' Name ' varchar not NULL COMMENT ' admin username ',
' passwd ' char (+) not NULL COMMENT ' login password MD5 value '
) Engine=innodb DEFAULT charset=utf8 comment= ' user table ';
--
--the data in the Dump table ' user '
--
INSERT into ' user ' (' name ', ' passwd ') VALUES
(' Ttxi ', ' 700469ca1555900b18c641bf7b0a1fa1 '),
(' Jitttanwa ', ' adac5659956d68bcbc6f40aa5cd00d5c ');
--
--Restricting the exported table
--
--
--Limit table ' Ip_votes '
--
ALTER TABLE ' ip_votes '
ADD CONSTRAINT ' ip_votes_ibfk_1 ' FOREIGN KEY (' selectname ') REFERENCES ' count_voting ' (' Selectname ') on the 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 inserting data into the Ip_votes table. And then the last sentence is to set the external association word.
Frame design
the Operatordb class is used to manipulate the database, and the Operatorvotingdb class is used for the system-specific set of operations.
Using PDO to manipulate the database, I simply encapsulate it:
| The code is as follows |
Copy Code |
|
/**
* Operation Database
* Package PDO to make it easy to operate
*/
Class Operatordb
{
Basic information about connecting to a database
Private $dbms = ' mysql '; Database type, for developers, use a different database, just change this.
Private $host = ' localhost '; Database host Name
Private $dbName = ' voting '; The database used
Private $user = ' voting '; Database connection user Name
Private $passwd = ' voting '; The 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);//Initialize a PDO object, which 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)
{
}
} |
The information of the connection database is encapsulated in to facilitate the subsequent operation.
| 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;
}
/**
* Empty All tables in voting data
*
* Call the database action class to perform the operation of the clear database
*/
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];//use MySQL to calculate today time
echo $time. " <br> ";
echo $row [0][0];
if ($time-$row [0][0]<1)//table $time Comparison of the maximum time and present time
{
echo "Failed to vote, the same IP need to vote every other day";
Return
}
}
Echo $sql;
echo "Vote success!"
$this->odb->exec ($sql);
}
/**
* Add the line of the Selectname field
*
* @param string $name
* @param string $label
* @param int $count
*/
public Function Addselectname ($name, $l Abel, $count =0)
{
$sql = INSERT into Count_ Voting VALUES (' $name ', ' $label ', $count); ";
$this->odb->exec ($sql);
}
/**
* Get total voting, sorted by number of votes
*
* Sort by countvotes field, return 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 Countvotes DESC; ";
}
// Echo $sql;
return $this->odb->query ($sql);
}
/**
* Results of voting, sorted by number of votes and grouped by label
*
* Sort by countvotes field and group by LabelName field, return count_voting table
*/
Public Function Getvotesgroupbylabel ()
{
$sql = "SELECT * from Count_voting order by labelname DESC;";
Echo $sql;
return $this->odb->query ($sql);
}
}
?>
|
