Keeping the results of a poll in a mysql table..

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
Trip1
Forum Newbie
Posts: 23
Joined: Sat Jan 25, 2003 7:36 pm

Keeping the results of a poll in a mysql table..

Post by Trip1 »

What im trying to code is a poll, that keeps the number of votes for each option in a mysql table.. ive coded something, and what its supposed to do is take only the first line of the table(limit 1, DESC). in the table i have 6 fields.. 5 answers and one pollid(auto inc) so i can have something to sort it by. What its supposed to do is if the person voted for answer1, then whatever is in answer1 in the mysql table and add 1 to it, put it back in the table, also taking answer2-5 and submitting those to the table to, so that i dont lose their values when someone votes for something. Heres the code ive got so far:

Code: Select all

if($answer1 == V1){
	$query = "SELECT answer1 FROM smecs_poll_results ORDER BY pollid DESC limit 0,1";
	$result = mysql_query($query);
	$answerup = mysql_fetch_row($result);
	$answerup = $answerup[0];
	$answerup = $answerup++;
	
	$queryfortwo = "SELECT answer2 FROM smecs_poll_results ORDER BY pollid DESC limit 0,1";
	$resultfortwo = mysql_query($queryfortwo);
	$answerfortwo = mysql_fetch_row($resultfortwo);
	$answerfortwo = $answerfortwo[0];

	$queryforthree = "SELECT answer3 FROM smecs_poll_results ORDER BY pollid DESC limit 0,1";
	$resultforthree = mysql_query($queryforthree);
	$answerforthree = mysql_fetch_row($resultforthree);
	$answerforthree = $answerfortwo[0];	
	
	$queryforfour = "SELECT answer4 FROM smecs_poll_results ORDER BY pollid DESC limit 0,1";
	$resultforfour = mysql_query($queryforfour);
	$answerforfour = mysql_fetch_row($resultforfour);
	$answerforfour = $answerforfour[0];	
	
	$queryforfive = "SELECT answer5 FROM smecs_poll_results ORDER BY pollid DESC limit 0,1";
	$resultforfive = mysql_query($queryforfive);
	$answerforfive = mysql_fetch_row($resultforfive);
	$answerforfive = $answerforfive[0];	
	
	$query2 = "INSERT INTO smecs_poll_results (answer1, answer2, answer3, answer4, answer5) VALUES('$answerup', '$answerfortwo', '$answerforthree', '$answerforfour', '$answerforfive')";
	
	mysql_query($query2);
	
}

if($answer1 == V2){
	$query = "SELECT answer1 FROM smecs_poll_results ORDER BY pollid DESC limit 0,1";
	$result = mysql_query($query);
	$answerup = mysql_fetch_row($result);
	$answerup = $answerup[0];
	
	
	$queryfortwo = "SELECT answer2 FROM smecs_poll_results ORDER BY pollid DESC limit 0,1";
	$resultfortwo = mysql_query($queryfortwo);
	$answerfortwo = mysql_fetch_row($resultfortwo);
	$answerfortwo = $answerfortwo[0];
	$answerfortwo = $answerfortwo++;

	$queryforthree = "SELECT answer3 FROM smecs_poll_results ORDER BY pollid DESC limit 0,1";
	$resultforthree = mysql_query($queryforthree);
	$answerforthree = mysql_fetch_row($resultforthree);
	$answerforthree = $answerfortwo[0];	
	
	$queryforfour = "SELECT answer4 FROM smecs_poll_results ORDER BY pollid DESC limit 0,1";
	$resultforfour = mysql_query($queryforfour);
	$answerforfour = mysql_fetch_row($resultforfour);
	$answerforfour = $answerforfour[0];	
	
	$queryforfive = "SELECT answer5 FROM smecs_poll_results ORDER BY pollid DESC limit 0,1";
	$resultforfive = mysql_query($queryforfive);
	$answerforfive = mysql_fetch_row($resultforfive);
	$answerforfive = $answerforfive[0];
	
	$query2 = "INSERT INTO smecs_poll_results (answer1, answer2, answer3, answer4, answer5) VALUES('$answerup', '$answerfortwo', '$answerforthree', '$answerforfour', '$answerforfive')";
	
	mysql_query($query2);	
	
	
}

