How can I join these two queries?

[facarroll]

Hi all.

I've revised an earlier post.

I have two queries that work fine. I cannot work out how to join them so that I can echo all of the results combined into the same array. I've unsuccessfully tried all of the JOIN methods, and also tried array_merge (although I suspect that there may be processing disadvantages if I use the latter). I've posted the two SELECT statements below, with their respective while statements.

managerId is the only column name common to the two tables, but see the reference to $egroup in the following paragraph.

$userId, $managerId and $egroup are session variables. When the variable $egroup=1 it equates to the name of one of six other columns in the topics table.

 

Code 1:

 

<?php
$query1 = mysql_query("SELECT url_big, url_small, title, comment
                 FROM topics
                 WHERE managerId = $managerId AND $egroup = 1 ORDER BY title ASC");

while ($row1 = mysql_fetch_array($query1))

   {
echo "<a href='../../wood/wood_tool_images/{$row1['url_big']}' target='_blank'><img src='../../wood/wood_tool_images/{$row1['url_small']}' /><br />\n";
echo "{$row1['title']} <br />\n";
echo "<a href='../equipment/{$row1['title']} Safety Quiz/{$row1['title']} Safety Quiz.php'>Take This Quiz</a><br />\n";
}
?>

 

Code 2:

 

<?php
$query2 = mysql_query("SELECT DISTINCT quizTitle, userId, passState, userScore, totalScore, userDate FROM quiz WHERE managerId = '$managerId' AND userId = '$userId' ORDER BY quizTitle, userDate ASC"); //query OK

while ($row2 = mysql_fetch_array($query2))

   {
echo "{$row2['userScore']} <br />\n";
echo "{$row2['passState']} <br />\n";
echo "{$row2['userDate']} <br />\n";
echo "blah, blah <br />\n";
   }
?>

 

And some code showing what I tried last. It should give an idea of what I want to do:

<?php
$query1 = mysql_query("SELECT DISTINCT topics.comment, topics.url_big, topics.url_small, topics.title, topics.pdf
                 FROM topics
                 INNER JOIN quiz
                 ON (topics.managerId = quiz.managerId)
                 WHERE topics.$egroup = 1
                 AND quiz.userId = '$userId'
                 AND quiz.passState = '$passState'
                 ORDER BY topics.title ASC
                 ");

while ($row1 = mysql_fetch_array($query1))
{
echo "<img src = '{$row1['url_small']}' /><br />\n";
echo "{$row1['title']} <br />\n";
echo "<a href='{$row1['title']} .php'>Click this link</a><br />\n";
echo "<img src='filename1.jpg' /><br />\n";
echo "{$row1['comment']} <br />\n";
   if ("{$row1['quiz.passState']}" == 1 )
   {echo "<img src='filename2.png' /><br />\n";}
    else {echo "blah, blah";}
}
?>

[fenway]

Combine how?

[facarroll]

I meant I wanted to derive information from two tables. I have had some success, but now I get my rows in triplicate. A common enough problem it seems, but I haven't worked it out yet. The code I've developed so far is here and without the triplicate problem, it's good..

 

                  <?php 
$query1 = mysql_query("SELECT DISTINCT comment, url_big, url_small, title, pdf, passState, userDate, userScore
				  FROM topics
				  LEFT JOIN quiz
				  ON (topics.managerId = quiz.managerId) AND (userId = '$userId') AND (userDate=(select max(quiz.userDate)))
				  WHERE $egroup = 1
				  ORDER BY topics.title ASC");

while ($row1 = mysql_fetch_array($query1))
{
echo "<a href='{$row1['url_big']}' target='_blank'><img src = '{$row1['url_small']}' /><br />\n"; 
{$row1['title']} <br />\n"; 
echo "<a href='{$row1['title']} Safety Quiz/{$row1['title']} Safety Quiz.php'>Take This Quiz</a><br />\n"; 
echo "<a href='{$row1['pdf']}' target='_blank'><img src='icon.jpg' /><br />\n"; 
echo "{$row1['comment']} <br />\n";
if ("{$row1['passState']}" == 1 ) {echo "<img src='tick2.png' /><br />\n";} elseif ("{$row1['passState']}" == 0 && "{$row1['userScore']}" >0){echo "<img src='cross2.png' /><br />\n ";}  else {echo "<img src='question2.png' /><br />\n ";} 
}

 

Can you suggest a solution to the rows being returned in triplicate?

[fenway]

Sorry, I all I see is PHP code.

 

Echo actual SQL queries, with sample output and desired output.