[SOLVED] MySQL query to pull usernames based on user_id

[drummer101]

Well, the code for selecting the users part of group 6 works correctly, my next step from here is to then fetch the username attached to the user_id

 

I'm a bit stumped as to the last part. If there is a way to condense both sql queries into one that would be the preferred route, but I couldn't quite figure out how to get it to work with UNION.

 

Hopefully one of you guys can offer some insight?

 

<?php $result=mysql_query("SELECT `user_id` FROM `phpbb_user_group` WHERE `group_id` = 6");
while ($i = mysql_fetch_array($result))
{
echo "<p>{$i['user_id']}</p>";
}
?>

 

<?php $names=mysql_query("SELECT `username` from `phpbb_users` WHERE `user_id` = {$i['user_id']}");
while ($roster = mysql_fetch_array($names))
{
echo "{$roster['username']}";
} ?>

 

WHERE `user_id` = {$i['user_id']}

is the problem area in the code, specifically $i['user_id'], the problem I'm having is I can't figure out a way to take the previous array results (user_id) and insert them into the SQL query.

 

SQL doesn't recognize arrays, much less a php array, so the question is, how do I pass the fetched data, into the sql query.

 

Thanks so much.

[kjtocool]

Couldn't you do something like:

 

SELECT phpbb_user_group.user_id, phpbb_users.username

FROM phpbb_user_group, phpbb_users

WHERE phpbb_user_group.group_id = 6

AND phpbb_users.user_id = {$i[user_id]}

[drummer101]

Couldn't you do something like:

 

SELECT phpbb_user_group.user_id, phpbb_users.username

FROM phpbb_user_group, phpbb_users

WHERE phpbb_user_group.group_id = 6

AND phpbb_users.user_id = {$i[user_id]}

 

Thanks so much, it's a start, now the array prints out the same name repeatedly (13 times for each member of the group actually). I can't quite figure out why, as there are no identical user_id's...

 

Any thoughts?

[kjtocool]

First off, your using mysql_ functions, which I am not familiar with.  I tend to use the updated mysqli_ functions.

 

If I were using those, I would try the following:

 

<?php
$databaseConnection = mysqli_connect("localhost", "username", "password", "database")
			Or die("Unable to connect to the database.");

$query = "SELECT phpbb_user_group.user_id, phpbb_users.username FROM phpbb_user_group, phpbb_users WHERE phpbb_user_group.group_id = 6 AND phpbb_users.user_id = phpbb_user_group.user_id";

$result = mysqli_query($databaseConnect, $query);
$row = mysqli_fetch_assoc($result);

while ($row)
{
$username = $row['username'];
$user_id = $row['user_id'];
echo "Username = " . $username . " - User_ID = " . $user_id;
$row = mysqli_fetch_assoc($result);
}
?>

[drummer101]

Hey, awesome thanks so much. Tweaked that for sql and what do you know, it worked

I think

AND phpbb_users.user_id = phpbb_user_group.user_id

was the part I needed.