[RESOLVED] An INNER JOIN problem.

facarroll

Can anyone see what is wrong here? This is my first serious attempt at this.

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

anakadote

What does this output?

<?php
$query1 = mysql_query("
SELECT topics.url_big, topics.url_small, topics.title, topics.".$egroup.", quiz.passState 
FROM topics 
INNER JOIN quiz 
ON (topics.managerId = quiz.".$managerId.") 
WHERE topics.".$egroup." = 1 
ORDER BY title ASC") or die(mysql_error());
?>

It looks like your ON condition isn't comparing columns but rather the manageId column from topics and then (I assume) an nonexistent column from quiz because you're using $managerId. I could be mistaken but do you mean this instead:

ON (topics.managerId = quiz.managerId)

facarroll

Thanks. That's put me on the right track. I have output, but the entries to the webpage are in duplicate. Would you like to comment on the rest of the code?

                  <?php

$query1 = mysql_query("SELECT topics.url_big, topics.url_small, topics.title, topics.".$egroup.", quiz.passState FROM topics INNER JOIN quiz ON (topics.managerId = quiz.managerId) WHERE topics.".$egroup." = 1 ORDER BY title ASC");				  

?>

              <table width="850" style="font-size:16px"
<?php
while ($row1 = mysql_fetch_array($query1))
{
?> 
                  <tr>
                    <td width="45"></td>
                    <td width="132" style="vertical-align:middle"><?php echo "<a href='../../wood/wood_tool_images/{$row1['url_big']}' target='_blank'><img src='../../wood/wood_tool_images/{$row1['url_small']}' alt='' /><br />\n"; ?></td>
                    <td width="25"></td>
                    <td width="172" style="vertical-align:middle"><?php echo "{$row1['title']} <br />\n"; ?></td>
                    <td width="25"></td>
                    <td width="132" style="vertical-align:middle"><?php echo "<a href='../equipment/{$row1['title']} Safety Quiz/{$row1['title']} Safety Quiz.php'>Take This Quiz</a><br />\n"; ?></td>
                    <td width="25"></td>
                    <td width="112" style="vertical-align:middle"><?php echo "<img src='../../wood/wood_tool_images/{$row2['testcompleted.jpg']}'  /><br />\n";?></td>
                    <td width="25"></td>
                    <td width="122" style="vertical-align:middle"><?php echo "{$row3['userDate']} <br />\n";?></td>
                    <td width="45"></td>
                  </tr>                  

<?php
}
?>
                </table>

facarroll

Sorry. That last posted code should have been...

                  </table>

              <?php

$query1 = mysql_query("SELECT topics.url_big, topics.url_small, topics.title, topics.".$egroup.", quiz.passState, quiz.userDate FROM topics INNER JOIN quiz ON (topics.managerId = quiz.managerId) WHERE topics.".$egroup." = 1 AND ORDER BY title ASC");				  

?>

              <table width="850" style="font-size:16px"
<?php
while ($row1 = mysql_fetch_array($query1))
{
?> 
                  <tr>
                    <td width="45"></td>
                    <td width="132" style="vertical-align:middle"><?php echo "<a href='../../wood/wood_tool_images/{$row1['url_big']}' target='_blank'><img src='../../wood/wood_tool_images/{$row1['url_small']}' alt='' /><br />\n"; ?></td>
                    <td width="25"></td>
                    <td width="172" style="vertical-align:middle"><?php echo "{$row1['title']} <br />\n"; ?></td>
                    <td width="25"></td>
                    <td width="132" style="vertical-align:middle"><?php echo "<a href='../equipment/{$row1['title']} Safety Quiz/{$row1['title']} Safety Quiz.php'>Take This Quiz</a><br />\n"; ?></td>
                    <td width="25"></td>
                    <td width="112" style="vertical-align:middle"><?php echo "<img src='../../wood/wood_tool_images/{$row1['testcompleted.jpg']}'  /><br />\n";?></td>
                    <td width="25"></td>
                    <td width="122" style="vertical-align:middle"><?php echo "{$row1['userDate']} <br />\n";?></td>
                    <td width="45"></td>
                  </tr>                  

<?php
}
?>
                </table>

anakadote

Are there actual duplicate entries in the database, or is your query returning duplicate results?

facarroll

There are no duplicate entries. The code still returns duplicate entries. For example. Each query is returned twice, with duplicate url_small, duplicate title and duplicate "Take this quiz" link, but the separate dates (only two dates) are delivered for each duplicate. I've gone over the code for a third time, (with amendments) and posted it below.

I've attached a screenshot.

                  

                  <?php

$query1 = mysql_query("SELECT topics.url_big, topics.url_small, topics.title, topics.".$egroup.", quiz.passState, quiz.userDate FROM topics INNER JOIN quiz ON (topics.managerId = quiz.managerId) WHERE topics.".$egroup." = 1 ORDER BY title ASC");				  

?>

              <table width="850" style="font-size:16px">
<?php
while ($row1 = mysql_fetch_array($query1))
{
?> 
                  <tr>
                    <td width="45"></td>
                    <td width="132" style="vertical-align:middle"><?php echo "<a href='../../wood/wood_tool_images/{$row1['url_big']}' target='_blank'><img src='../../wood/wood_tool_images/{$row1['url_small']}' alt='' /><br />\n"; ?></td>
                    <td width="25"></td>
                    <td width="172" style="vertical-align:middle"><?php echo "{$row1['title']} <br />\n"; ?></td>
                    <td width="25"></td>
                    <td width="132" style="vertical-align:middle"><?php echo "<a href='../equipment/{$row1['title']} Safety Quiz/{$row1['title']} Safety Quiz.php'>Take This Quiz</a><br />\n"; ?></td>
                    <td width="25"></td>
                    <td width="112" style="vertical-align:middle"><?php echo "<img src='../../wood/wood_tool_images/{$row2['testcompleted.jpg']}'  /><br />\n"; ?></td>
                    <td width="25"></td>
                    <td width="122" style="vertical-align:middle"><?php echo "{$row1['userDate']} <br />\n"; ?></td>
                    <td width="45"></td>
                  </tr>                  

<?php
}
?>
                </table>

johanafm

I'm guessing the quiz table contains one entry per user since you have quiz.userDate. Since you join topic with quiz, for each row in quiz that matches a particular topic, you will have all the data from that row in topic and all the data from each respective row in quiz.

facarroll

Thanks. Much appreciated. I have also discovered the value of pen and paper and mapping. Cheers.