Selecting Answers For my POll Form

[Xtremer360]

What I'm trying to do is have it select the answers that are associated to that poll so that I can make a poll form out of this.

 

function getpoll($dbc) {
    $query = "
    SELECT
        polls.ID,
        polls.question,
        polls.totalVotes,
        (SELECT pollAnswers.ID, pollAnswers.answer FROM pollAnswers WHERE pollAnswers.pollID = polls.ID)
    FROM
        polls
    INNER JOIN pollAnswers
        ON polls.ID = pollAnswers.pollID
    WHERE
        polls.statusID = '1'
    ORDER BY
        polls.ID DESC
    LIMIT 
        1";
    $result = mysqli_query($dbc, $query);
    $numrows = mysqli_num_rows($result);
    if ($numrows > "0") {
        
    } else {
      print "<p class=none>There are currently no open polls.";  
    }
}

[Xtremer360]

Updated code also with the same question as above.

 

function getpoll($dbc) {
    $query = "
    SELECT
        polls.ID as pollID,
        polls.question,
        polls.totalVotes,
        (SELECT pollAnswers.ID, pollAnswers.answer FROM pollAnswers WHERE pollAnswers.pollID = polls.ID)
    FROM
        polls
    INNER JOIN pollAnswers
        ON polls.ID = pollAnswers.pollID
    WHERE
        polls.statusID = '1'
    ORDER BY
        polls.ID DESC
    LIMIT 
        1";
    $result = mysqli_query($dbc, $query);
    $numrows = mysqli_num_rows($result);
    if ($numrows > "0") {
        while ($row = mysqli_fetch_array($result)) {
		$fieldarray=array('pollID','question','totalVotes');
		foreach ($fieldarray as $fieldlabel) {
			if (isset($row[$fieldlabel])) { 
				$$fieldlabel=$row[$fieldlabel];
				$$fieldlabel=cleanquerydata($$fieldlabel);
			}
		}
            print "<form action=efedmanager/processes/poll.php method=post name=poll id=poll>";
            print "<fieldset>";
            print "<legend>".$question."</legend>";
            foreach ($answer as $answer) {
                print "<label>";
                print "<input type=radio name=Poll value=".$answer." id=".$answerID." />".$answer."";
                print "</label>";
            }
            print "<input type=hidden name=pollID value=".$pollID." />";
            print "<input type=submit name=submit id=submit value=Vote />";
            print "</fieldset>";
            print "</form>"; 
        }
    } else {
      print "<p class=none>There are currently no open polls.";  
    }
}

[Xtremer360]

Any ideas?

[Xtremer360]

?

[Xtremer360]

I'm hoping I can get some help with this issue.

[DavidAM]

This SQL is not valid. You cannot have a subquery in the SELECT phrase that returns multiple rows.

    SELECT
        polls.ID as pollID,
        polls.question,
        polls.totalVotes,
        (SELECT pollAnswers.ID, pollAnswers.answer FROM pollAnswers WHERE pollAnswers.pollID = polls.ID)
    FROM
        polls
    INNER JOIN pollAnswers
        ON polls.ID = pollAnswers.pollID
    WHERE
        polls.statusID = '1'
    ORDER BY
        polls.ID DESC
    LIMIT 
        1

 

Since you have LIMIT 1, the WHILE loop is useless, you are only going to have one row to process.

 

I think, in this case, you will have to do two queries. The first query will get the Poll info and the second will get the answer list. Something along these lines:

 

Note: I also rearranged the FORM contents a bit, some of the elements were mixed in with the FIELDSET, and should not have been.

 

function getpoll($dbc) {
$query = "SELECT polls.ID as pollID, polls.question, polls.totalVotes,
		FROM polls
		WHERE polls.statusID = '1'
		ORDER BY polls.ID DESC
		LIMIT 1";
$result = mysqli_query($dbc, $query);
if ( ($result) and (mysqli_num_rows($result) > 0) ) {
	$row = mysqli_fetch_array($result);
	$pollID = $row['pollID'];
	$question = $row['question'];
	$totalVotes = $row['totalVotes'];

	$query = "SELECT pollAnswers.ID, pollAnswers.answer 
			FROM pollAnswers 
			WHERE pollAnswers.pollID = $pollID";

	print "<form action=efedmanager/processes/poll.php method=post name=poll id=poll>";
	print "<input type=hidden name=pollID value=".$pollID." />";
	print "<fieldset>";
	print "<legend>".$question."</legend>";
    
	$result = mysqli_query($dbc, $query);
	if ( ($result) and (mysqli_num_rows($result) > 0) ) {
		while ($row = mysqli_fetch_array($result)) {
		        print "<label>";
		        print "<input type=radio name=Poll value=" . 
		        		$row['answer'] . 
		        		" id=" . $row['ID'] .
		        		" />" . $row['answer'];
		        print "</label>";
		}
	}
	print "</fieldset>";
	print "<input type=submit name=submit id=submit value=Vote />";
	print "</form>"; 
} else {
  print "<p class=none>There are currently no open polls.";  
}
}

 

This code is not tested and may contain syntax or logic errors . It is provided as a example of an approach that should work for the stated problem.

 

 

[Xtremer360]

Thank you for responding. The code you suggested worked great but I had to alter a few things because of how I changed my database but this is what I have now and for some reason its not displaying the text of the (poll_answer). Not sure why.

 

function getpoll($dbc) {
    $query = "
    SELECT
        polls.id as poll_id,
        polls.poll_question,
        polls.total_votes
    FROM
        polls
    WHERE
        polls.status_id = '1'
    ORDER BY
        polls.id DESC
    LIMIT 
        1";
    $result = mysqli_query($dbc, $query);
    if ( ($result) and (mysqli_num_rows($result) > 0) ) {
        $row = mysqli_fetch_array($result);
        $poll_id = $row['poll_id'];
        $poll_question = $row['poll_question'];
        $total_votes = $row['total_votes'];                
        $query = "
        SELECT
            poll_answers.id as answer_id,
            poll_answers.poll_answer
        FROM
            poll_answers
        WHERE
            poll_answers.poll_id = '".$poll_id."'";
        print "<form action=efedmanager/processes/poll.php method=post name=poll id=poll>";
        print "<fieldset>";
        print "<legend>".$poll_question."</legend>";
        $result = mysqli_query($dbc, $query);
        $num_rows = mysqli_num_rows($result);
        if ( ($result) and (mysqli_num_rows($result) > 0) ) {
            while ($row = mysqli_fetch_array($result)) {
                print "<label>";
                print "<input type=radio name=Poll value=".$poll_answer." id=".$answer_id." />".$poll_answer."";
                print "</label>";
            }
         }
        print "<input type=hidden name=poll_id value=".$poll_id." />";
        print "<input type=submit name=submit id=submit value=Vote />";
        print "</fieldset>";
        print "</form>"; 
    } else {
      print "<p class=none>There are currently no open polls.";  
    }
}

[DavidAM]

The data from the query is stored in $row. You have to reference it from there or copy it to another variable:

 

print "<input type=radio name=Poll value=" . $row['poll_answer'] . " id=" . $row['answer_id'] . " />" . $row['poll_answer'] . "";