PHP DB - Re: [PHP-DB] assigning variables after one-to-many query

Author

Re: [PHP-DB] assigning variables after one-to-many query

Uzi Klein

2004-04-23, 9:31 am

----- Original Message -----=20
From: "Rachel Rodriguez" <email2rachel2003@yahoo.com>
To: <php-db@lists.php.net>
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 | bill@hotmail.com|
> | 2 | diana@yahoo.com |
> | 3 | bill@yahoo.com |
> | 4 | bill@excite.com |
> | 5 | vic@hotmail.com |
> +--------+-----------------+
>

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 =3D "SELECT t3.email
> FROM table3 AS t3
> LEFT JOIN table2 AS t2
> ON (t3.emailID =3D t2.emailID)
> LEFT JOIN table1 AS t1
> ON (t2.id =3D t1.id)
> WHERE t1.id =3D 1";
>
> $result =3D @myql_query($query, $db_connection);
>
> $num =3D mysql_num_rows($result);
>
> $email1 =3D "";
> $email2 =3D "";
> $email3 =3D "";
>
> if ($num > 0)
> {
> while ($row =3D mysql_fetch_array($result))
> {
> // do something here to assign
> // $email1 =3D $row[email] and
> // $email2 =3D $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 =3D bill@hotmail.com,
> $email2 =3D bill@excite.com, and $email3 =3D
> bill@yahoo.com.

what i would do is add it into an array:

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

then u have an array with :

$emails[0] =3D bill@hotmail.com
$emails[1] =3D bill@excite.com
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 =3D 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=A2
> http://photos.yahoo.com/ph/print_splash
>
> --=20
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>
>

Fgc

2004-04-23, 11:34 am

Hi,

I would try:

Select email
From table2, table3
Where table3.emailID = table2.emailID
And id = "1"

This should give you

bill@hotmail.com
bill@excite.com
bill@yahoo.com

FG

Rachel Rodriguez

2004-04-23, 7:38 pm

> 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] = bill@hotmail.com
> $emails[1] = bill@excite.com
> etc...
>
>

Thanks, Uzi! This is exactly what I was looking for!

Rachel

__________________________________
Do you Yahoo!?
Yahoo! Photos: High-quality 4x6 digital prints for 25¢
http://photos.yahoo.com/ph/print_splash

Uzi

2004-04-29, 9:57 am

----- Original Message -----=20
From: "Rachel Rodriguez" <email2rachel2003@yahoo.com>
To: <php-db@lists.php.net>
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 | bill@hotmail.com|
> | 2 | diana@yahoo.com |
> | 3 | bill@yahoo.com |
> | 4 | bill@excite.com |
> | 5 | vic@hotmail.com |
> +--------+-----------------+
>
> 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 =3D "SELECT t3.email
> FROM table3 AS t3
> LEFT JOIN table2 AS t2
> ON (t3.emailID =3D t2.emailID)
> LEFT JOIN table1 AS t1
> ON (t2.id =3D t1.id)
> WHERE t1.id =3D 1";
>
> $result =3D @myql_query($query, $db_connection);
>
> $num =3D mysql_num_rows($result);
>
> $email1 =3D "";
> $email2 =3D "";
> $email3 =3D "";
>
> if ($num > 0)
> {
> while ($row =3D mysql_fetch_array($result))
> {
> // do something here to assign
> // $email1 =3D $row[email] and
> // $email2 =3D $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 =3D bill@hotmail.com,
> $email2 =3D bill@excite.com, and $email3 =3D
> bill@yahoo.com.

what i would do is add it into an array:

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

then u have an array with :

$emails[0] =3D bill@hotmail.com
$emails[1] =3D bill@excite.com
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 =3D 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=A2
> http://photos.yahoo.com/ph/print_splash
>
> --=20
> PHP Database Mailing List (http://www.php.net/)
> To unsubscribe, visit: http://www.php.net/unsub.php
>
>
>

Sponsored Links