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

okay i have this sql statement

PHP Code:

---

$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

PHP Code:

---

 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

PHP Code:

---

$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.

PHP Code:

---

    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.

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.

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.

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...

The schema is the structure of your database:

Code:

---

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:

Code:

---

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.

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

btw

PHP Code:

---

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

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

Code:

---

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:

Code:

---

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):

PHP Code:

---

$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:

PHP Code:

---

$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 Code:

---

<?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.)

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

just wont use smarty or PEAR

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