[SOLVED] MySQL Query from multiple tables.

[Bakes]

If I have posted this in the wrong forum, sorry, but I don't know where it should go.

 

In my database, I have 3 tables (lets call them table1, table2, table3)

 

table1 is the master, it carries a list of groups.

table2 contains a list of subgroups, and what group in table1 they correspond to.

table3 contains a list of users in those subgroups, and what group in table2 they correspond to, and values.

 

What PHP code could I use to grab a list of users, who are in a subgroup that is in the specified group, and print the field 'val1' ?

[premiso]

mysql, mysql_query mysql_fetch_assoc

 

I take it you know how to make sql statements. If not take that part to the MySQL Help forum on this site and they can help you. Read through the user manual for how to properly utilize those functions to pull data from a MySQL database.

[Bakes]

My problem is, I can get my subgroups from the group, but when I try to get the users, it tries to get the user with the id that is all the subusers mashed together:

 

 

group:

28775

subuser (how it's meant to be: 3233872)

what it actually looks like:

323387233332323396039284600234158203434083345376134590763511262351176913318253034876352600435292733531242353506435357573537629353798835453842638183355005535508763551041355695635624613570907293388135742763575091238580035784513579965358146535879343590674359155535915693592206359280035954932605850359776836014463601629360486536052663605301361609136167893204525339558536260953626746174588168788736322513633235363858536427293623542361582334985643657448367051116397253669713373182437361563737714375143637687883777633378874737929313857833373204338874363367095286462730528843921637399409940137454148507413207438605614205469207686328991024385422136287326992744035991

 

how would I query for individual items, not for them all mashed together?

[Ayon]

this should probably be in the MySQL Help forum.. but a mod can kick it over there if it's possible

 

try something like "SELECT subgroup FROM table WHERE group='groupid'"

 

kinda hard to to know for sure since you haven't provided any code...

[Bakes]

I was thinking of sticking it there, but I decided against it, for the reason that it's my php that doesn't work, not my SQL syntax.

 

My code is:

 

$triberank = "SELECT id
        FROM groups
        WHERE rank = 20";

$group = mysql_query($triberank);

while ($groupid = mysql_fetch_assoc($group)){
    $groupided = $groupid['id'];
    echo $groupided;
    echo "";
}
$subgrouprank = "SELECT id
     FROM subgroups
     WHERE group = $groupided";

$subgroup = mysql_query($subgrouprank);
while (list($subgroups) = mysql_fetch_row($subgroup)){
  echo $subgroups;
}

$users = "SELECT data1, data2
     FROM users
     WHERE level = 20 AND subgroup = $subgroups";

echo "<table>";
$user = mysql_query($users);
while (list($data1, $data2) = mysql_fetch_row($user)){
echo "
<tr>
<td>$data1</td>
<td>$data2</td>
</tr>";
}
echo "</table>";

[Ayon]

maybe this is what you're looking for?

while ($groupid = mysql_fetch_assoc($group)){
    $groupided = $groupid['id'];
    echo "$groupided<br />";
}

while (list($subgroups) = mysql_fetch_row($subgroup)){
  echo "$subgroups<br />";
}

[Bakes]

Not as far as I know;

 

that would just print a load of crap, where I actually need to use the result in another query.

[Bakes]

ie; That would print the ids out, but I need to do an sql query based on id's in that.

[Ayon]

well... can you enter the query you need or at least something close to it?

[xylex]

Looking from what I think you're trying to do, your error is in

 

$subgroup = mysql_query($subgrouprank);
while (list($subgroups) = mysql_fetch_row($subgroup)){
  echo $subgroups;
}

$users = "SELECT data1, data2
     FROM users
     WHERE level = 20 AND subgroup = $subgroups";

 

Would work better as

 

$subgroups = array();
$subgroup = mysql_query($subgrouprank);
while (mysql_fetch_row($subgroup){
  $subgroups[] = $subgroup;
}

$subgroups = implode(', ' , $subgroups);

$users = "SELECT data1, data2
     FROM users
     WHERE level = 20 AND subgroup IN ($subgroups);
"

 

 

 

But why aren't you just doing a join on this to do it in one query

 

$sql = '
SELECT data1, data2 FROM users 
  JOIN subgroups ON
  (subgroups.id = users.subgroup) 
  JOIN groups ON
  (groups.id = subgroups.group)
WHERE user.level = 20 AND groups.rank = 20
';