homepage Welcome to WebmasterWorld Guest from 54.198.139.141
register, free tools, login, search, subscribe, help, library, announcements, recent posts, open posts,
Accredited PayPal World Seller

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
Forum Library, Charter, Moderators: coopster & jatar k

PHP Server Side Scripting Forum

    
Store questions in one database, responses in another
MySQL table query
s9901470




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

Hi

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




msg:1305974
 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




msg:1305975
 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




msg:1305976
 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




msg:1305977
 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




msg:1305978
 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




msg:1305979
 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:


$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 '<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




msg:1305980
 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');

$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 '<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




msg:1305981
 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




msg:1305982
 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




msg:1305983
 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




msg:1305984
 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;

?>

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / PHP Server Side Scripting
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About
© Webmaster World 1996-2014 all rights reserved