if($answer1 == V3){
        $query = "SELECT answer1 FROM smecs_poll_results ORDER BY pollid DESC limit 0,1";
	$result = mysql_query($query);
	$answerup = mysql_fetch_row($result);
	$answerup = $answerup[0];
	
	
	$queryfortwo = "SELECT answer2 FROM smecs_poll_results ORDER BY pollid DESC limit 0,1";
	$resultfortwo = mysql_query($queryfortwo);
	$answerfortwo = mysql_fetch_row($resultfortwo);
	$answerfortwo = $answerfortwo[0];
	

	$queryforthree = "SELECT answer3 FROM smecs_poll_results ORDER BY pollid DESC limit 0,1";
	$resultforthree = mysql_query($queryforthree);
	$answerforthree = mysql_fetch_row($resultforthree);
	$answerforthree = $answerforthree[0];
	$answerforthree = $answerforthree++;	
	
	$queryforfour = "SELECT answer4 FROM smecs_poll_results ORDER BY pollid DESC limit 0,1";
	$resultforfour = mysql_query($queryforfour);
	$answerforfour = mysql_fetch_row($resultforfour);
	$answerforfour = $answerforfour[0];	
	
	$queryforfive = "SELECT answer5 FROM smecs_poll_results ORDER BY pollid DESC limit 0,1";
	$resultforfive = mysql_query($queryforfive);
	$answerforfive = mysql_fetch_row($resultforfive);
	$answerforfive = $answerforfive[0];
	
	$query2 = "INSERT INTO smecs_poll_results (answer1, answer2, answer3, answer4, answer5) VALUES('$answerup', '$answerfortwo', '$answerforthree', '$answerforfour', '$answerforfive')";
	
	mysql_query($query2);	
}

if($answer1 == V4){
	  $query = "SELECT answer1 FROM smecs_poll_results ORDER BY pollid DESC limit 0,1";
	$result = mysql_query($query);
	$answerup = mysql_fetch_row($result);
	$answerup = $answerup[0];
	
	
	$queryfortwo = "SELECT answer2 FROM smecs_poll_results ORDER BY pollid DESC limit 0,1";
	$resultfortwo = mysql_query($queryfortwo);
	$answerfortwo = mysql_fetch_row($resultfortwo);
	$answerfortwo = $answerfortwo[0];
	

	$queryforthree = "SELECT answer3 FROM smecs_poll_results ORDER BY pollid DESC limit 0,1";
	$resultforthree = mysql_query($queryforthree);
	$answerforthree = mysql_fetch_row($resultforthree);
	$answerforthree = $answerfortwo[0];
		
	
	$queryforfour = "SELECT answer4 FROM smecs_poll_results ORDER BY pollid DESC limit 0,1";
	$resultforfour = mysql_query($queryforfour);
	$answerforfour = mysql_fetch_row($resultforfour);
	$answerforfour = $answerforfour[0];
	$answerforfour = $answerforfour++;	
	
	$queryforfive = "SELECT answer5 FROM smecs_poll_results ORDER BY pollid DESC limit 0,1";
	$resultforfive = mysql_query($queryforfive);
	$answerforfive = mysql_fetch_row($resultforfive);
	$answerforfive = $answerforfive[0];
	
	$query2 = "INSERT INTO smecs_poll_results (answer1, answer2, answer3, answer4, answer5) VALUES('$answerup', '$answerfortwo', '$answerforthree', '$answerforfour', '$answerforfive')";
	
	mysql_query($query2);	
	
}


