[SOLVED] Help with JOIN query

[CrazeD]

I'm having trouble with a MySQL JOIN query.

 

I'm still dabbing with making forums, and someone suggested in my other thread to use a JOIN query to only use one query, instead of two (for categories and subcategories).

 

I can't quite get this to work as it's supposed to. Here's my table structure:

 

categories

cat_id | cat_title

0 | category 1
1 | category 2

 

forums (sub categories)

forum_id | cat_id | forum_title

0 | 0 | forum 1
1 | 0 | forum 2
3 | 1 | forum 3

 

So, forum 1 & 2 should be displayed under category 1, and forum 3 should be displayed under category 2.

 

However, this is not the case. It is displaying like this:

category 1

forum 1

category 1

forum 2

category 2

forum 3

 

It's displaying category 1 twice, I guess because there is more forums than categories.

 

Here is my code; which I found on a tutorial:

 

SELECT c.*, f.* FROM categories=c, forums=f WHERE c.cat_id = f.cat_id

 

I've tried LEFT JOIN, RIGHT JOIN, etc and can't make this work.

 

Thanks.

[Fadion]

Give this a go:

 

SELECT cat.cat_id, cat.cat_title, for.forum_id, for.cat_id, for.forum_title FROM categories AS cat
INNER JOIN forums AS for ON cat.id=for.cat_id
ORDER BY cat.cat_title

[gaza165]

Here is a simpler version of GuiltyGears code..

 

SELECT *
FROM CATEGORIES
INNER JOIN FORUMS
ON CATEGORIES.cat_id=FORUMS.cat_id

 

 

[CrazeD]

Tried both of your suggestions, gets me the same result.

[Fadion]

What is the PHP code you're using? You can post here the part where you make the query and fetch the data.

[CrazeD]

$sql = mysql_query ("SELECT cat.cat_id, cat.cat_title, for.forum_id, for.cat_id, for.forum_title FROM categories AS cat
INNER JOIN forums AS for ON cat.cat_id = for.cat_id")
while ($row = mysql_fetch_array ($sql)) {
echo $row['cat_title'].'<br />';
echo $row['forum_title'].'<br />';
}

[DarkWater]

Check out the GROUP BY clause.

[CrazeD]

Can you be more specific?

[aschk]

What you're asking for can't be achieved by SQL you need to perform this using PHP.

The SQL result you are getting back is perfectly fine in each case, what you're attempting to do is show the results in a categorised fashion.

 

i.e. the resultset you are getting is as follows:

forum_id | cat_title | forum_title
0 | category1 | forum 1
1 | category2 | forum 2
3 | category2 | forum 3

 

This is PERFECT, as there is no such thing as nested presentation in SQL. Results are 2D datasets.

 

Anyway to resolve your problem you need to do the following in PHP:

 

// Dataset container
$data = array();

// SQL query string
$query_string = "
SELECT f.forum_id
      ,c.cat_title
      ,f.forum_title
FROM categories c
JOIN forums f ON c.cat_id = f.cat_id
ORDER BY cat_title
"; // This gives an alphabetical result by category.

// Get SQL result.
$result = mysql_query($query_string);

// Build dataset from result.
while($row = mysql_fetch_assoc($result)){
  $data[] = $row;
}

// Perform presentation
$current_cat = "";
foreach($data as $d){
  if($d['cat_title'] != $current_cat){
    $current_cat = $d['cat_title'];
    echo $current_cat . "<br/>";
  }
  echo $d['forum_title'] . "<br/>";
}
[code]

The above will need some tweaking for the particular of the presentation (i.e. tabulated, or line breaks or whatever html you have), but you get the gist of it hopefully.

[/code]

[CrazeD]

Thanks, that works.