[SOLVED] Getting SUM votes from my voting tables

[AdRock]

I have 2 tables for my voting script and I am having a nightmare getting the number of votes for each poll

 

The first table is pollquestions

`poll_id` int(11) NOT NULL auto_increment,
  `question` varchar(100) NOT NULL default '',
  `current` enum('0','1') NOT NULL default '0',
  `answer1` varchar(50) NOT NULL default '',
  `answer2` varchar(50) NOT NULL default '',
  `answer3` varchar(50) NOT NULL default '',
  `answer4` varchar(50) NOT NULL default '',
  `answer5` varchar(50) NOT NULL default '',
  PRIMARY KEY  (`poll_id`),
  UNIQUE KEY `id` (`poll_id`)

the second is pollanswers

 `answer_id` int(11) NOT NULL auto_increment,
  `poll_id` int(11) NOT NULL default '0',
  `answer` varchar(50) NOT NULL default '',
  `visitorIP` varchar(15) NOT NULL default '',
  PRIMARY KEY  (`answer_id`),
  UNIQUE KEY `Id` (`answer_id`)

 

I have no problem getting the votes into the answer table, I am having problems getting the votes out of the database.

 

I want to query both tables and get the SUM the answers from the answers table but linking them to the questions table

 

I know how to do joins but it says I have an error in my sql syntax.

 

I did an INNER JOIN on the poll_id

[toplay]

It would have been easier if you just listed your join query so we can help with the syntax issue.

 

I'm not sure what you mean exactly, but here is something to try:

 

select

          pq.poll_id

        , sum(if(pa.answer = pq.answer1, 1, 0)) as total_for_answer_1

        , sum(if(pa.answer = pq.answer2, 1, 0)) as total_for_answer_2

        , sum(if(pa.answer = pq.answer3, 1, 0)) as total_for_answer_3

        , sum(if(pa.answer = pq.answer4, 1, 0)) as total_for_answer_4

        , sum(if(pa.answer = pq.answer5, 1, 0)) as total_for_answer_5

 

from  pollquestions pq

join    pollanswers pa

using  (poll_id)

 

[AdRock]

Here is my query I have written.  It makes sense to me to get the result i'm looking for but i need to get the sume of each answer, so if 10 users voited for question 1, it would sum 10 for that etc.

 

I would have put them both in 1 table but then I would be able to limit users to one vote

 

sql = "SELECT pq.poll_id, pq.question, pq.answer1, pq.answer2, pq.answer3, pq.answer4, pq.answer5, pa.poll_id, pa.answer FROM pollquestions as pq INNER JOIN pollanswers as ON  pa pq.poll_id=pa.poll_id WHERE pq.poll_id = $pollid";

[Barand]

or maybe

 

SELECT pq.poll_id, pq.question, pa.answer, COUNT(*) as total

FROM pollquestions pq

JOIN pollanswers pa USING (poll_id)

GROUP BY pq.poll_id, pa.answer

[AdRock]

I have been working on it and it nearly works but I am having a problem with the mysql_data_seek to move back to the beginn of the tables

 

The error i'm getting is

Warning: mysql_data_seek(): supplied argument is not a valid MySQL result resource in d:\Apache\htdocs\copy of working\footer.inc.php on line 160

 

If i put the for loop in a while loop it works but echoing out the write percentages etc but it repeats all the data for every record which i did expect.

 

How can i move back to get the the answer titles to display with the percentages etc?

 

Here is my whole form

