displaying results from two tables as seperate blocks

[Kiint]

This is probably basic coding for most of you but I've been trying to find out how to do this.

I have 2 tables of data,

the first table "ranks" has 3 columns, Rank_id, Rank_desc, Rank_order
the second table "roster" has many columns but I only want to display a few of them.
roster_id, member_name, roster_rank.

it's a one to many relationship as each rank can have many members. so ranks.Rank_id and roster.roster_rank are the linking primary keys

I need a query which will select each rank and all the members that have that rank and display each rank and members in a seperate block in the template, so that the results on the page would show like this:

Rank officers
bob
harry
john

Rank corporals
mike
sally
peter

Rank Private
Tom
Rich
Harry

Could someone please let me know how to do this? I've spent several hours looking up code and can't find the solution to this.

[kh99]

You could do something like this:

$result = $vbulletin->db->query_read_slave("SELECT ranks.*, roster.roster_id, roster.member_name, roster.roster_rank
                                               FROM roster LEFT JOIN ranks ON(ranks.Rank_id = roster.roster_rank)
                                               ORDER BY Rank_order ASC, member_name ASC");

$roster = array();
while ($row = $vbulletin->db->fetch_array($result))
{
    $roster[$row['Rank_id']][] = $row;
}

Then you'd register $roster to your template (or use preRegister if it's an existing template), and in the template you can use <vb:each> to format the blocks, like:

<vb:each from="roster" value="members">
{vb:var members.0.Rank_desc}<br />
    <vb:each from="members" value="member">
{vb:var member.name}<br />
    </vb:each>
<br />
</vb:each>

[Kiint]

Many many thanks.

I manged to adapt your code to fit my existing template and now have a dynamic list of ranks instead of a manual hard coded set of ranks which I manually had to add code whenever I added or deleted a rank.
I did have one problem and that was when I created the array I used 'Rank_id' instead of 'rank_id' ...took me quite a while to trace that

The end result is here:
http://www.rpgguild.co.uk/vbforum/roster.php

--------

One extra question, On the old hard coded version, whenever I had members which were ranked as "inactive" (rank_id 9) I used to have this condition above the block of code so that only admins could see it:

<vb:if condition="is_member_of($vbulletin->userinfo,5,6,11,12)">
<h1 class="blockhead stat_home">Inactive - Only admins can see this</h1>

and this would hide that rank from being displayed.

Is there a way that I can add this into the <vb:each> so that when it detects an admin it is displayed and not displayed when a registered/non-registered members views the page?

Thanks again for your help, this has helped so much.

[kh99]

Originally Posted by Kiint

I did have one problem and that was when I created the array I used 'Rank_id' instead of 'rank_id' ...took me quite a while to trace that

I thought the column name was 'Rank_id'. Oh well, I'm glad you were able to get it working because I didn't actually try any of that code.

One extra question, On the old hard coded version, whenever I had members which were ranked as "inactive" (rank_id 9) I used to have this condition above the block of code so that only admins could see it:

<vb:if condition="is_member_of($vbulletin->userinfo,5,6,11,12)">
<h1 class="blockhead stat_home">Inactive - Only admins can see this</h1>

and this would hide that rank from being displayed.

Is there a way that I can add this into the <vb:each> so that when it detects an admin it is displayed and not displayed when a registered/non-registered members views the page?

Thanks again for your help, this has helped so much.

I guess it would be something like:

<vb:each from="roster" value="members">
<vb:if condition="$members[0].Rank_id != 9 OR is_member_of($vbulletin->userinfo,5,6,11,12)">
<vb:if condition="$members[0].Rank_id == 9>
<h1 class="blockhead stat_home">Inactive - Only admins can see this</h1>
<vb:else />
{vb:var members.0.Rank_desc}<br />
</vb:if>
    <vb:each from="members" value="member">
{vb:var member.name}<br />
    </vb:each>
<br />
</vb:if>
</vb:each>

[Kiint]

Thanks again for this.

I had to slightly modify the IF conditional to this:

<vb:if condition="$members[0]['rank_id'] != 9">

as for some reason it would not assign the value to that variable.

However I couldn't get the 2 IF's to work together, I basically had to do the following to make it work...not great I know.....

<vb:each from="roster" value="members">
<vb:if condition="$members[0]['rank_id'] != 9">

  (code here)

</vb:if>

<vb:if condition="$members[0]['rank_id'] == 9 AND is_member_of($vbulletin->userinfo,5,6,11,12)">

   (same code here)

</vb:if>
</vb:each>

This worked, but it doesn't look nice

Thanks again, you've saved me a LOT of work.

[kh99]

Originally Posted by Kiint

Thanks again for this.

I had to slightly modify the IF conditional to this:

<vb:if condition="$members[0]['rank_id'] != 9">

as for some reason it would not assign the value to that variable.

Sorry, that was my fault - you couldn't get it to work because what I posted was wrong.

Anyway, what you did looks OK to me. Of course there are lots of ways to achieve the same thing, and since I didn't test what I posted it could have been wrong.

[Kiint]

Originally Posted by kh99

Sorry, that was my fault - you couldn't get it to work because what I posted was wrong.

Anyway, what you did looks OK to me. Of course there are lots of ways to achieve the same thing, and since I didn't test what I posted it could have been wrong.

No worries, it all worked out in the end.

Now the work begins, It's now working the way I want, but now to make it look better

Thanks for all the help you've given me. Very much appreciated.