"Rachel Rodriguez" <email2rachel2003@xxxxxxxxx> wrote in message news:20040423015539.12050.qmail@xxxxxxxxxxxxxxxxxxxxxxxxxx > 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@xxxxxxxxxxx| > | 2 | diana@xxxxxxxxx | > | 3 | bill@xxxxxxxxx | > | 4 | bill@xxxxxxxxxx | > | 5 | vic@xxxxxxxxxxx | > +--------+-----------------+ > 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@xxxxxxxxxxx, > $email2 = bill@xxxxxxxxxx, and $email3 = > bill@xxxxxxxxxx > > 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 -- PHP Database Mailing List (http://www.php.net/) To unsubscribe, visit: http://www.php.net/unsub.php
