homepage Welcome to WebmasterWorld Guest from 54.237.213.31
register, free tools, login, search, pro membership, help, library, announcements, recent posts, open posts,
Pubcon Platinum Sponsor 2014
Home / Forums Index / Code, Content, and Presentation / Databases
Forum Library, Charter, Moderator: open

Databases Forum

    
linking tables and retrieving data
Mr_Cat

5+ Year Member



 
Msg#: 4405210 posted 2:30 pm on Jan 9, 2012 (gmt 0)

Hi folks,

I'm totally new to SQL and my php is't fabulous but I'm doing my best to learn :)

My problem is this. For simplicity and my current problem we'll say I have three tables in my database; one called 'men' with the columns 'ID' and 'name', the second table is called 'women' and also has columns 'ID' and 'name', and the third table is a linking table called 'pairs' with columns 'ID', 'men_ID' and 'women_ID'.

I need to retrieve the data and list all the entries according to ID numbers.

Here is what I've tried to no avail;

$query = "SELECT pairs.ID, men.name, women.name";
$query.= " FROM women INNER JOIN (men INNER JOIN pairs ON men.ID = pairs.men_ID) ON women.ID = pairs.women_ID";

// We could add the following too to pick out a solo entry or other such WHERE condition but right now we want them all
// $query .=" WHERE (pairs.ID=1)";

$result = mysql_query($query) or die(mysql_error());

if ($row = mysql_fetch_array($result)) {

do {
$pairs_id = $row['pairs.ID'];
$personA_Name = $row['men.name'];
$personB_Name = $row['women.name'];

echo "Pair - " . $personA_Name . " meets " . $personB_Name . "<BR>";
} while($row = mysql_fetch_array($result));
}

My friend helped me with this bit of code by using MS Access to create the query with the appropriate JOINS but I just get a long list of 'Underfined Index' errors, 3 for each row pointing to the 3 lines in the 'do' statement?

Any ideas?

 

Mr_Cat

5+ Year Member



 
Msg#: 4405210 posted 6:39 pm on Jan 10, 2012 (gmt 0)

Hi folks,

Problem solved, I came accross this post [webmasterworld.com...]

I changed my field names to make them all unique and changed my code to the following which works fine.

$query = "SELECT p_ID, m_name, w_name FROM pairs, men, women";
$query.= " WHERE men.m_ID = pairs.men_ID AND women.w_ID = pairs.women_ID";

$result = mysql_query($query) or die(mysql_error());

if ($row = mysql_fetch_array($result)) {

do {
$pairs_id = $row['p_ID'];
$personA_Name = $row['m_name'];
$personB_Name = $row['w_name'];

echo "Pair - " . $personA_Name . " meets " . $personB_Name . "<BR>";
} while($row = mysql_fetch_array($result));
}


It may not be as elegant as using JOIN, or maybe it is, I'm not sure :) but I thought I ought to post that it works.

I don't know why the first code snippet didn't work but it was something to do with the ['tablename.fieldname'] syntax in the 'do' statement.

Global Options:
 top home search open messages active posts  
 

Home / Forums Index / Code, Content, and Presentation / Databases
rss feed

All trademarks and copyrights held by respective owners. Member comments are owned by the poster.
Home ¦ Free Tools ¦ Terms of Service ¦ Privacy Policy ¦ Report Problem ¦ About ¦ Library ¦ Newsletter
WebmasterWorld is a Developer Shed Community owned by Jim Boykin.
© Webmaster World 1996-2014 all rights reserved