stumped with php and mysql

Creamy Goodness

first a little explanation.

i have a mysql table of projects that is layed out like this:

Project:
projectID  |  Project name  |  SubCategory  |  answer1  |  answer2  |  answer3  |  answer4  | ........  |  answer10  |      

there are ten questions that a user has to answer, the questions and answers are stored in a another table called questions. which looks like this.

Questions:
id  |  questionText  |  AnswerText  |

so the questionText contains the text of the question i want to ask "was this completed on time" and the AnswerText contains the possible answers (delimited by a ~) that the user can select so for this question the answerText looks like "Yes~No".

now what i'm trying to do is create a table that outputs the id and question text in one row, and then on the next row have a drop down box with the possible answers in them. this is no problem and i've gotten my script this far.

the problem arises when say i submit my answers to the database and the answers get stored in the Project table under the answer1...answer10 depending on which question they answered and then if the user comes back to edit their answers i want to display the answers to the questions they answered.

so basically it's have a drop down box populated by the columns answer1..10 from the project and then add in the other answers from the questions table.

this is what i have to create the ten drop down boxes table.

[php]
$query = "SELECT id, question, answer from questions";

$result = mysql_query($query) or die ( mysql_error() );

echo "<form action=\"submit.php\" method=\"post\">
<table class=\"stats\"><tbody>
<tr>
<td class=\"hed\" colspan=\"2\">Questions:</td>
</tr>
";


while($row = mysql_fetch_array($result))
{

$id = $row;
$question = $row;
$answer= $row;

//split the answer string on a ~

$answers = explode("~", $answer);

$selected = "selected=\"selected\"";

echo "<tr>
<td class=\"hed\" colspan=\"2\">$id. $question</td>
</tr>
<tr>
<td colspan=\"2\">
<select id=\"question_$id\" name=\"question_$id\"><option value=\"\">-</option>
";

foreach($answers as $value)
{
echo "<option id=\"$value\" value=\"$value\">$value</option>";
}

}
[/php]

Adam

This line: [php]echo "<option id=\"$value\">$value</option>"; [/php]

It's you're using what you want to be the value that's stored to the database, but you're calling it the options id, not the options value. View your source to see what I mean.

You need: [php]echo "<option id=\"$value\" value=\"$value\">$value</option>";[/php]

Creamy Goodness

sorry the above was a older version of that foreach loop the value="$value" should be there. i'll edit the previous post.

i think you kind of misread my post (probably my fault).

basically what i want is to have a drop down box that has the value inside answer1 column selected, then have the other answers for question one in that box as well but not to duplicate the answer1 value.

hope that clarifies it.

Adam

Ah ok. Well what you want to do then is simply check which of the answers is the current one. So you change the <option> line to the following:

[php]echo "<option value='$value' ".if($value == $answer) { echo 'SELECTED'; }.">$value</option>";[/php]

Now I think this is right, I'm trying to work it out in my head in a bit of a rush though. Basically what I'm getting at is within your foreach, you check to see for each answer if the answer in the database matches it, and then make it selected if it is. So while it's running though all the select boxes, when it comes across a match it will make it the selected option.

*disclaimer*

Could be way off the ball!

Creamy Goodness

i've tried that.

at the top of the script i grab all the answers to a certain project.

[php]
$sqlquery = "select answer1,answer2,answer3,answer4,answer5,answer6,answer7,answer8,answer9,answer10 from trs WHERE wrnid='$wrnID'";

$answers = array();

$result = mysql_query($sqlquery) or die (mysql_error());
while($row = mysql_fetch_array($result))
{
$answers[] = $row;
$answers[] = $row;
$answers[] = $row;
$answers[] = $row;
$answers[] = $row;
$answers[] = $row;
$answers[] = $row;
$answers[] = $row;
$answers[] = $row;
$answers[] = $row;
}
[/php]

then in the second while loop where i'm writing the line

