How would I achieve a poll script?

[MHaris]

Hello,

I want to write a poll script based on certain candidates.

My table structure is as follow:

Answers - id, answer, candidate_id, question_id
Questions - id, question
Candidates - id, names

Each candidate will have the question and the answer. For example, I have 10 questions. Each candidate will have 10 questions and each question will have answer to it of the candidate.

Thanks

What I don't know is how will I link the table. For example, I have created a candidate, I want the newly created candidate to have all the questions linked to him from questions and answers page and vice versa.

Any idea guys?

P.S: I'm a newcomer to programming.

[PappaJohn]

I would set the tables up like this:

candidates:

id - integer, auto-increment
first_name - varchar of appropriate length
last_name - varchar of appropriate length

questions:

id - integer, auto-increment
candidate_id - integer to link the candidate to the question
question - text (unless short questions only, then varchar)

answers:

id - integer, auto-increment
question_id - integer to link the answer to the question
answer - text

[MHaris]

Originally Posted by PappaJohn

I would set the tables up like this:

candidates:

id - integer, auto-increment
first_name - varchar of appropriate length
last_name - varchar of appropriate length

questions:

id - integer, auto-increment
candidate_id - integer to link the candidate to the question
question - text (unless short questions only, then varchar)

answers:

id - integer, auto-increment
question_id - integer to link the answer to the question
answer - text

How would you query?

[PappaJohn]

To return all questions and answers associated with each candidate, for all the candidates, the SQL would be:

SELECT c.*, q.*, a.* FROM (( candidates c LEFT JOIN questions q ON q.candidate_id = c.id ) LEFT JOIN answers a ON a.question_id = q.id ) ORDER BY c.last_name

[MHaris]

Originally Posted by PappaJohn

To return all questions and answers associated with each candidate, for all the candidates, the SQL would be:

SELECT c.*, q.*, a.* FROM (( candidates c LEFT JOIN questions q ON q.candidate_id = c.id ) LEFT JOIN answers a ON a.question_id = q.id ) ORDER BY c.last_name

A friend of mine recommended me to use sessions instead of SQL. He told me that the long query is the wrong method.

Can you elaborate how can I use sessions and avoid this long query?

[PappaJohn]

I assumed you were storing this information in database tables (MySQL was my assumption). If that's the case, you will need SQL to retrieve the information from the database, sessions will not do that for you.

I believe what your friend is referring to is long query strings in the url, something like quiz.php?candidate=1&question=364&answer=1285. In which case, sessions would be useful.

That SQL I posted is not passed in the url, but is part of your php code.

[MHaris]

How would I update my page on each database update?

[Inigoesdr]

The page information is generated each time it's loaded so as soon as the database information changes the pages will show it.

[MHaris]

Originally Posted by Inigoesdr

The page information is generated each time it's loaded so as soon as the database information changes the pages will show it.

It doesn't updates the page for me.

candidates.php

PHP Code:

<?php

include('../config/db.php'); // Includes Database

$sql = mysql_query('SELECT * FROM candidates');

while($row = mysql_fetch_array($sql)){
    echo $row['name'];
    echo "<br />";
}

include('templates/candidates.html');
$submit = $_POST['submit'];
$candidate = $_POST['name'];

if(isset($submit)){

mysql_query("INSERT INTO candidates(name) VALUES('$candidate')") or die(mysql_error());

}

mysql_close(); // Closes DB connection

?>

candidates.html

<form action="candidates.php" method="post">
<input name="name">
<input type="submit" name="submit">
</form>

[Inigoesdr]

You're inserting the user after you select the users in the database.

PHP Code:

<form action="<?=$_SERVER['SCRIPT_NAME'];?>" method="post">
<input name="name">
<input type="submit" name="submit">
</form>
<?php
include('../config/db.php'); // Includes Database

$submit = $_POST['submit'];
$candidate = $_POST['name'];

if(isset($submit)){

mysql_query("INSERT INTO candidates(name) VALUES('$candidate')") or die(mysql_error());

}

$sql = mysql_query('SELECT * FROM candidates');

while($row = mysql_fetch_array($sql)){
    echo $row['name'];
    echo "<br />";
}

mysql_close(); // Closes DB connection

[likon]

there was one script in sourceforge.net

[MHaris]

How will I iterate a query?

When I insert a new question(through a form), I want to add the question(iterate) for every candidate in candidates table therefore, it would need to update the candidate_id to every question. How will I achieve this?

[Inigoesdr]

Well, I'm not sure what you're asking. There's no reason to add the question for everyone. You just need to store the question(w/possible answers), and the user(w/their answer).

[MHaris]

Originally Posted by Inigoesdr

Well, I'm not sure what you're asking. There's no reason to add the question for everyone. You just need to store the question(w/possible answers), and the user(w/their answer).

There is no possible answer. The answer is of the candidate and that's the correct answer in both(true or false condition).

You can look at the database structure above.

http://www.codingforums.com/showpost...82&postcount=2
(I don't have last_name for the candidates table, only name column)

[MHaris]

Originally Posted by MHaris

There is no possible answer. The answer is of the candidate and that's the correct answer in both(true or false condition).

You can look at the database structure above.

http://www.codingforums.com/showpost...82&postcount=2
(I don't have last_name for the candidates table, only name column)

Any ideas?