Outputting dataset TWICE instead of once?

[galvin]

I'm doing the following query where "ctext" comes from the clues table and "answerid" and "atext" come from the answers table...

 

	$sql = "SELECT * from clues, answers 
					WHERE clues.quizid = '{$_GET['quizid']}'
					AND answers.quizid = '{$_GET['quizid']}'";
					$result = mysql_query($sql, $connection);
					if (!$result) { 
					die("Database query failed: " . mysql_error());
					} else {

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

						echo "<tr><td>" . $info['answerid'] . "</td>";

						echo "<td>".$info['ctext']."</td>";

						echo "<td>".$info['atext']."</td>";

						}
						echo "</tr>";
						}
					}

 

But when this displays in the browser, it's outputting each result set twice and kinda mixed up.  For example, it looks like this...

 

1 monkey funny

1 cat funny

2 monkey boring

2 cat boring

 

But I want (i.e. was expecting) it to display as...

 

1 monkey funny

2 cat boring

 

Can anyone tell me why it's showing TWO rows for each and seemingly mixing  up the returned results?

 

Ultimate question is this.  Do I have to break apart the mysql queries in order to get PHP to display the results how I want?  I gotta think there is a way to do just one query and do what I want, but I obviously can't figure it out

 

 

[Pikachu2000]

What results do you get when you echo the query, paste it in to phpMyAdmin and execute it?

[galvin]

If I put the code right into mysql, it brings back the same thing, i.e. 4 rows instead of what I want (i.e. 2 rows).

 

Clearly something with my syntax is wrong, but not sure what change I would make to only get 2 rows.

 

I could split it out into 2 separate queries, but that seems so inefficient (but maybe it's not )

 

I'll mess with it some more but if anyone can see my stupid mistake, let me know 

[jcbones]

For grins, try:

SELECT * from clues, answers
                  WHERE clues.quizid = answers.quizid
                  AND clues.quizid = '{$_GET['quizid']}'

[galvin]

Nice try, but it gave the same results (4 rows).  Damn, I was excited and thought might be the right answer

 

Thanks again for trying!

 

 

[jcbones]

What does your database table look like, and how would this one quizid link to funny and boring?

 

This may be due to database design, rather than the query design.

[galvin]

table "clues" structure:

clueid, quizid,cluetext

 

actual data example:

Row 1: 1,16,funny

Row 2: 2,16,boring

 

table "answers" structure:

answerid, quizid, answertext

 

actual data example:

Row1: 1,16,monkey

Row 2: 2, 16,cat

 

So based on that, I just want to get this back from my query...

 

1 funny monkey

2 boring cat

 

Instead, I'm getting back...

 

1 funny monkey

2 boring monkey

1 funny cat

2 boring cat

[jcbones]

Try this:  Matches the clueid to the answerid, which would match funny to monkey, and boring to cat.

SELECT * from clues, answers
                  WHERE clues.clueid = answers.answerid
                  AND clues.quizid = '{$_GET['quizid']}'

[galvin]

Got it!  You loosened it for me   Your last suggestion brought back other answers that had an answerid of 1 or 2, even if they didn't have a quizid of 16.  So I added one more line to the query and this worked...

 

SELECT * from clues, answers
					  WHERE clues.clueid = answers.answerid
					  AND clues.quizid = '{$_GET['quizid']}'
					  AND answers.quizid = '{$_GET['quizid']}'

 

Thanks so much for your help!!