Jump to content

Archived

This topic is now archived and is closed to further replies.

MrLarkins.com

how do I join data?

Recommended Posts

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.

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
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.

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
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]

Share this post


Link to post
Share on other sites
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

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.