[RESOLVED] output explode()/array() problem[For Lack of Better term]

[PlaGuE]

okay i have this sql statement

$sql = <<<EOS
  SELECT f.id as id, f.forum_cat_id as cid, f.forum_name as forum, 
    f.forum_desc as description,
    count(forum_id) as threads,  u.username as mod, u.id as uid
  FROM FORUM_TABLE f
  LEFT JOIN FORUM_TABLE_POSTS p
  ON f.id = p.forum_id
  AND p.topic_id=0
   AND f.id = p.forum_id
  LEFT JOIN FORUM_MEMBERS_TABLE u
  ON f.forum_moderator = u.id
  WHERE f.forum_cat_id = $cat_row[id] 
  GROUP BY f.id
EOS; 

for a forum im making.

Now it CAN do all i want it to do... but i cant seem to figure out how im going to do it so that with the moderators.

i want to do multiple moderators

instead of ONE MODERATOR per forum.

so instead of

Chit-Chat :: Mod: PlaGuE
Its
Chit-Chat :: Mod: PlaGuE Da_Jordan Guy

it seems that that sql quesry statement only picks the first

 u.id that equals  f.forum_moderator 

as my input in the db for moderator of one forum is.
2, 1, 3

i thought maybe

$moderators = explode(", ", $row['mod']);
foreach($moderators as $mods) {
$mod = $mods;
} 

would solve the problem. But it only outputs ONE name/id.

i know im doing something wrong...


i think its cuz im only shoting it once.

this is the place where it outputs.

    echo"<tr height='40px'>
    <td class='forum_bg'><a href='viewforum.php?f=" .$row['id'] . "'>" . $row['forum'] . "</a><br>
    <font size='2px'>" . $row['description']."<br></font></td>
   <td  class='thread_bg'><div align=center>" . $row['threads']."</div></td>
   <td  class='thread_bg'><div align=center>".$mod."</div></td>
  </tr>"; 

How would i go about making it almost "loop" after the foreach. Or w/e. so that it returns the correct amount of moderators.

Im not used to doing things like this.

[CornedBee]

Why do you query moderators and posts in the same query? (On second thought, never mind that. I see why.)

Post your database schema. From what I've gleaned from the query, your database schema simply doesn't support multiple moderators per forum.

[PlaGuE]

schema?

sorry never used that term before.

the moderator_colomn was TINYINT(4)

i changed it to TEXT... because idk... i thought it would allow "multiple" modsas in.

forum_moderator = 1, 5, 9

which i just now changed back to tinyint(4)
still if i knew what schema is.. i could help better.

[PlaGuE]

what should i do in order to "allow" multiple moderators?

how should i go about it.

I've never done something like this before.... sooo... meh...

[CornedBee]

The schema is the structure of your database:

TABLE forums {
  id INT PRIMARY KEY,
  name STRING
}

The format I just made up, it doesn't really matter.

My point is that "moderators for forums" is an N:M relation: a forum can have multiple moderators, each moderator can moderate multiple forums. You always need a separate connection table for such relations:

CREATE TABLE forum_moderators (
  forum_id INT FOREIGN KEY REFERENCES(forums),
  user_id INT FOREIGN KEY REFERENCES(users),
  PRIMARY KEY(forum_id, user_id)
);

You ought to learn a bit database theory.

[PlaGuE]

yeah... i should... know where i can learn it.

btw

CREATE TABLE `forum_forum` (
  `id` int(11) NOT NULL auto_increment,
  `forum_cat_id` mediumint(11) NOT NULL default '0',
  `forum_name` varchar(100) NOT NULL default '',
  `forum_desc` varchar(255) NOT NULL default '',
  `forum_moderator` tinyint(4) NOT NULL default '0',
  `admin_only` tinyint(4) NOT NULL default '0',
  PRIMARY KEY  (`id`)
) TYPE=MyISAM AUTO_INCREMENT=24 ; 

hmm how would i go about using that lil "example" you showed me... it looks about right.

I've only ever done stuff that i know.
I am still learning.
I i still have alot to learn

[CornedBee]

Well, here's a simple query that retrieves all moderators for a forum with a given ID:

SELECT users.*
FROM users INNER JOIN forum_moderators
  ON users.id = forum_moderators.user_id
WHERE forum_moderators.forum_id = ?

It's not possible to integrate this into a single query getting all the forums without unpleasant duplication of the forum data. It's possible, however, to fetch the moderators for all forums in a single query:

SELECT forum_moderators.forum_id AS forum, users.id as user_id, users.name as username
FROM users INNER JOIN forum_moderators ON users.id = forum_moderators.user_id

Then you can use PHP code like to create an array where you can look up moderators based on the forum ID (assuming the above is in $sql, and still approximately in PEAR DB syntax):

$result =& $connection->query($sql);
$moderators = array();
while($row = $result->fetch_assoc()) {
  $moderators[$row['forum_id'][] = array('id' => $row['user_id'], 'name' => $row['username']);
} 

Finally, if you use a query to fetch every forum in an array:

$sql = 'SELECT * FROM forums';
$result = $connection->query($sql);
$forums = array();
while($row = $result->fetch_assoc()) {
  $forums[] = $row;
} 

You can use this in the markup-emitting part like thus:

<?php
foreach($forums as $forum) {
?>
<div class="forum">
  <span class="name"><?php echo $forum['name']; ?></span>
  <span class="description"><?php echo $forum['description']; ?></span>
  <span class="mods">Moderators:
  <?php
    foreach($moderators[$forum['id']] as $mod) {
      ?>
      <a href="userinfo.php?id=<?php echo $mod['id']; ?>"><?php echo $mod['name']; ?></a>
      <?php
    }
  ?></span>
<?php
}

(Of course, I would actually use Smarty for the last.)

[PlaGuE]

hmm when my connection stops f'n up.. ill play with that.

just wont use smarty or PEAR

[PlaGuE]

my only problem is with the duplication-of-the-info-per-category.

but i think i can solve that easily.

if not.. i post back here for help.hehe.

Live to learn