<form id="pollsform" name="pollsform" method="post" action="<?php echo $_SERVER['REQUEST_URI'] ?>">
<?php
	$db = & new MySQL($host,$dbUser,$dbPass,$dbName);

	if(isset($_POST['vote']) && !empty($_POST['poll'])) {
		$pollid = $_POST['pollid'];
		$answer = trim(check_input($_POST['poll']));
		$visitorIP = $_SERVER['REMOTE_ADDR'];

		$sql = "INSERT INTO pollanswers (answer_id, poll_id, answer, visitorIP) VALUES ('','$pollid','$answer','$visitorIP')";

		$result = $db->query($sql);

		if(!$result) {
			echo "Error getting results!";
		}
		else {
			$sql = "SELECT pq.poll_id, pq.question, pq.answer1, pq.answer2, pq.answer3, pq.answer4, pq.answer5, pa.poll_id, pa.answer FROM
				pollquestions as pq INNER JOIN pollanswers as pa ON pq.poll_id=pa.poll_id WHERE pq.poll_id = $pollid";

			$answertally[0] = 0;
			$answertally[1] = 0;
			$answertally[2] = 0;
			$answertally[3] = 0;
			$answertally[4] = 0;
			$answertotal = 0;

			$result = $db->query($sql);

			while($row = $result->fetch()) {
				switch($row["answer"]) {
					case $row["answer1"]:
						$answertally[0]++;
						break;

					case $row["answer2"]:
						$answertally[1]++;
						break;

					case $row["answer3"]:
						$answertally[2]++;
						break;

					case $row["answer4"]:
						$answertally[3]++;
						break;

					case $row["answer5"]:
						$answertally[4]++;
						break;
				}
			}

			for($i = 0; $i < sizeof($answertally); $i++) $answertotal += $answertally[$i];

			mysql_data_seek($result, 0);

			for($i = 1; $i <= 5; $i++) {
				if($row["answer$i"] != "") { ?>
					<table>
						<tr>
							<td bgcolor="#FFEFCE" style="padding-left:10" width="50%" colspan="1" height="21">
								<?php echo $row["answer$i"] . " [" . number_format(($answertally[$i-1] / $answertotal) * 100, 0, ".", '') . "%]" . "<br>"; ?>
							</td>
							<td bgcolor="#FFEFCE" style="padding-left:10" width="45%" colspan="1" height="21">
							</td>
							<td bgcolor="#FFEFCE" style="padding-left:10" width="5%" colspan="1" height="21">
							</td>
						</tr>
					</table>
				<?php } 
			}
		}
	}
	else {
		$sql="SELECT poll_id, question, current, answer1, answer2, answer3, answer4, answer5 FROM pollquestions WHERE current='1' LIMIT 1";

		// Perform a query getting back a MySQLResult object
		$result = $db->query($sql);

		// Iterate through the results
		while ($row = $result->fetch()) {
			echo ( '<h3>'.$row['question'].'</h3>' );	
			echo ( '<label for="question1">'.$row['answer1'].'</label><input type="radio" id="question1" name="poll" value="'.$row['answer1'].'" /><br />' );
			echo ( '<label for="question2">'.$row['answer2'].'</label><input type="radio" id="question2" name="poll" value="'.$row['answer2'].'" /><br />' );
			echo ( '<label for="question3">'.$row['answer3'].'</label><input type="radio" id="question3" name="poll" value="'.$row['answer3'].'" /><br />' );
			echo ( '<label for="question4">'.$row['answer4'].'</label><input type="radio" id="question4" name="poll" value="'.$row['answer4'].'" /><br />' );
			echo ( '<label for="question5">'.$row['answer5'].'</label><input type="radio" id="question5" name="poll" value="'.$row['answer5'].'" /><br />' );
			echo ( '<input type="hidden" name="pollid" value="'.$row['poll_id'].'" />' );
		}
	?>
	<input type="submit" id="vote" name="vote" value="" class="vote-button" />
<?php } ?>
</form>

 

Any help getting the results to echo properly would help me finish my site 

[fenway]

Which query is generating that error?

[AdRock]

This one

 

$sql = "SELECT pq.poll_id, pq.question, pq.answer1, pq.answer2, pq.answer3, pq.answer4, pq.answer5, pa.poll_id, pa.answer FROM

pollquestions as pq INNER JOIN pollanswers as pa ON pq.poll_id=pa.poll_id WHERE pq.poll_id = $pollid";

 

I've got the percentages to echo out and create a bar graph but not the answers

[fenway]

Well, you're not catching mysql_error() after that query... echo $sql.

[AdRock]

I echoed out $sql and i got the query i created.

 

Here is my database class if it helps

 

<?php
/**
* @package SPLIB
* @version $Id: MySQL.php,v 1.1 2003/12/12 08:06:07 kevin Exp $
*/
/**
* MySQL Database Connection Class
* @access public
* @package SPLIB
*/
class MySQL {
    /**
    * MySQL server hostname
    * @access private
    * @var string
    */
    var $host;

    /**
    * MySQL username
    * @access private
    * @var string
    */
    var $dbUser;

    /**
    * MySQL user's password
    * @access private
    * @var string
    */
    var $dbPass;

    /**
    * Name of database to use
    * @access private
    * @var string
    */
    var $dbName;

    /**
    * MySQL Resource link identifier stored here
    * @access private
    * @var string
    */
    var $dbConn;

