Match row to row...

[dannyluked]

Hi,

I have two tables;

Cat

id      |      name    |

1      |      Cat 1    |

2      |      Cat 2    |

3      |      Cat 3    |

 

Forum

id      |  forumname | catid |

1      |      forum 1  |  1

2      |      forum 2  |  1

3      |      forum 3  |  2

 

I am trying to get the output;

Cat 1

Forum 1

Forum 2

 

Cat 2

Forum 3

 

(notice there is no Cat 3 as there are no forums for that category)

 

I cannot get any sort of query to work (im a newbie)! All I really want to do is match up two rows but im finding it hard.

 

Thanks

[premiso]

My SQL is a little rusty, but this query would work. In your PHP code is where you would make the display how you want it, but that should have it sorted like you described in the output.

 

SELECT c.name, f.forumname FROM forum f JOIN cat c on f.catid = c.id ORDER BY c.name, f.forumname

 

[dannyluked]

That works however it only shows one forum.forumname under each cat.name. Thanks

[JustLikeIcarus]

In the interest of having more than one ways to skin a cat ..... (see whay i did there?)

 

This would work as well.

SELECT c.name, f.forumname 
FROM forum f, cat c 
WHERE f.catid = c.id
ORDER BY c.name, f.forumname

 

I dont know why but I hardly ever use the "JOIN" keywords.  I think they tend to confuse me, lol.

[dannyluked]

Same again, only 1 forum under each cat

[PFMaBiSmAd]

It's up to your presentation code to output it the way you want. The query just returns the rows you want in the order that you want them.

 

 

[dannyluked]

This is what I have;

 

<?php
   include "config.php";
   mysql_connect($server, $db_user, $db_pass) or die(mysql_error());
   mysql_select_db($database) or die(mysql_error());

$result = mysql_query("SELECT c.*, f.*
FROM forum f, cat c
WHERE f.catid = c.id
ORDER BY c.name, f.forumname") or die(mysql_error()); 
$row = mysql_fetch_array( $result );
while($qry = mysql_fetch_array($result)){
echo "<b>$qry[name]</b><br />";
echo "-$qry[forumname]-<br>
<br>
";
}
?>

[PFMaBiSmAd]

Someone just asked and was shown how to output a new heading only when it changes value - http://www.phpfreaks.com/forums/index.php/topic,279704.msg1324897.html#msg1324897

[JustLikeIcarus]

If the joins arent doing it for you try swapping the order of equality condition.  Could change the result set since it would now select categories based of the forums.

 

SELECT c.*, f.*
FROM  forum f, cat c
WHERE c.id =  f.catid
ORDER BY c.name, f.forumname"

[dannyluked]

Thanks, Ill try this tommorow - Thats me done for tonight!

[premiso]

Correct, that is how it will be done due to certain limitations. This is where you use your PHP code to display how you want:

 

$query = "SELECT c.name, f.forumname FROM forum f JOIN cat c on f.catid = c.id ORDER BY c.name, f.forumname";
$result = mysql_query($query) or trigger_error("Query Failed: " . mysql_error());

$priorCat = "";
$output = "";
while ($row = mysql_fetch_row($result)) {
    if ($priorCat != $row[0]) {
         $priorCat = $row[0];
         $output .= "<u><b>" . $row[0] . "</b></u><br /><br />";
    }

    $output .= $row[1] . "<br />";
}

echo $output;

 

And it will display as you wanted it to.

 

EDIT:

Since I already had this coded up before the above replies I decided to just post it.

[dannyluked]

Worked perfectley first time!

Thanks for the easy answer and fully working code.