----- Original Message ----- 
From: "Rachel Rodriguez" <[EMAIL PROTECTED]>
To: <[EMAIL PROTECTED]>
Sent: Friday, April 23, 2004 3:55 AM
Subject: [PHP-DB] assigning variables after one-to-many query


> 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    | [EMAIL PROTECTED]|
> |   2    | [EMAIL PROTECTED] |
> |   3    | [EMAIL PROTECTED]  |
> |   4    | [EMAIL PROTECTED] |
> |   5    | [EMAIL PROTECTED] |
> +--------+-----------------+
>

maybe think again about your tables structure?
seems like a mess to me.

> 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 = [EMAIL PROTECTED],
> $email2 = [EMAIL PROTECTED], and $email3 =
> [EMAIL PROTECTED]


what i would do is add it into an array:

$emails = Array();
while ($row = mysql_fetch_array($result,MYSQL_NUM))
{
    $emails[] = $row[0];
}

then u have an array with :

$emails[0] = [EMAIL PROTECTED]
$emails[1] =  [EMAIL PROTECTED]
etc...


>
> 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
>
>
>

--
PHP Database Mailing List (http://www.php.net/)
To unsubscribe, visit: http://www.php.net/unsub.php

Reply via email to