    /**
    * Stores error messages for connection errors
    * @access private
    * @var string
    */
    var $connectError;

    /**
    * MySQL constructor
    * @param string host (MySQL server hostname)
    * @param string dbUser (MySQL User Name)
    * @param string dbPass (MySQL User Password)
    * @param string dbName (Database to select)
    * @access public
    */
    function MySQL ($host,$dbUser,$dbPass,$dbName) {
        $this->host=$host;
        $this->dbUser=$dbUser;
        $this->dbPass=$dbPass;
        $this->dbName=$dbName;
        $this->connectToDb();
    }

    /**
    * Establishes connection to MySQL and selects a database
    * @return void
    * @access private
    */
    function connectToDb () {
        // Make connection to MySQL server
        if (!$this->dbConn = @mysql_connect($this->host,
                                      $this->dbUser,
                                      $this->dbPass)) {
            trigger_error('Could not connect to server');
            $this->connectError=true;
        // Select database
        } else if ( !@mysql_select_db($this->dbName,$this->dbConn) ) {
            trigger_error('Could not select database');
            $this->connectError=true;
        }
    }

    /**
    * Checks for MySQL errors
    * @return boolean
    * @access public
    */
    function isError () {
        if ( $this->connectError )
            return true;
        $error=mysql_error ($this->dbConn);
        if ( empty ($error) )
            return false;
        else
            return true;
    }

    /**
    * Returns an instance of MySQLResult to fetch rows with
    * @param $sql string the database query to run
    * @return MySQLResult
    * @access public
    */
    function & query($sql) {
        if (!$queryResource=mysql_query($sql,$this->dbConn))
            trigger_error ('Query failed: '.mysql_error($this->dbConn).
                           ' SQL: '.$sql);
        return new MySQLResult($this,$queryResource);
    }
}

/**
* MySQLResult Data Fetching Class
* @access public
* @package SPLIB
*/
class MySQLResult {
    /**
    * Instance of MySQL providing database connection
    * @access private
    * @var MySQL
    */
    var $mysql;

    /**
    * Query resource
    * @access private
    * @var resource
    */
    var $query;

    /**
    * MySQLResult constructor
    * @param object mysql   (instance of MySQL class)
    * @param resource query (MySQL query resource)
    * @access public
    */
    function MySQLResult(& $mysql,$query) {
        $this->mysql=& $mysql;
        $this->query=$query;
    }

    /**
    * Fetches a row from the result
    * @return array
    * @access public
    */
    function fetch () {
        if ( $row=mysql_fetch_array($this->query,MYSQL_ASSOC) ) {
            return $row;
        } else if ( $this->size() > 0 ) {
            mysql_data_seek($this->query,0);
            return false;
        } else {
            return false;
        }
    }

    /**
    * Returns the number of rows selected
    * @return int
    * @access public
    */
    function size () {
        return mysql_num_rows($this->query);
    }

function fetchrow () {
	return mysql_fetch_row($this->query);
}

    /**
    * Returns the ID of the last row inserted
    * @return int
    * @access public
    */
    function insertID () {
        return mysql_insert_id($this->mysql->dbConn);
    }
    
    /**
    * Checks for MySQL errors
    * @return boolean
    * @access public
    */
    function isError () {
        return $this->mysql->isError();
    }
}
?>

[fenway]

I echoed out $sql and i got the query i created.

Where is it?

[AdRock]

When i echo out $sql i get this echoed out

 

SELECT pq.poll_id, pq.question, pq.answer1, pq.answer2, pq.answer3, pq.answer4, pq.answer5, pa.poll_id, pa.answer FROM pollquestions as pq INNER JOIN pollanswers as pa ON pq.poll_id=pa.poll_id WHERE pq.poll_id = $pollid

[fenway]

Why is your variable not being interpolated? Isn't it in double-quotes?

[AdRock]

Yeah it is....

 

I thought it was somethng to do with this function

 

function fetch () {
        if ( $row=mysql_fetch_array($this->query,MYSQL_ASSOC) ) {
            return $row;
        } else if ( $this->size() > 0 ) {
            mysql_data_seek($this->query,0);
            return false;
        } else {
            return false;
        }
    }

[fenway]

I have no idea what that's all about -- and it won't have anything to do with your other string.

[AdRock]

I have it working now

 

Thanks for all your help but it turned out i didn't need the mysql_data_seek

 

found a workaround but it works...may look to optimize the code later