Dynamic PHP/MySQL Question

[centenial]

Hi,

I've been working on this poll script. I want the user to be able to specify how many poll answers there will be, and I did this using a while loop (see below for code example):

[code]
$answers = $_POST['answers'];
$i = 1;

while ($i <= $answers) {
  echo "<tr>\n<td>Answer $i</td>\n<td><input type=\"text\" name=\"answer$i\">\n</tr>\n\n";
$i++;

<input type=\"hidden\" name=\"answers\" value=\"$answers\">
[/code]

This worked out great, and I was even able to figure out how to insert this into the MySQL database (This was tricky for me because I had to make the SQL statement dynamic):

[code]


for ($i=1; $i <= $_POST["answers"]; $i++)
{
include 'db.php';
$sql = "INSERT INTO poll_answers (id, poll_id, answer) VALUES ('', '$poll_id', '".$_POST['answer'.$i]."')";
$res = mysql_query($sql,$conn) or die(mysql_error());
}
[/code]

Now, here's where my problem this. I'm trying to create the results page for the poll, and I don't know how to generate the results because I don't know how many answers they would enter for the poll. I tried doing this, but it didn't seem to work:

[code]
    $id = $_GET['id'];
    include 'db.php';
    $check = mysql_query("SELECT * FROM poll_answers WHERE poll_id = '$id'") or die(mysql_error());
    $total = mysql_num_rows($check);
    $percentage = 100 / $total;

for ($i=1; $i <= $total; $i++)
{

    $id = $_GET['id'];
    include 'db.php';
    $check2 = mysql_query("SELECT * FROM poll_votes WHERE poll_id = '$id' and answer_id = '$i'") or die(mysql_error());
    $a_count = mysql_num_rows($check2);
    $a_final = $a_count * $percentage;

}

$id = $_GET['id'];
include 'db.php';
$sql = "SELECT * FROM poll_answers WHERE poll_id = '$id'";
$res = mysql_query($sql,$conn) or die(mysql_error());

    while ($newArray = mysql_fetch_array($res)) {
       // Convert the Result set into Variables
       $id = $newArray['id'];
       $poll_id = $newArray['poll_id'];
       $answer = $newArray['answer'];

    echo "<tr>";
    echo "<td>$answer</td>";
    echo "<td>$a_final</td>";
    echo "<td>$a_count</td>";
    echo "</tr>";
}
[/code]

I've wasted a couple days tearing my hair out over this. Please help?

Here are my MySQL tables:

[code]
CREATE TABLE master_poll (
id smallint not null primary key auto_increment,
question text,
status varchar(15),
startdate text
);

CREATE TABLE poll_answers (
id smallint not null primary key auto_increment,
poll_id varchar(5),
answer varchar(100)
);

CREATE TABLE poll_votes (
id smallint not null primary key auto_increment,
poll_id varchar(5),
answer_id varchar(5),
ipaddress varchar(25),
time text
);
[/code]

[craygo]

Try this out. You only have to include the db connection and the $id once. You will not be able to do the loop with the for statement, because as you add answers the answer id's will start from the next number. So you will have to put them into and array and go from there

[code]<?
// Get id to use
$id = $_GET['id'];
// Make datebase connection below

// Start table for results
echo "<table width=200>";

// Get total votes
    $votes = mysql_query("SELECT * FROM poll_votes WHERE poll_id = '$id'") or die(mysql_error());
    $total = mysql_num_rows($votes);

// Get Available answers
    $an_ids = array();
    $ans_check = mysql_query("SELECT master_poll.question AS question, poll_answers.id AS an_id FROM master_poll, poll_answers
    WHERE master_poll.id = poll_answers.poll_id AND poll_answers.poll_id = '$id'") or die (mysql_error());
    $answers = mysql_num_rows($ans_check);
// Put answer id's into array
    while($r = mysql_fetch_array($ans_check)){
        $an_ids[] = $r['an_id'];
    }
// Echo out the question
    echo "<tr><td colspan=3 align=center>".$r['question']."</td></tr>";

// query each answer from array id's
foreach($an_ids as $v)
{
    $check2 = mysql_query("SELECT poll_answers.id AS ans_id, poll_answers.answer AS answer, master_poll.id AS poll_id, Count(poll_votes.answer_id) AS results
    FROM
    (master_poll INNER JOIN poll_answers ON master_poll.id = poll_answers.poll_id)
    LEFT JOIN poll_votes ON poll_answers.id = poll_votes.answer_id
    WHERE master_poll.id = '$id' and poll_answers.id = '$v'
    GROUP BY master_poll.question, poll_answers.answer") or die(mysql_error());
      $r2 = mysql_fetch_assoc($check2);
    $a_count = $r2['results'];
    $a_final = (($r2['results'] / $total)*100);
    echo "<tr>
          <td width=50>".$r2['answer']."</td>
          <td width=60>$a_count</td>
          <td width=60>".number_format($a_final, 0)."%</td>
          </tr>";
}
echo "</table>";
?>[/code]

Ray

[centenial]

[!--quoteo(post=370612:date=May 2 2006, 01:20 PM:name=craygo)--][div class=\'quotetop\']QUOTE(craygo @ May 2 2006, 01:20 PM) [snapback]370612[/snapback][/div][div class=\'quotemain\'][!--quotec--]
Try this out. You only have to include the db connection and the $id once. You will not be able to do the loop with the for statement, because as you add answers the answer id's will start from the next number. So you will have to put them into and array and go from there

[code]<?
// Get id to use
$id = $_GET['id'];
// Make datebase connection below

// Start table for results
echo "<table width=200>";

// Get total votes
    $votes = mysql_query("SELECT * FROM poll_votes WHERE poll_id = '$id'") or die(mysql_error());
    $total = mysql_num_rows($votes);

// Get Available answers
    $an_ids = array();
    $ans_check = mysql_query("SELECT master_poll.question AS question, poll_answers.id AS an_id FROM master_poll, poll_answers
    WHERE master_poll.id = poll_answers.poll_id AND poll_answers.poll_id = '$id'") or die (mysql_error());
    $answers = mysql_num_rows($ans_check);
// Put answer id's into array
    while($r = mysql_fetch_array($ans_check)){
        $an_ids[] = $r['an_id'];
    }
// Echo out the question
    echo "<tr><td colspan=3 align=center>".$r['question']."</td></tr>";

// query each answer from array id's
foreach($an_ids as $v)
{
    $check2 = mysql_query("SELECT poll_answers.id AS ans_id, poll_answers.answer AS answer, master_poll.id AS poll_id, Count(poll_votes.answer_id) AS results
    FROM
    (master_poll INNER JOIN poll_answers ON master_poll.id = poll_answers.poll_id)
    LEFT JOIN poll_votes ON poll_answers.id = poll_votes.answer_id
    WHERE master_poll.id = '$id' and poll_answers.id = '$v'
    GROUP BY master_poll.question, poll_answers.answer") or die(mysql_error());
      $r2 = mysql_fetch_assoc($check2);
    $a_count = $r2['results'];
    $a_final = (($r2['results'] / $total)*100);
    echo "<tr>
          <td width=50>".$r2['answer']."</td>
          <td width=60>$a_count</td>
          <td width=60>".number_format($a_final, 0)."%</td>
          </tr>";
}
echo "</table>";
?>[/code]

Ray
[/quote]
Wow, you are the best! Thanks!