linking tables and retrieving data

[Mr_Cat]

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]

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.