Re: assigning variables after one-to-many query

"Rachel Rodriguez" <email2rachel2003@yahoo.com> wrote in message news:20040423015539.12050.qmail@web41805.mail.yahoo.com... > Hi! > > I have a one-to-many relationship between two tables > (table1 and table3) with a "linking" table between > them (table2): > > table1: table2 > +---+--------+ +---+--------+ > |id | f_name | |id | emailID| > +---+--------+ +---+--------+ > | 1 | bill | | 1 | 1 | > | 2 | john | | 1 | 4 | > | 3 | diana | | 1 | 3 | > | 4 | victor | | 2 | 2 | > | 5 | renata | | 4 | 5 | > +---+--------+ +---+--------+ > > table3 > +--------+-----------------+ > |emailID | email | > +--------+-----------------+ > | 1 | bill@hotmail.com| > | 2 | diana@yahoo.com | > | 3 | bill@yahoo.com | > | 4 | bill@excite.com | > | 5 | vic@hotmail.com | > +--------+-----------------+ > Hi Rachel, if you know that you will always have a one to many relationship and never a many to many relationship you don't need table2 at all. Just add column 'id' to table three as the foreign key. This should make your life easier. Regards, Torsten > I would like to write a query that matches table1.id > with records > from table3.emailID via the linking table (table2) and > then > assign each match to a variable. > > Here is what I have: > > $query = "SELECT t3.email > FROM table3 AS t3 > LEFT JOIN table2 AS t2 > ON (t3.emailID = t2.emailID) > LEFT JOIN table1 AS t1 > ON (t2.id = t1.id) > WHERE t1.id = 1"; > > $result = @myql_query($query, $db_connection); > > $num = mysql_num_rows($result); > > $email1 = ""; > $email2 = ""; > $email3 = ""; > > if ($num > 0) > { > while ($row = mysql_fetch_array($result)) > { > // do something here to assign > // $email1 = $row[email] and > // $email2 = $row[email], etc. > } > } > > Of course, the problem I am having is as I am going > through the "while" loop, I am assigning $email1, > $email2, and so on the > > same e-mail address. > > I would like to get: $email1 = bill@hotmail.com, > $email2 = bill@excite.com, and $email3 = > bill@yahoo.com. > > I would prefer to do that rather than what I have seen > in most examples which is similar to the following: > > // snippet of code > > while ($row = mysql_fetch_array($result)) > { > echo"Bill's email is $row[email]."; > } > > // end of snippet > > Any assistance is greatly appreciated. > > Thanks, > Rachel > > > > > __________________________________ > Do you Yahoo!? > Yahoo! Photos: High-quality 4x6 digital prints for 25¢ > http://photos.yahoo.com/ph/print_splash