[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