Grabbing Data from 2 Databases

[InsaneRhino]

Hello all I need some help with some PHP and MySQL code im writing. Basically Im using phpbb forums and what I want to do is have a php page that gets the username of users from the phpbb_users table and then cross references them with those from the phpbb_user_group table. I only need usernames that are in a certain group. phpbb used IDs to see who should be in which group. Let me lay down an exanple:

phpbb_users:

user_id: 1
username: InsaneRhino

user_id: 2
username: Gill Bates

now phpbb_user_group looks like:

group_id: 3
user_id: 1

group_id: 3
user_id 2

etc, this puts user id 1 and 2 into group id 3 which is specified in the phpbb_groups table. So to clarify what I need to do is grab from the database all those that are in group_id 3, but I need to get both their username (field: username) and class (field: class). I then need the code to display this on a page. Here is what I have so far, and it seems to work, but it is only displaying 1 username, not all of them.

$link = mysql_connect( "localhost", "user", "pass" );
mysql_select_db( "db", $link );
$sql = mysql_query("SELECT * FROM phpbb_users u, phpbb_user_group ug WHERE u.user_id = ug.user_id && u.class = 'Mage' && ug.group_id = 3 ORDER BY u.username");
$mage_sql = mysql_query("SELECT * FROM phpbb_users u, phpbb_user_group ug WHERE u.user_id = ug.user_id && u.class = 'Mage' && ug.group_id = 3 ORDER BY u.username");
$hunter_sql = mysql_query("SELECT * FROM phpbb_users u, phpbb_user_group ug WHERE u.user_id = ug.user_id && u.class = 'Hunter' && ug.group_id = 3 ORDER BY u.username");
$priest_sql = mysql_query("SELECT u.username FROM phpbb_users u, phpbb_user_group ug WHERE u.user_id = ug.user_id && u.class = 'Priest' && ug.group_id = 3 ORDER BY u.username");
$warlock_sql = mysql_query("SELECT u.username FROM phpbb_users u, phpbb_user_group ug WHERE u.user_id = ug.user_id && u.class = 'Warlock' && ug.group_id = 3 ORDER BY u.username");
$warrior_sql = mysql_query("SELECT * FROM phpbb_users u, phpbb_user_group ug WHERE u.user_id = ug.user_id AND u.class = 'Warrior' && ug.group_id = 3 ORDER BY u.username");
$shaman_sql = mysql_query("SELECT u.username FROM phpbb_users u, phpbb_user_group ug WHERE u.user_id = ug.user_id && u.class = 'Shaman' && ug.group_id = 3 ORDER BY u.username");
$druid_sql = mysql_query("SELECT u.username FROM phpbb_users u, phpbb_user_group ug WHERE u.user_id = ug.user_id && u.class = 'Druid' && ug.group_id = 3 ORDER BY u.username");
$rogue_sql = mysql_query("SELECT * FROM phpbb_users u, phpbb_user_group ug WHERE u.user_id = ug.user_id AND u.class = 'Rogue' && ug.group_id = 3 ORDER BY u.username");

and then I have:

<?php
while( $row = mysql_fetch_array( $mage_sql ) ) {
Echo($row['username']);
Echo("<BR");

}
?>
which continues ovbviously changing &mage_sql for $hunter_sql or what not.

I really need help with this so a massive thank you to anyone who can help. Oh and I have another quick query. I have a news poster that inserts rows into a table, the problem is that when I put a " in the form it comes out as /" on the website, how can i fix this. Thanks

[Velox Letum]

For one, you can simplify your queries into a single query.

$sql = mysql_query("SELECT * FROM phpbb_users u, phpbb_user_group ug WHERE u.user_id = ug.user_id && ug.group_id = 3 ORDER BY u.username");

Then use PHP to sort the results.

<?php

// SQL stuff

while($row = mysql_fetch_assoc($sql)) {
switch ($row['class']) {
case "Mage":
echo 'Mage: ' . $row['username'];
break;
case "Hunter":
echo 'Hunter: ' . $row['username'];
break;
// And so on
}
}
?>

I believe the problem was your use of mysql_fetch_array, as it would retreive a numerical array instead of associative, but I'm not sure, so if it doesn't work let me know and we can work on that.

As for the escape slashes in the output, run stripslashes() (http://www.php.net/function.stripslashes) on the output before echoing it.

[InsaneRhino]

Thanks, the strislashes thing worked, i tried it before but i had it on the input of the form before it entered that databse, not on the output, so thats fixed. The problem is with the main part its not exactly liek you have it scripted, its a table with headings so there is some html injected between each php code, like this:

<tr align="left" valign="middle">
<td class="style7"><B>Shamans</B></td>
<td width="50%" align="left" class="style7"><B>Warriors</B></td>
</tr>
<tr>
<td width="50%" align="left" valign="top" class="style6">
<?php
while( $row = mysql_fetch_array( $shaman_sql ) ) {
Echo($row['username']);
Echo("<BR");

}
?></td>
<td width="50%" align="left" valign="top" class="style6">
<?php
while( $row = mysql_fetch_array( $warrior_sql ) ) {
Echo($row['username']);
Echo("<BR");

}
?><

I tried doing a while/switch thing for each area but it just come sup witht he titles in bold. Any clues?

[Velox Letum]

Add them to arrays, or just a string.

<?php

// SQL stuff

while($row = mysql_fetch_assoc($sql)) {
switch ($row['class']) {
case "Mage":
$mages .= $row['username'] . '<br />' . "\n";
break;
case "Hunter":
$hunters .= $row['username'] . '<br />' . "\n";
break;
// And so on
}
}
?>
<tr align="left" valign="middle">
<td class="style7"><B>Shamans</B></td>
<td width="50%" align="left" class="style7"><B>Warriors</B></td>
</tr>
<tr>
<td width="50%" align="left" valign="top" class="style6">
<? echo $shamans; ?></td>
<td width="50%" align="left" valign="top" class="style6">
<? echo $warriors; ?></td></tr>

Alternatively, you could add the names to an array for each "class" and then use foreach to output them, but this way is more simple.

[InsaneRhino]

I tried that and im still just getting titles

heres the code I have:

$sql = mysql_query("SELECT * FROM phpbb_users u, phpbb_user_group ug WHERE ug.group_id = 3 && u.user_id = ug.user_id ORDER BY u.username");


while($row = mysql_fetch_assoc($sql)) {
switch ($row['class']) {
case "Mage":
$mage .= $row['username'] . '<br />' . "\n";
break;
case "Hunter":
$hunter .= $row['username'] . '<br />' . "\n";
break;
case "Rogue":
$rogue .= $row['username'] . '<br />' . "\n";
break;
case "Warlock":
$warlock .= $row['username'] . '<br />' . "\n";
break;
case "Driud":
$druid .= $row['username'] . '<br />' . "\n";
break;
case "Priest":
$priest .= $row['username'] . '<br />' . "\n";
break;
case "Shaman":
$shaman .= $row['username'] . '<br />' . "\n";
break;
case "Warrior":
$warrior .= $row['username'] . '<br />' . "\n";
break;
}
}

and then

<?php

echo $mage;

//etc
?>

[InsaneRhino]

Oh I fixed it, i think the problems was that i had the MySQL field of 'class' set to varchar (duh) I changed it to text and it worked fine. Thanks! Massive help you have been!

[Velox Letum]

Odd...varchar should still work. Glad it works now though.