if($answer1 == V5){
	  $query = "SELECT answer1 FROM smecs_poll_results ORDER BY pollid DESC limit 0,1";
	$result = mysql_query($query);
	$answerup = mysql_fetch_row($result);
	$answerup = $answerup[0];
	
	
	$queryfortwo = "SELECT answer2 FROM smecs_poll_results ORDER BY pollid DESC limit 0,1";
	$resultfortwo = mysql_query($queryfortwo);
	$answerfortwo = mysql_fetch_row($resultfortwo);
	$answerfortwo = $answerfortwo[0];
	

	$queryforthree = "SELECT answer3 FROM smecs_poll_results ORDER BY pollid DESC limit 0,1";
	$resultforthree = mysql_query($queryforthree);
	$answerforthree = mysql_fetch_row($resultforthree);
	$answerforthree = $answerfortwo[0];
		
	
	$queryforfour = "SELECT answer4 FROM smecs_poll_results ORDER BY pollid DESC limit 0,1";
	$resultforfour = mysql_query($queryforfour);
	$answerforfour = mysql_fetch_row($resultforfour);
	$answerforfour = $answerforfour[0];
		
	
	$queryforfive = "SELECT answer5 FROM smecs_poll_results ORDER BY pollid DESC limit 0,1";
	$resultforfive = mysql_query($queryforfive);
	$answerforfive = mysql_fetch_row($resultforfive);
	$answerforfive = $answerforfive[0];
	$answerforfive = $answerforfive++;
	
	$query2 = "INSERT INTO smecs_poll_results (answer1, answer2, answer3, answer4, answer5) VALUES('$answerup', '$answerfortwo', '$answerforthree', '$answerforfour', '$answerforfive')";
	
	mysql_query($query2);	
	
}

?>
This code is not working- when someone votes for answer 1, it reads the results back to the person like so:
Answer1:1
Answer2:
Answer3:
Answer4:
Answer5:

even if answer1 had already had a vote in it.
This is the code that im using to read the results back to the person:

Code: Select all

$query = "SELECT answer1 FROM smecs_poll_results ORDER BY pollid DESC limit 0,1";
$resultanswer1 = mysql_query($query);
$resultsone = mysql_fetch_row($resultanswer1);
$resultsone = $resultsone[0];

$query = "SELECT answer2 FROM smecs_poll_results ORDER BY pollid DESC limit 0,1";
$resultanswer2 = mysql_query($query);
$resultstwo = mysql_fetch_row($resultanswer2);
$resultstwo = $resultstwo[0];

$query = "SELECT answer3 FROM smecs_poll_results ORDER BY pollid DESC limit 0,1";
$resultanswer3 = mysql_query($query);
$resultsthree = mysql_fetch_row($resultanswer3);
$resultsthree = $resultsthree[0];

$query = "SELECT answer4 FROM smecs_poll_results ORDER BY pollid DESC limit 0,1";
$resultanswer4 = mysql_query($query);
$resultsfour = mysql_fetch_row($resultanswer4);
$resultsfour = $resultsfour[0];				

$query = "SELECT answer5 FROM smecs_poll_results ORDER BY pollid DESC limit 0,1";
$resultanswer5 = mysql_query($query);
$resultsfive = mysql_fetch_row($resultanswer5);
$resultsfive = $resultsfive[0];

$result3 = "SELECT * FROM smecs_poll ORDER BY pollid DESC limit 0,1";
$result2 = mysql_query($result3);

{
	while($rowanswers=mysql_fetch_array($result2)) {
print "<b><center>".$rowanswers["answer1"]."</b>:".$resultsone;
print "<b><center>".$rowanswers["answer2"]."</b>:".$resultstwo;
print "<b><center>".$rowanswers["answer3"]."</b>:".$resultsthree;
print "<b><center>".$rowanswers["answer4"]."</b>:".$resultsfour;
print "<b><center>".$rowanswers["answer5"]."</b>:".$resultsfive;
	
	
	
}
Any help is greatly appreciated, thanks in advance!
Jim
Forum Contributor
Posts: 238
Joined: Fri Apr 19, 2002 5:26 am
Location: Near Austin, Texas

Post by Jim »

Why is there so much code for such a simple job? If you're just trying to update results in a single table, do it like this:

Code: Select all

// If the user votes option 1, for example...
//Select the current number of votes for option 1 ...
$sql = "select option_1 from poll_table where pollid = '1'";
$act = mysql_query($sql);

          while ($info = mysql_fetch_array($act)) {

         $option1 = $info['option_1']; //This value is the current votes for option 1
       
          }

$newoption = $option1 + 1; //Add another vote to option 1

$sql = "update table poll_table set option_1 = '$newoption' where pollid = '1'"; // This updates the table
$act = mysql_query($sql);
Pretty simple. This code pulls out all the votes for option number one currently on the table, adds one to it and puts it back in the table. You'll have to edit this code, of course, or you'll only have information in your option_1 table ;)
Jim
Forum Contributor
Posts: 238
Joined: Fri Apr 19, 2002 5:26 am
Location: Near Austin, Texas

