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

2004-04-23 Thread Torsten Roehr 
Rachel Rodriguez [EMAIL PROTECTED] wrote in message
news:[EMAIL PROTECTED]
 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] |
 ++-+


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

 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))
  {
   echoBill'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

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

2004-04-23 Thread Rachel Rodriguez 

--- Torsten Roehr [EMAIL PROTECTED] wrote:
 Rachel Rodriguez [EMAIL PROTECTED]
 wrote in message

news:[EMAIL PROTECTED]
  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] |
  ++-+
 
 
 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.
 

Thanks Torsten!  The database design tip is useful,
but I'm still stuck with the variable assignment
problem from my original post.  

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

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

2004-04-23 Thread Uzi Klein 
- 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))
  {
   echoBill'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

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

2004-04-23 Thread fgc 
Hi,

I would try:

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

This should give you 

[EMAIL PROTECTED]
[EMAIL PROTECTED]
[EMAIL PROTECTED]

FG

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

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

2004-04-23 Thread Rachel Rodriguez 
 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...
 
 

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

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

[PHP-DB] Hi I am new

2004-04-23 Thread water_foul 
I am new to databases and php and I was wondering if any one would point me
to a good guide
anything would help :)
thanks in advance,
water_foul

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

[PHP-DB] Inserting date into a table

2004-04-23 Thread Pambos Nicolaou 
I have created the table below:

CREATE TABLE questions (ID INT NOT NULL AUTO_INCREMENT,name VARCHAR(30),day 
TIMESTAMP, question TEXT, email VARCHAR(30),answer TEXT, PRIMARY KEY(ID));

I want to insert into the TIMESTAMP field the date automatically. How can I 
do it using the  insert command

INSERT INTO $table 
VALUES('','$name','TIMESTAMP','$question','$email','NULL');

Thanks in advance

Pambos Nicolaou

_
Protect your PC - get McAfee.com VirusScan Online 
http://clinic.mcafee.com/clinic/ibuy/campaign.asp?cid=3963

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

Re: [PHP-DB] Inserting date into a table

2004-04-23 Thread Rachel Rodriguez 
 
 I want to insert into the TIMESTAMP field the date
 automatically. How can I 
 do it using the  insert command
 
 INSERT INTO $table 

VALUES('','$name','TIMESTAMP','$question','$email','NULL');
 

Use the word null (no quotes) in place of 'TIMESTAMP':

INSERT INTO $table
VALUES('', '$name', null, '$question', '$email',
null);

The above will work in MySQL.  I haven't tried it in
other databases.

~R




__
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