Jump to content


Photo

how do I join data?


  • Please log in to reply
7 replies to this topic

#1 MrLarkins.com

MrLarkins.com
  • Members
  • PipPip
  • Member
  • 20 posts

Posted 13 October 2006 - 03:56 PM

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.

#2 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 13 October 2006 - 04:03 PM

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

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

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#3 HuggieBear

HuggieBear
  • Members
  • PipPipPip
  • Advanced Member
  • 1,899 posts
  • LocationEngland, UK

Posted 13 October 2006 - 04:04 PM

Give this a try:

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

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

Regards
Huggie

Note: Barand beat me to it, and I'm not even sure mine would work, but they look pretty similar :)
Advice to MySQL users: Get phpMyAdmin and test your queries work there first, take half the hassle out of diagnosis, also check the reserved words list.

Links: PHP Docs :: RegEx's :: MySQL :: DevGuru :: w3schools

#4 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 13 October 2006 - 04:13 PM

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.
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#5 MrLarkins.com

MrLarkins.com
  • Members
  • PipPip
  • Member
  • 20 posts

Posted 13 October 2006 - 04:29 PM

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
while($main = mysql_fetch_array($main)){ //while you have records available from the SELECT query



#6 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 13 October 2006 - 04:35 PM

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
<?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");
}
?>

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#7 MrLarkins.com

MrLarkins.com
  • Members
  • PipPip
  • Member
  • 20 posts

Posted 13 October 2006 - 04:44 PM

hey, that works great...EXCEPT:

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

http://www.ice-squad...Commandlist.php

#8 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,021 posts

Posted 13 October 2006 - 06:23 PM

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
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users