how do i merge these 2 codes?

[treprti]

Hi,
I am trying to create a table listing like this:

problem | solution
pr1      | sol1
pr1      | sol2
pr1      | sol3
pr2      | sol4 

from 3 mysql indexes:

mainindex:
autoID | problemID | solutionID
1      | 1           |1
2      | 1           |2
3      | 1           |3
4      | 2           |4

problems:
autoID | problem
1      | pr1
2      | pr2

solutions:
autoID | solution
1      | sol1
2      | sol2 

i have 2 pieses of code, the first piece populates the problem part:

<?php
	$i=0;
	while ($i < $num)
	{
		$result = mysql_query("SELECT problem FROM problems WHERE autoID = '$problemID' ") or die(mysql_error());
		$row = mysql_fetch_array( $result );
		$problem = $row['problem'];

		//$result = mysql_query("SELECT solution FROM solutions WHERE autoID = '$solutionID' ") or die(mysql_error());
		//$row = mysql_fetch_array( $result );
		//$solution = $row['solution'];

		?>
		<TABLE width="100%" border="1">
			<TR>
			<TD width="10%"><a href=""><? echo $problem; ?> </a>
			//<TD width="10%"><a href=""><? echo $solution; ?> </a>
		</TABLE>
		<?php
		$i++;
	}

and the second piece gets the corresponding solutions from the mysqldb:

$sql = "SELECT autoID, solution_indexID FROM mainindex WHERE solution_indexID = 1";
$result = mysql_query( $sql );
while( $row=mysql_fetch_row($result) )
{
  $found[] = $row[1];
}
mysql_free_result( $result );


foreach( $found as $solution )
{
    echo $solution."\n";
};

but i am at a loss in combining the two.
Can anybody help me, or when my approach is totally wrong, correct me?

[treprti]

I cant seem to edit my original post, but the first line of the last piece should be

$sql = "SELECT autoID, solutionID FROM mainindex WHERE problemID = 1";

[Redscouse]

Try this:
[php]

<?php
$query = “SELECT *
FROM mainindex
LEFT JOIN problems
ON mainindex.problemID = problems.autoID
LEFT JOIN solutions
ON mainindex.solutionID = problems.autoID
ORDER BY mainindex.autoID ASC”;
$result = mysql_query($query);
while($row = mysql_fetch_array($result, MYSQL_ASSOC)) {
print ‘’;
echo ‘’;
echo ‘’;
print ‘’;
}
?>

Problem	Solution
’ . $row[‘problem’] . ‘	’ . $row[‘solution’] . ‘

[/php]

Let me know if it does what you need.

Cheers,
Red.

[Redscouse]

Just spotted a mistake in my query…

Try this:
[php]<?php
$query = “SELECT *
FROM mainindex
LEFT JOIN problems
ON mainindex.problemID = problems.autoID
LEFT JOIN solutions
ON mainindex.solutionID = problems.autoID
ORDER BY mainindex.autoID ASC”;
[/php]

this line: ON mainindex.solutionID = problems.autoID
should be: ON mainindex.solutionID = solutions.autoID

Sorry, my bad! :-[

[treprti]

Awesome!
That worked as a charm
Thank you so much, I would not have been able to do this myself and now i can finally start understanding joins

[Redscouse]

Glad you got it working, always happy to help.
Red.

[treprti]

I do actually have one more question.

Not all problems have a solution, and, allthough it sounds weird, not all solutions have a problem, and they are not listed right now.
Is there a way to list those as well?

[treprti]

nvm… i was to quick with my question, i can just use identifiers for empty solutions/problems. solved