Post by Jim »

And to print those answers... it only takes one select statement to select all the answers you need :)

Code: Select all

$sql = "select answer_1, answer_2, answer_3, answer_5, answer_5 from poll_table where pollid = '1'";
$act = mysql_query($sql);

         while ($info = mysql_fetch_array($act)) {

          $answer1 = $info['answer_1'];
          $answer2 = $info['answer_2'];
          $answer3 = $info['answer_3'];
          $answer4 = $info['answer_4'];
          $answer5 = $info['answer_5'];


        echo "<b>Option 1:</b>$answer1<BR>";
        echo "<b>Option 2:</b>$answer2<BR>";
        echo "<b>Option 3:</b>$answer3<BR>";
        echo "<b>Option 4:</b>$answer4<BR>";
        echo "<b>Option 5:</b>$answer5<BR>";


          }
Trip1
Forum Newbie
Posts: 23
Joined: Sat Jan 25, 2003 7:36 pm

RE

Post by Trip1 »

thanks for the reply, but im still having some trouble. I know this script is not adding anything to the table:

Code: Select all

if($answer1 == V1){
	$sql = "SELECT votes1 FROM smecs_poll ORDER BY pollid DESC limit 0,1"; 
	$act = mysql_query($sql); 
while ($info = mysql_fetch_array($act)) { 

         $option1 = $info['votes1']; //This value is the current votes for option 1 
        
          } 

$newoption = $option1 + 1; //Add another vote to option 1 

$sql = "UPDATE TABLE smecs_poll SET votes1 = '$newoption'"; // This updates the table 
mysql_query($sql); 


	
}
I can't find whats wrong with it. If i add data to the table manually it will showup on the page correctly, but it wont add anything to it via php with that script above.. any help is appreciated!
User avatar
evilmonkey
Forum Regular
Posts: 823
Joined: Sun Oct 06, 2002 1:24 pm
Location: Toronto, Canada

Post by evilmonkey »

If your script is not addning anything to a database, the problem is most likely very simple. If one thing in your code is wrong (i.e. misspelled), nothing will be added to the database. For example:

Code: Select all

$query "INSERT INTO table (joe, bob evilmokey) VALUES ('$joe', '$bob', '$evilmonkey')";
$result=mysql_query ($query, $db)
Notice I misspelled evilmonkey. In this code, nothing will be added to the database. Go back and make sure all the spellings are proper.

Cheers!

Evil Monkey.
Trip1
Forum Newbie
Posts: 23
Joined: Sat Jan 25, 2003 7:36 pm

Post by Trip1 »

im pretty sure i didn't misspell anything.. but maybe im looking with my nose.. here is my mysql table (called smecs_poll)

Field Type
poll longtext
answer1 varchar(50)
answer2 varchar(50)
answer3 varchar(50)
answer4 varchar(50)
answer5 varchar(50)
pollid bigint(20) (AUTO INCREMENT)
votes1 bigint(20)
votes2 bigint(20)
votes3 bigint(20)
votes4 bigint(20)
votes5 bigint(20)

That's my table.. the poll field is the text for the actual poll, the answer1-5 is for the text of the answers, and the votes1-5 contains the # of votes for each answer.

And heres my code that im using to insert data into the field.

Code: Select all

if($answer1 == V2){
	$sql = "SELECT votes2 FROM smecs_poll ORDER BY pollid DESC limit 0,1";
	$act = mysql_query($sql); 
while ($info = mysql_fetch_array($act)) { 

         $option1 = $info['votes2']; //This value is the current votes for option 1 
        
          } 

$newoption = $option1 + 1; //Add another vote to option 1 

$sql = "update table smecs_poll set votes2 = '$newoption' where pollid = '1'"; // This updates the table 
mysql_query($sql);
Post Reply