| linking tables and retrieving data
|
Mr_Cat
msg:4405212 | 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
msg:4405640 | 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.
|
|
|
