Sort list from a join

[bayswater]

Hi,

 

In my database I have tables for:

- Tools (sc_tools)

- Categories (sc_categories)

- Screencasts (sc_screencasts)

 

In the database there are connections between the mentioned as follows:

1) "sc_tools" has an id: tool_id

2) "sc_categories" has an id: (cat_id) AND a relation to sc_tools (cat_rel_to_tool - which is = tool_id)

3) "sc_screencasts" has an: id (sc_id) AND a relation to sc_tools (sc_rel_to_tool - which is = tool_id) AND a relation to sc_categories (sc_rel_to_cat - which is = sc_id)

 

 

Ok, so I want to list my data as follows:

 

Tool 1

- Category 1

- - Screencast 1.1

- - Screencast 1.2

- Category 2

- - Screencast 2.1

Tool 2

- Category 1

- - Screencast 1.1

osv.

 

 

I have been tryin with the JOIN-query:

 

    $sql_screencasts = mysql_query("SELECT t.tool_id, t.tool_name, c.cat_id, c.cat_name_dk, s.sc_id, s.sc_desc_dk
    FROM sc_tools t
    JOIN sc_categories c ON c.cat_rel_to_tool = t.tool_id
    JOIN sc_screencasts s ON s.sc_rel_to_cat = c.cat_id
    ");
   
    while ($row_screencasts = mysql_fetch_array($sql_screencasts ))
    {
    $tool_name = $row_screencasts["tool_name"];
    $cat_name_dk = $row_screencasts["cat_name_dk"];
    $sc_desc_dk = $row_screencasts["sc_desc_dk"];
    $sc_id = $row_screencasts["sc_id"];
               
    echo "<br><font size=3><b>".$tool_name."</b></font><br>";
    echo "- ".$cat_name_dk;
    echo "<br>- - <a href='view.php?id=".$sc_id."'>".$sc_desc_dk."</a><br>";
    }

 

But is lists the data as:

Tool 1

- Category 1

- - Screencast 1

 

Tool 1

- Category 1

- - Screencast 2

 

etc. - I can't get e GROUP BY working either, so I'm lost right now. Hopefully an expert can help me out? Any help is highly appreciated!

[denno020]

Something that might work is saving each part into a tmp variable, and then comparing the next item with the tmp to see if it's the same. If it is, then don't echo that part, skip to the next, do the check for it's tmp variable etc. Obviously it would be easier to understand if I provide you with some code, so here goes:

 

$sql_screencasts = mysql_query("SELECT t.tool_id, t.tool_name, c.cat_id, c.cat_name_dk, s.sc_id, s.sc_desc_dk
    FROM sc_tools t
    JOIN sc_categories c ON c.cat_rel_to_tool = t.tool_id
    JOIN sc_screencasts s ON s.sc_rel_to_cat = c.cat_id
    ");
//initialize the tmp variables
$tmp_cat = '';
$tmp_tool = '';
$tmp_sc = '';
    while ($row_screencasts = mysql_fetch_array($sql_screencasts ))
    {
    $tool_name = $row_screencasts["tool_name"];
    $cat_name_dk = $row_screencasts["cat_name_dk"];
    $sc_desc_dk = $row_screencasts["sc_desc_dk"];
    $sc_id = $row_screencasts["sc_id"];
            
if($tmp_tool != $tool_name){
	echo "<br><font size=3><b>".$tool_name."</b></font><br>";
	$tmp_tool = $tool_name //update the tmp variable to the recently printed part
}
if($tmp_cat != $cat_name){
	echo "- ".$cat_name_dk;
	$tmp_cat = $cat_name;	//update tmp variable
}
if($tmp_sc != $sc_desc_dk){
	echo "<br>- - <a href='view.php?id=".$sc_id."'>".$sc_desc_dk."</a><br>";
	$tmp_sc = $sc_desc_dk;	//update tmp variable
}
    }

 

I think that could possibly get you what you want? I haven't tested it though...

 

Denno

[bayswater]

Thanks Denno, I used your approach but used CSS instead - it now looks exactly like I want it to. Thanks again!

[bayswater]

Hi Denno,

 

One follow-up question - if you're still about: If I want a div tag around ALL the:

 

if($tmp_sc != $sc_desc_dk){
echo "<br>- - <a href='view.php?id=".$sc_id."'>".$sc_desc_dk."</a><br>";
$tmp_sc = $sc_desc_dk; //update tmp variable
}

 

How would I do that?

 

Right now I get:

<div> - - something 1</div>

<div> - - something 2</div>

<div> - - something 3</div>

 

and all I need is:

<div>

- - something 1

- - something 2

- - something 3

</div>

 

[bayswater]

Hmmm maybe I got it:

 

I add:

$first_cat = 'true';

before the while (next to your empty variables)

 

Then:

if($tmp_cat != $cat_id){
echo "".(($first_cat != 'true') ? "</div>":"")."<div class='category'>".$cat_name_dk."</div><div class='screencasts'>";
$tmp_cat = $cat_id; //update tmp variable
$first_cat = "false";
}

 

And finally:

echo "</div>"; // CLOSE DIV AFTER LAST <div class='screencasts'>

- outside the WHILE end

 

Would you agree?