Jump to content

Match row to row...


dannyluked

Recommended Posts

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

Link to comment
https://forums.phpfreaks.com/topic/184295-match-row-to-row/
Share on other sites

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

 

Link to comment
https://forums.phpfreaks.com/topic/184295-match-row-to-row/#findComment-972962
Share on other sites

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.

Link to comment
https://forums.phpfreaks.com/topic/184295-match-row-to-row/#findComment-972971
Share on other sites

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>
";
}
?>

Link to comment
https://forums.phpfreaks.com/topic/184295-match-row-to-row/#findComment-972977
Share on other sites

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"

Link to comment
https://forums.phpfreaks.com/topic/184295-match-row-to-row/#findComment-972980
Share on other sites

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.

Link to comment
https://forums.phpfreaks.com/topic/184295-match-row-to-row/#findComment-972991
Share on other sites

Archived

This topic is now archived and is closed to further replies.

×
×
  • Create New...

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.