DB2 和开放源代码: 用 DB2、PHP 和 Linux 实现 Web 投票

-表名: polls
	"polls" 表中的列是：
	pollkey —— 票号，这是主键。 
		question —— 投票问题的文本，放在一个 varchar 字段中。
		pollstartdate —— 投票起始日期。
		pollenddate —— 指定的投票截止日期。
表名: pollresponses
			"pollresponses" 表中的列是：
			responsekey —— 作为应答的惟一标识符，是主键。
			pollkey —— 对 "polls" 表的引用，以便将应答指派给一个投票问题。 
			responsenum —— 用于排列对某一特定问题的应答的编号。
			responsetext —— 应答的文本。
			votes —— 同意此应答的票数。

CONNECT TO PHPWEB;
CREATE TABLE polls (
    pollkey int primary key,
    question varchar(500),
    pollstartdate date,
    pollenddate date
);
CREATE TABLE pollresponses (
    responsekey int primary key,
    pollkey int references polls,
    responsenum int,
    responsetext varchar(300),
    votes int
);
-- create an index on the pollresponse pollkey column
-- since most joins use this to join to the polls table
create index i_response  on pollresponses ( pollkey );
--Example of creating a new poll
INSERT INTO polls VALUES 
(1, 'What do you think of this perfect article?', '2004-05-30','2005-01-01');
--The number 1 is the number of the poll, which is the primary key
--The question 'What do you think of this perfect article?' 
-- is the question the poll will 
--be asking.
--The date '2004-05-30' is the date the poll started. 
--The date '2004-03-22 is the date designated for this poll to end. 
--All dates must be in the format 'YYYY-MM-DD'.
--Example of adding a response that can be chosen by the poll-ee.
INSERT INTO pollresponses VALUES (1, 1, 1, 'It''s absolutely amazing!', 0);
--The first 1 is the number of the response out of all the responses for 
-- all the polls. 
--This is the primary key.
--The second 1 is the number of the primary key of the poll 
-- that this will be a response to.
--The third 1 is the number to order the responses that will 
-- appear within the specific poll that the response belongs to.
--'It''s absolutely amazing' is the text of the response
--The 0 is the amount of votes the response has. This number 
--should ALWAYS start at zero, and will be changed by the PHP script.
--Adding another response:
INSERT INTO pollresponses VALUES (2,1,2, 'It''s good', 0);

<?php
        $dbname = "PHPWEB";
        $username = "phpweb";
        $password = "phpweb1";
 
        // odbc_pconnect returns 0 if the connection attempt fails
        // otherwise it returns a connection ID used by other ODBC functions
                echo ( "attempt connect.....\n " );
        $conn = odbc_pconnect ( $dbname, $username, $password );
 
        if ( $conn == 0 ) {
                echo ( "Connection to database failed." );
		//If connection failed, show what the error message was:
                $sqlerror = odbc_errormsg ( $conn );
                echo ( $sqlerror );
        }
                echo ( "connect ok" );
?>

<?php
                                                                                
$dbname = "PHPWEB";
$username = "phpweb";
$password = "phpweb1";
                                                                                
$dbh = odbc_pconnect ( $dbname, $username, $password );
                                                                                
$today = date("Y-m-d");
$sql = "select * from polls where  pollenddate > '$today' order by pollkey";
$pollexec = odbc_exec($dbh , $sql);
$rows = odbc_num_rows($pollexec);
                                                                                
//if there is one current poll
if ($rows == "1")
{
   //sends the user to vote on that poll
   $data = odbc_fetch_object($pollexec);
   header("Location: vote.php?pollkey=$data->POLLKEY");
   exit;
}
//if there is more than one or zero current polls
else
{
   //sends the user to choosevote.php
   header("Location: choosevote.php");
   exit;
}
                                                                                
?>

