Store questions in one database, responses in another

Forum Moderators: coopster

Message Too Old, No Replies

Store questions in one database, responses in another

MySQL table query

s9901470

3:41 pm on Apr 14, 2005 (gmt 0)

I'd like to keep a list of questions for a quiz in one MySQL table, but the answers people give in another.

So the list of questions is generated from the database and not static HTML.

Can anyone give me an example of how that can be done with PHP/MySQL.

I currently have one table for the answers and write the questions manually in HTML.

Many thanks

bomburmusicmallet

5:45 pm on Apr 14, 2005 (gmt 0)

Create a table for questions. Then, associate the questions and answers somehow. If there is one answer per question, then create a field for the questionID in your answer table, and then do a query for an answer with a particular questionID. If there are multiple answers for each question, create a separate linking table of just questionIDs and answerIDs.

This all presupposes that you are creating questionID and answerID fields as part of the question and answer tables.

HTH, or at least gets you going.

phbk9d

6:13 pm on Apr 14, 2005 (gmt 0)

Ok, I will assume that you know how to write PHP code so you can understand what is going on. First let's cover the MySQL Tables.

MySQL
CREATE TABLE answers(
answerid INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
answer VARCHAR(255),
questionid INT);

CREATE TABLE questions(
questionid INT NOT NULL PRIMARY KEY AUTO_INCREMENT,
question VARCHAR(255));

PHP

[2]
<?php
 ######################################
 # Start Variables
 ######################################


 $mysql_host = 'YOUR MYSQL HOST';
 $mysql_user = 'YOUR MYSQL USER';
 $mysql_password = 'YOUR MYSQL PASSWORD';
 $mysql_database = 'YOUR MYSQL DATABASE';


 ######################################
 # End Variables
 ######################################


 // Connect To Database
 $link = mysql_connect($mysql_host, $mysql_user, $mysql_password)
 or die('Could not connect: ' . mysql_error());
 
 mysql_select_db($mysql_database) or die('Could not select database');


 // SQL GET ANSWERS AND QUESTIONS
 $query = 'SELECT a.answerid, a.answer, q.questionid, q.question FROM answers a, questions q WHERE a.answerid = q.questionid';
 $result = mysql_query($query) or die('Query failed: ' . mysql_error());


 //GET RESULTS
 while ($line = mysql_fetch_array($result, MYSQL_ASSOC)) {


 // $line[0] = answerid
 // $line[1] = answer
 // $line[2] = questionid
 // $line[3] = question
 print "QUESTION: $line[3]<br />\n";
 print "ANSWER: $line[1]<br />\n";
 
 }


 // CLEAN UP
 mysql_free_result($result);
 mysql_close($link);
?> 
[/2]

With these two tables you can run a lot of different queries on it. To get all answers for a give question run this:

SELECT answerid, answer FROM answers WHERE questionid = '$questionid';

where $questionid equals to the id of the question you want to find the answers for.

s9901470

12:39 pm on Apr 18, 2005 (gmt 0)

Great!
That works well. How can I generate a set of radio buttons for each answer (1 = very sure, 5 = very unsure)?

Thanks

realitybytes

12:55 pm on Apr 18, 2005 (gmt 0)

Great!
That works well. How can I generate a set of radio buttons for each answer (1 = very sure, 5 = very unsure)?
Thanks

You have a few options available to you for this, firstly, you could use the string value very sure - very unsure, however I would suggest using them as integer for later use.

here is a sample radio button form using post, and php to get the information.

/* form will post to the same page with <?=$_SERVER['PHP_SELF']?> change accordingly and it is formatted to newline with <br /> change this to suit aswell*/

<form action="<?=$_SERVER['PHP_SELF']?>" method="post">
<input type=radio name="mySure" value="1">Very sure<br />
<input type=radio name="mySure" value="2">Slightly sure <br />
<input type=radio name="mySure" value="3">Unbiased <br />
<input type=radio name="mySure" value="4">Slightly unsure <br />
<input type=radio name="mySure" value="5">Very unsure <br />
<input type="submit" name="rate" value="Submit">
</form>

<?php
if (isset($_POST['rate'])) {
// get value selected
$mySure = $_POST['mySure'];

/* then you can do what you like with the value, I have shown an echo, but you could put it in a database etc */

echo $mySure;
}

s9901470

2:04 pm on Apr 18, 2005 (gmt 0)

What if I want to put the form in the PHP itself?
I have modified the script based on comments on a different thread but it is similar to above:

