Topic: How get in one query which forum(s)are moderated by which moderator(s)

Hi,

I 'm doing a mod to show up in one page all the moderators. I want to display for each which forums is in charge of. Easy to do with one query by moderator, but as I have 29 is a bit ugly ...
Do you have any idea how I can do it with only one query ?

Tx

Re: How get in one query which forum(s)are moderated by which moderator(s)

Well, it's all in the forums table right? You could do a join with the users table (I'm not sure, I haven't really studied the forum table wink)

Re: How get in one query which forum(s)are moderated by which moderator(s)

Probably but is not as so simple ... have a look on the table forums and the field moderators ....

Re: How get in one query which forum(s)are moderated by which moderator(s)

So no idea? No other solutions to use a cache file?

5 (edited by Smartys 2006-05-31 01:32)

Re: How get in one query which forum(s)are moderated by which moderator(s)

Err, what do you want to do? Get a list of users and which forums they moderate? Or get a list of forums and which users moderate them?

Re: How get in one query which forum(s)are moderated by which moderator(s)

Get a list of users and which forums they moderate !

Re: How get in one query which forum(s)are moderated by which moderator(s)

The easiest way (not most efficent) might be something like this:
Get all forum names/moderators (and I mean the columns). Unserialize, store all data in an array
Query all moderators from the users table. Loop through for each row, use a foreach to check if the user is a moderator in the forum

Re: How get in one query which forum(s)are moderated by which moderator(s)

Yeah i was thinking too something like that, but doesn't efficient ...
Anyway thanks, I should be able to do something now ...

Re: How get in one query which forum(s)are moderated by which moderator(s)

Well, I found it was quite easy =P

This is the code I used (boxed with a nice header):

        <?php
        $result = $db->query("SELECT id, forum_name, moderators FROM ".$db->prefix."forums WHERE moderators IS NOT NULL");
        while($row = $db->fetch_assoc($result))
        {
            echo '<div class="box">', '<h2><a href="'.PUN_ROOT.'viewforum.php?id='.$row['id'].'">'.$row['forum_name'].'</a></h2>';
            $mods = unserialize($row['moderators']);
            foreach($mods as $key => $value)
                echo '<a href="'.PUN_ROOT.'profile.php?id='.$value.'">'.$key.'</a>, ';
            echo '</div>', '<br />';
        }
?>

10 (edited by Smartys 2006-05-31 17:21)

Re: How get in one query which forum(s)are moderated by which moderator(s)

Err, Bekko: there's a reason I did it my way tongue

He said that he doesn't want

Forum 1
- User A
- User B

Forum 2
- User C
- User A

which is what your thing does

He wants

User A
- Forum 1
- Forum 2

User B
- Forum 1

User C
- Forum 2

Re: How get in one query which forum(s)are moderated by which moderator(s)

Ah, like that tongue

Gotta think extra on that to do it easily ^^

Re: How get in one query which forum(s)are moderated by which moderator(s)

elbekko wrote:

Ah, like that tongue

Gotta think extra on that to do it easily ^^

There IS no way to do it easily tongue

Re: How get in one query which forum(s)are moderated by which moderator(s)

No news ? Ok I do something this WE , hopefully smile

Re: How get in one query which forum(s)are moderated by which moderator(s)

Bah, I'll write some better code tongue

15 (edited by fpouget 2006-06-02 21:13)

Re: How get in one query which forum(s)are moderated by which moderator(s)

do it now so lol !

Re: How get in one query which forum(s)are moderated by which moderator(s)

<?php
define('PUN_ROOT', './');
require PUN_ROOT.'include/common.php';

// Get the array the right way!
$mod_users = array();
$result = $db->query("SELECT id, forum_name, moderators FROM ".$db->prefix."forums WHERE moderators IS NOT NULL")  or error('Unable to fetch forum info', __FILE__, __LINE__, $db->error());;
while ($cur_forum = $db->fetch_assoc($result))
{
    $cur_forum['moderators'] = unserialize($cur_forum['moderators']);
    
    foreach ($cur_forum['moderators'] as $mod_name => $mod_id)
    {
        $mod_users[$mod_name][$cur_forum['id']] = $cur_forum['forum_name'];
    }
}

require PUN_ROOT.'header.php';
?>
<div class="blockform">
    <h2><span>Moderator List</span></h2>
    <div class="box">
    <form method="post" action="thisisfake.php">
<?php
foreach ($mod_users as $mod_name => $forums)
{
?>
        <div class="inform">
            <fieldset>
                <legend><?php echo $mod_name ?></legend>
                <div class="infldset">
<?php
foreach ($forums as $forum_id => $forum_name)
{
?>
                    <label><a href="viewforum.php?id=<?php echo $forum_id ?>"><?php echo $forum_name ?></a></label>
<?php
}
?>
                </div>
            </fieldset>
        </div>
<?php
}
?>
    </form>
    </div>
</div>
<?php
require PUN_ROOT.'footer.php';

Ugly? Yes. Using a form that doesn't go anywhere? Yes. Working properly? Yes. tongue

17 (edited by fpouget 2006-06-02 21:51)

Re: How get in one query which forum(s)are moderated by which moderator(s)

So quick !!! or done before ????

Re: How get in one query which forum(s)are moderated by which moderator(s)

I started coding it after I made that post tongue

Re: How get in one query which forum(s)are moderated by which moderator(s)

let's me check if it works ....

20 (edited by fpouget 2006-06-02 22:00)

Re: How get in one query which forum(s)are moderated by which moderator(s)

Well done !!!!

Thanks smile