[php]echo "<option id=\"$value\" value=\"$value\">$value</option>"; [/php]

i have this

[php]
$query = "SELECT id, question, answer from questions";

$result = mysql_query($query) or die ( mysql_error() );

while($row = mysql_fetch_array($result))
{

$id = $row;
$question = $row;
$answer= $row;

//split the answer string on a ~

$predefAnswers = explode("~", $answer);


echo "<tr>
<td class=\"hed\" colspan=\"2\">$id. $question</td>
</tr>
<tr>
<td colspan=\"2\">
<select id=\"question_$id\" name=\"question_$id\"><option value=\"\">-</option>
";

//from the array in the first sql query.
$t = array_shift($answers);
echo "t is $t";

foreach($predefAnswers as $value)
{
$selected = "";
if($t == value){ $selected = "selected";}
echo "<option id=\"$value\" $selected>$value</option>";
}

}
[/php]

this sets every option value to be selected, here's the html source.

<select id="question_1" name="question_1"><option value="">-</option>
	<option id="Yes" value="Yes" selected>Yes</option>
        <option id="No" value="No" selected>No</option>
</select>

Adam

Let me just clarify here for a second. What you want to do is print out the ten select boxes, with all the options printed only once, and one answer selected in each based on the answers given previously?

Creamy Goodness

yup

the answers given previously are stored in the project table.

and the possible list of answers are stored in the questions table.

Adam

Ok Cremo, here's a solution. Actually it's more like pseudo code, you've got a bit more database work going on. What I'm saying is this does the job, I created a dummy database to test based on your schema, but I won't claim it's the best solution in the world. Maybe it is though!

Let me know how you get on:

[php]
<?

mysql_connect('localhost', 'root', '');
mysql_select_db('test');

$query_q="SELECT * FROM questions";
$result_q=mysql_query($query_q) or die(mysql_error());

$i=1;

while($row_q=mysql_fetch_array($result_q)) {

$answers = explode("~", $row_q);

echo $row_q;
echo "<br />";

echo "<select name=question$i>";
foreach($answers as $answer) {

$query_a="SELECT answer$i FROM trs";
$result_a=mysql_query($query_a) or die(mysql_error());
$row_a=mysql_fetch_array($result_a);

$field = 'answer'.$i;

if($answer == $row_a[$field]) {
$selected = " SELECTED";
} else {
$selected = "";
}

echo "<option value=$answer$selected>$answer</option>";
}
echo "</select>";
echo "<br /><br />";

$i++;

}

?>
[/php]

daymobrew

Quick aside: You should use selected="selected" in the "option" element.

Cantab.

mysql_fetch_assoc() instead of mysql_fetch_array() ? :rolleyes:

Adam

Cantab. wrote: »

mysql_fetch_assoc() instead of mysql_fetch_array() ? :rolleyes:

Where? And why? You could be more specific...

Creamy Goodness

sorry guys but been snowed under with other parts of my projects i completely forgot about this.

got it working guys with thanks to mirror lovely code above, thanks a mil.

one thing though, my question seven has now turned into a text box, so i need to break out of the foreach loop when $i = 7.

[php]
foreach($answers as $answer)
{
//tried breaking out like this
if($i == 7){
break;
}

$query_a="SELECT answer$i FROM trs";
$result_a=mysql_query($query_a) or die(mysql_error());
$row_a=mysql_fetch_array($result_a);

$field = 'answer'.$i;

if($answer == $row_a[$field]) {
$selected = " SELECTED";
} else {
$selected = "";
}

echo "<option value=$answer$selected>$answer</option>";
}
[/php]
and then have a if($i == 7) //do the code for a text box instead.

but what i get is two question 7s , one with a drop down box which i don't want and one with a text box like i want


*edit* whoops nevermind the if($i == 7){break;} should of been in the while and not the foreach loop, and the code for printing the textbox should be outside while loop