$result = mysql_query("SELECT * FROM questions ORDER BY questionid DESC LIMIT 10 ");
$count=0;
while($row=mysql_fetch_array($result))
{
$count+=1;
$text.='<b>Question '.$count.'</b><br>
'.$row['Question_Contents'].'<br>
<label>
<input type="radio" name="q'.$count.'" value="1">
Strongly Agree</label>
<br>
<label>
<input type="radio" name="q'.$count.'" value="2">
Agree</label>
<br>
<label>
<input type="radio" name="q'.$count.'" value="3">
Unsure</label>
<br>
<label>
<input type="radio" name="q'.$count.'" value="4">
Disagree</label>
<br>
<label>
<input type="radio" name="q'.$count.'" value="5">
Strongly Disagree</label>
<br><br>';

}
echo $text;

{HOW DO I TAKE THE RESPONSES FOR EACH OF THE TEN QUESTIONS HERE AND PASS THEM TO A FORM ON THE NEXT PAGE?}
?>

realitybytes

4:33 pm on Apr 18, 2005 (gmt 0)

What if I want to put the form in the PHP itself?
I have modified the script based on comments on a different thread but it is similar to above:

}
echo '<form action="change to url of page you want to post answers to>" method="post">';
echo $text;
echo '<input type="submit" name="qcount" value="Submit">';
?>
<?php
//form detection on the next page
if (isset($_POST['qcount'])) {

/*then all of the values will be available in $_POST['q1']
q2
q3
q4
q5
and so on eg*/

//variable names are an example change to suit
$question0_answer = $_POST['q0'];
$question1_answer = $_POST['q1'];
}
echo $question0_answer;
// or
echo $_POST['q0'];
?>

s9901470

8:07 pm on Apr 18, 2005 (gmt 0)

Thanks - I get a blank page though. Am I doing this right?

FIRST PAGE

// Connect To Database
$link = mysql_connect($mysql_host, $mysql_user, $mysql_password)
or die('Could not connect: ' . mysql_error());

mysql_select_db($mysql_database) or die('Could not select database');

}
echo '<form action="1.php" method="post">';
echo $text;
echo '<input type="submit" name="qcount" value="Submit">';
?>

SECOND PAGE

<?php
//form detection on the next page
if (isset($_POST['qcount'])) {

/*then all of the values will be available in $_POST['q1']
q2
q3
q4
q5
and so on eg*/

//variable names are an example change to suit
$question0_answer = $_POST['q0'];
$question1_answer = $_POST['q1'];
}
echo $question0_answer;
// or
echo $_POST['q0'];
?>

Does the second page need a $_GET to capture the variables from the previous page?
What is the 'isset' term?

Thanks for your help

s9901470

8:12 pm on Apr 18, 2005 (gmt 0)

Actually - I've just got the above to work, I needed to add
$question0_answer = $_POST['q0'];
$question1_answer = $_POST['q1'];
$question2_answer = $_POST['q2'];
and so on

But do I need to list every question like this? If I have 50 questions, is there a way on the second page to define all the questions from the previous page automatically?
up to
$question50_answer = $_POST['50'];

Thanks

realitybytes

8:26 pm on Apr 18, 2005 (gmt 0)

isset is to check if any value other than null or 0 is stored in a variable etc.

which page is blank? try adding

echo $_POST['q1']; if it is the second page,

$_POST is fine get is used as part of the query string, and adds the data to the url, post will pass them in the background.

Well the values are already stored in variables,

you can access them by just using the $_POST['q1']
and so on. You do not need to add them to any further variables, I just gave that as an example.

s9901470

8:14 am on Apr 21, 2005 (gmt 0)

I'm not sure I understand how to access them on the next page without typing them all out:

$question0_answer = $_POST['q0'];
$question1_answer = $_POST['q1'];
$question2_answer = $_POST['q2'];
and so on up to
$question50_answer = $_POST['50'];

Is there a way using MySQL to INSERT q1-q50 INTO table?

s9901470

9:30 am on Apr 27, 2005 (gmt 0)

This is working great thanks. I now have a small problem, in that some of my questions have 'strongly agree' to 'strongly disagree' and some have 'strongly limited' to 'not at all limited' and so on.

What I really want is an extra field in the questions table caled 'answer types' and then I want the appropriate radio button labels to display depending on the 'answer type' related to the question.

So the code needs to say something like "Select each question from 'questions', display radio buttons (or text box) according to 'answer type''

Can anyone suggest how to modify the code below to achieve this?

Many thanks

$result = mysql_query("SELECT * FROM questions WHERE (questionid >=391) AND (questionid <=400) ORDER BY questionid");
$count=0;
while($row=mysql_fetch_array($result))
{
$count+=1;
$text.='<b><br>
'.$row['Question_Contents'].'</B><br>
<label>
<input type="radio" name="q'.$count.'" value="3">
Yes, limited a lot</label>
<br>
<label>
<input type="radio" name="q'.$count.'" value="4">
Yes, limited a little</label>
<br>
<label>
<input type="radio" name="q'.$count.'" value="5">
<label>No, not limited at all</label>
<br><label>
<input type="radio" name="q'.$count.'" checked value="8">
Select one</label> <br>';

}
echo '<form action="form1.php" method="post">';
echo $text;