problems with loops

[mattennant]

I have two tables one contains a row of questions, the other contains users answers to those questions.

 

question table rows are roughly like this

id  -  question1  -  question2 -  question3 - etc etc

 

answer table is

id  -  username - answer1  -  answer2  -  answer3 - etc etc

 

i'm trying to display the results as follows

 

question 1

answer user 1

answer user 2

 

question 2

answer user 1

answer user 2

 

the following code gets me this far

 

question 1

answer user 1

answer user 2

 

question 2

 

but i am unable to display the answers after question 1 - hers's the code

$number = $row_task_question['qnum'];
				for( $y=1; $y<=$number; $y++ ){
                    
                    print "\t\n";
                    print "\t\t<strong>";
                    
				echo  $row_task_question['question'.($y)] .  '</strong><p>';

					do {
					echo $row_task_answer['answer'.($y)] . '<br>';
					echo $row_task_answer['username'].'<P></td>';
					} while ($row_task_answer = mysql_fetch_assoc($task_answer));

                    print "\n";
                   }

 

i'm hoping i'm missing something simple and obvious, but it's driving me crazy, any help or pointers in the right direction would be much appreciated

thanks

mat

 

 

[teng84]

show us the query i believe it would be allot easier to deal with your DB than using php in this case ..

[mattennant]

the query is a bit embarrassing, it's spewed out from dreamweaver,

but here goes, here's the answer query

$colname2_task_answer = "-1";
if (isset($_GET['step_ref'])) {
  $colname2_task_answer = (get_magic_quotes_gpc()) ? $_GET['step_ref'] : addslashes($_GET['step_ref']);
}
$colname_task_answer = "-1";
if (isset($_GET['task_ref'])) {
  $colname_task_answer = (get_magic_quotes_gpc()) ? $_GET['task_ref'] : addslashes($_GET['task_ref']);
}
mysql_select_db($database_mattyboy, $mattyboy);
$query_task_answer = sprintf("SELECT * FROM answer_text WHERE task_ref = %s AND step_ref = %s", $colname_task_answer,$colname2_task_answer);
$task_answer = mysql_query($query_task_answer, $mattyboy) or die(mysql_error());
$row_task_answer = mysql_fetch_assoc($task_answer);
$totalRows_task_answer = mysql_num_rows($task_answer); 

and here's the  here's the question query

$colname2_task_question = "-1";
if (isset($_GET['step_ref'])) {
  $colname2_task_question = (get_magic_quotes_gpc()) ? $_GET['step_ref'] : addslashes($_GET['step_ref']);
}
$colname_task_question = "-1";
if (isset($_GET['task_ref'])) {
  $colname_task_question = (get_magic_quotes_gpc()) ? $_GET['task_ref'] : addslashes($_GET['task_ref']);
}
mysql_select_db($database_mattyboy, $mattyboy);
$query_task_question = sprintf("SELECT * FROM question_text WHERE task_ref = '%s' AND step_ref = '%s'", $colname_task_question,$colname2_task_question);
$task_question = mysql_query($query_task_question, $mattyboy) or die(mysql_error());
$row_task_question = mysql_fetch_assoc($task_question);
$totalRows_task_question = mysql_num_rows($task_question);

I Know

[teng84]

just a suggestion its dont wanna read the code lol

 

do a select statement like this..

select t1.question,t2.answer form table1 t1
left joint table2 t2 on t1.id=t2.id
where condition here...
[code]

running this kind of query will give you what you need this is allot shorter and easier i guess?

[/code]

[phpSensei]

Don't you need a question_id? To specify which questions they are answering?

[mattennant]

i do have a question id it's called, step_ref, i think i missed that bit out -oops ,i'm having no trouble matching up the q and a's just spitting out all the answers - could be a late night!

[phpSensei]

<?php

$question_1 = mysql_query("SELECT question1 FROM questions");

while($row_1=mysql_fetch_array($question_1)){

$answer = mysql_fetch_array(mysql_query("SELECT * FROM answers WHERE step_ref = '$row_1[question1]'"));

// Question 1
echo $row['question1'];
//Answer 1
echo $answer['answer_1'];


}
}

$question_2 = mysql_query("SELECT question2 FROM questions");

while($row_2=mysql_fetch_array($question_1)){

$answer = mysql_fetch_array(mysql_query("SELECT * FROM answers WHERE step_ref = '$row_2[question2]'"));

// Question 2
echo $row['question2'];
//Answer 2
echo $answer['answer_2'];


}
}

?>

 

and keep going for question 3.

 

I am sorry, this isnt the right way of doing it but...

[teng84]

i have given a sample query that should do the trick :-\

 

try to follow that and tell us what happen

[phpSensei]

i have given a sample query that should do the trick :-\

 

try to follow that and tell us what happen

 

don't forget the step_ref, the rows of questions answered can be different by the ID given in the step_ref. 

 

 

[mattennant]

i'm just looking into that join query now, thanks for that, will hopefully get this sorted before my eyelids finally shut, thanks for the advice

[teng84]

 

@phpSensei

I dont understand you .. look if you will query tables and theres a relationship with them theres no other way to do it but to join them

this step_ref  is only part of the condition and has nothing to do with the relationship of table

hmm .. im confuzed with your idea

[phpSensei]

 

@phpSensei

I dont understand you .. look if you will query tables and theres a relationship with them theres no other way to do it but to join them

this step_ref  is only part of the condition and has nothing to do with the relationship of table

hmm .. im confuzed with your idea

 

Well he put a ID there, meaning there must be like

 

id = 1

question1

question2

question3

 

then

 

id

step_ref

answer1

answer2

answer3

 

 

Now lets say there was id 1 and 2 for the questions meaning

 

id = 1

question1

question2

question3

 

id = 2

question1

question2

question3

 

 

so step_ref can be 1 or 2 depending on the ID of the question.

 

Know what i mean?

 

 

My query won't work, because I am a little confused with the way he has setup his database tables.

[mattennant]

does this look right to you, getting a mysql error

 

select question_text.question1, answer_text.answer1

from question_text

left joint answer_text.foreign  on question_text  question_text.number=answer_text.foreign

FROM question_text

WHERE task_ref = 'colname' AND step_ref = 'colname2'

 

[teng84]

select question_text.question1, answer_text.answer1
from question_text
left joint answer_text  on question_text.number=answer_text.foreign
FROM question_text
WHERE task_ref = 'colname' AND step_ref = 'colname2'