<?php
$dbname = "PHPWEB";
$username = "phpweb";
$password = "phpweb1";
$conn = odbc_pconnect ( $dbname, $username, $password );
//if the user doesn't choose an answer...
if($_POST[vote] == 0){
echo "You did not choose an answer.\n ";
die("<center><h2> Please go back and select one.");
}
$sqlstr= "select * from pollresponses where  responsekey = $_POST[vote]";
$getvotesnum = odbc_exec($conn, $sqlstr );
$votesnum = odbc_fetch_object($getvotesnum);
$today=date("Y-m-d");
$sqlstr= "select * from polls where pollkey=$votesnum->POLLKEY";
$getexpire=odbc_exec($conn, $sqlstr);
$expiredata=odbc_fetch_object($getexpire);
if ($expiredata->POLLENDDATE > "$today"){
$newvotesnum = $votesnum->VOTES+1;
$vote = odbc_exec($conn, "update pollresponses set 
        votes = $newvotesnum where responsekey = $_POST[vote]");
	$sql = "select * from pollresponses where pollkey = $_POST[pollkey] ";
	$sql .=	"order by responsekey";
$execute= odbc_exec($conn, $sql);
$rows = odbc_num_rows($execute);
echo "<center><h1>Poll Results</h1>";
echo "<h3>Question: $expiredata->QUESTION</h3>";
echo"<table name='table' border='0'>";
for ($i=1; $i<=$rows; $i++)
{
   //retrieve all the responses and their data, and display it
   $data = odbc_fetch_object($execute, $i);
   echo "<tr valign='center' rowspan='1' colspan='1' align='right'>";
   echo "<td valign = 'center' align = 'right' rowspan='1' colspan='1'>";
   echo "$data->RESPONSETEXT:</td><td>$data->VOTES votes";
   echo "</td></tr>";
}
echo "</table>";
echo "<br><a href='index.php'>Back To Poll Index</a>";
}
//if the poll has expired
else
{
echo "<center>Your vote was not processed because this poll has expired.";
echo "< br>To get to a list of polls that are not expired please click ";
echo "< a href='choosevote.php' class='primary'>here</a><br>";
echo "To view the results for this poll, please click ";
echo "<a href='pollresults.php?pollkey=$expiredata->POLLKEY'>here</a>.";
}
?>

<?php
$dbname = "PHPWEB";
$username = "phpweb";
$password = "phpweb1";
$conn = odbc_pconnect ( $dbname, $username, $password );
//in this part of the script, it will add the question, startdate, 
// and enddate to the database
//the @ sign before the function tells the function not to 
// output any error messages or warnings
$pollkeyexec = @odbc_exec($conn, "select * from polls order by pollkey");
$rows2 = @odbc_num_rows($pollkeyexec);
$keydata2 = @odbc_fetch_object($pollkeyexec, $rows2);
$pollkey = $keydata2->POLLKEY+1;
//these replace functions change the \' created by the php parser into a '' 
//that the database can read, and changes the \" from php to a " for the 
//database
$str1 = str_replace ("\'", "''", $_POST[question]);
$str2 = str_replace ('\"', '"', $str1);
$today = @date("Y-m-d");
$insertpoll = odbc_exec($conn,"insert into polls values 
             ($pollkey, '$str2', '$today', '$_POST[enddate]')");
                 
//in this part, the script will collect more information from the admin
                                                               
echo "<center><h1>Create a Poll</h1></center>";
echo "<center>";
echo "<form name = 'newpoll' action = 'poll-create3.php' method='post'>";
echo "<table border = '0'>";
                                                                                
for ($i=0; $i<$_POST[numresponses];$i++){
$i2 = $i+1;
echo "<tr valign='center' rowspan='1' colspan='1' align='right'>";
echo "<td valign = 'center' align = 'right' rowspan='1' colspan='1'>";
echo "Response $i2:</td><td align='left'>";
echo "<input type = 'text' name = 'response$i2' size = '70'>";
echo "<br></td></tr>";
}
echo "</table>";
echo "<input type='hidden' value='$_POST[numresponses]' name='numresponses'>";
echo "<input type = 'submit' value= 'Next Step'>";
echo "</form>";
?>

<?php
echo "<center><h1>Delete A Poll</h1>";
echo "<br><h3>Poll $_POST[pollkey] has been deleted</h3>";
$dbname = "PHPWEB";
$username = "phpweb";
$password = "phpweb1";
$conn = odbc_pconnect ( $dbname, $username, $password );
$sqlstr = "delete from pollresponses where pollkey=$_POST[pollkey]";
$query = odbc_exec($conn, $sqlstr);
$query2 = odbc_exec($conn, "delete from polls where pollkey=$_POST[pollkey]");
echo "<hr width='50%'>";
echo "<a href='poll-manage1.php'>Back to Poll Admin Tool</a>";
?>