how do I join data?

[MrLarkins.com]

I have data from 3 different tables to join.

table 1 has id, name, email
table 2 has icq_number
table 3 has field_1,field_2,field_3

here is what i have so far:
$main = mysql_query("SELECT id, name, email, joined, bday_day, bday_month, bday_year FROM ibf_members WHERE mgroup=4 ORDER BY id");
while($main = mysql_fetch_array($main)){ //there are 5 members in this group
$id = $main['id'];
$name = $main['name'];
$email = $main['email'];

$icq = mysql_query("SELECT icq_number FROM ibf_member_extra WHERE id = $id");
$icq_number = $icq['icq_number'];

$extra_two = mysql_query("SELECT field_1, field_2, field_3, field_4 FROM ibf_pfields_content WHERE member_id = $id");
$stats = $extra_two['field_1'];
$character = $extra_two['field_2'];
$country = $extra_two['field_3'];

//do some html stuff to display
}

this code is wrong, but i have no clue how to join data together.  please help.

[Barand]

You join on the common data fields, in this case member_id

[code]SELECT m.id, m.name, m.email, m.joined, m.bday_day, m.bday_month,
    m.bday_year, x.icq_number, c.field_1, c.field_2, c.field_3, c.field_4
FROM ibf_members AS m
INNER JOIN ibf_member_extra AS x ON m.id = x.id
INNER JOIN ibf_pfields_content AS c ON m.id = c.member_id
WHERE mgroup=4
ORDER BY id[/code]

[HuggieBear]

Give this a try:

[code]SELECT m.id, m.name, m.email, m.joined, m.bday_day, m.bday_month, m.bday_year, i.icq_number, c.field_1, c.field_2, c.field_3, c.field_4
FROM ibf_members m, ibf_member_extra i, ibf_pfields_content c
WHERE m.id = i.id
AND i.id = c.id
AND m.mgroup = 4
ORDER BY m.id[/code]

Echo all the results to see if it's what you expect, then go about your formatting.

Regards
Huggie

[size=8pt][color=red][b]Note:[/b][/color] Barand beat me to it, and I'm not even sure mine would work, but they look pretty similar :)[/size]

[Barand]

Looks OK Huggie, alternative syntax.

I prefer to specify the joins, showing the structure of the query, separate from the the search criteria in the WHERE clause. I think it's a lot easier to see what's going on on and helps to ensure the joins are specified.

[MrLarkins.com]

i tried this

$main = mysql_query("SELECT m.id, m.name, m.email, m.joined, m.bday_day, m.bday_month,
    m.bday_year, x.icq_number, c.field_1, c.field_2, c.field_3, c.field_4
FROM ibf_members AS m
INNER JOIN ibf_member_extra AS x ON m.id = x.id
INNER JOIN ibf_pfields_content AS c ON m.id = c.member_id
WHERE mgroup=4
ORDER BY id");
while($main = mysql_fetch_array($main)){ //while you have records available from the SELECT query
$id = $main['m.id'];
$name = $main['m.name'];
$email = $main['m.email'];
$join_date = $main['m.joined'];
$bday_day = $main['m.bday_day'];     
$bday_month = $main['m.bday_month'];
$bday_year = $main['m.bday_year'];   
$icq = $main['x.icq_number'];
$stats = $main['c.field_1'];
$character = $main['c.field_2'];
$country = $main['c.field_3'];
$state = $main['c.field_4'];

echo ("$id = $name = $email = $icq = $stats");

i got 4 equal signs (nodata) and an error message referring to[code]while($main = mysql_fetch_array($main)){ //while you have records available from the SELECT query[/code]

[Barand]

A couple of things. You use the same variable for the row data as for the query result thus overwriting one with the other.

Also, the column names should be reference without the table aliases in the array.

Try [code]
<?php
$main_result = mysql_query("SELECT m.id, m.name, m.email, m.joined, m.bday_day, m.bday_month,
    m.bday_year, x.icq_number, c.field_1, c.field_2, c.field_3, c.field_4
FROM ibf_members AS m
INNER JOIN ibf_member_extra AS x ON m.id = x.id
INNER JOIN ibf_pfields_content AS c ON m.id = c.member_id
WHERE mgroup=4
ORDER BY id");
while($main = mysql_fetch_array($main_result)) { //while you have records available from the SELECT query
    $id = $main['id'];
    $name = $main['name'];
    $email = $main['email'];
    $join_date = $main['joined'];
    $bday_day = $main['bday_day'];     
    $bday_month = $main['bday_month'];
    $bday_year = $main['bday_year'];   
    $icq = $main['icq_number'];
    $stats = $main['field_1'];
    $character = $main['field_2'];
    $country = $main['field_3'];
    $state = $main['field_4'];

    echo ("$id = $name = $email = $icq = $stats");
}
?>
[/code]

[MrLarkins.com]

hey, that works great...EXCEPT:

there are five members of mgroup=4, but only the last 3 show up...

[url=http://www.ice-squad.com/Commandlist.php]http://www.ice-squad.com/Commandlist.php[/url]

[Barand]

When using INNER JOIN only records which match are shown.

If there may not be records on the member_extra or pfields tables then change the join types from "INNER" to "LEFT" to see all members in the selected group