retriving multiple values to a single database field in PHP

[ckb]

HI all,
actually what i have i s one master table tmcmembers and child table table tmcchildren. i did the inserts in the master & child table...(means i got multiple records in the child table for one master record) corresponding to a user name. this happens everywhere right... now what i want is to retrive the child records into 5 fields in the update screen but it got single value in the database namely child_name,Child_sex,Child_dob.. can anybody give any suggestions??
thanx
ckb

i tried like this to get values :
$connection=mysql_connect($hostname,$user,$pass)
or die ("Unable to connect to the database");

$query="SELECT id,e_mail,child_name,child_sex,child_dob from tmcchildren where e_mail='{$_SESSION['SESSION_UID']}'";

$result=mysql_db_query($database,$query,$connection)
or die("Error in query:$query.". mysql_error());
if(mysql_num_rows($result)==0)
{
mysql_close($connection);
//echo "<center>This is the first time you trying to Apply</center>";

}
else
{
list($id,$e_mailchild,$child_name,$childSelSex,$child_dob) = mysql_fetch_row($result);

}

i did the insert like this :
$connection=mysql_connect($hostname,$user,$pass)
or die ("Unable to connect to the database");

          $query="INSERT INTO tmcchildren (e_mail,child_name,child_sex,child_dob) VALUES";

          if((!empty($vtxtchildname1)) or (!empty($vchildSelSex1)) or (!empty($vtxtchildname1)) or (!empty($doboff)))

          $query .= "('$vtxtFullName','$vtxtchildname1','$vchildSelSex1','$doboff')";

          if((!empty($vtxtchildname2)) or (!empty($vchildSelSex2)) or (!empty($vtxtchildname1)) or (!empty($doboffa)))

          $query .= " ,('$vtxtFullName','$vtxtchildname2','$vchildSelSex2','$doboffa')";

          if((!empty($vtxtchildname3)) or (!empty($vchildSelSex3)) or (!empty($vtxtchildname1)) or (!empty($doboffa)))

          $query .= " ,('$vtxtFullName','$vtxtchildname3','$vchildSelSex3','$doboffb')";

           if((!empty($vtxtchildname4)) or (!empty($vchildSelSex4)) or (!empty($vtxtchildname1)) or (!empty($doboffc)))

          $query .= " ,('$vtxtFullName','$vtxtchildname4','$vchildSelSex4','$doboffc')";

           if((!empty($vtxtchildname5)) or (!empty($vchildSelSex5)) or (!empty($vtxtchildname1)) or (!empty($doboffd)))

          $query .= " ,('$vtxtFullName','$vtxtchildname5','$vchildSelSex5','$doboffd')";

[laserlight]

I would suggest that you clearly state your database schema.

e.g.

Table: tablename
table_id INT NOT NULL AUTO_INCREMENT PRIMARY KEY
field1 INT
field2 TEXT

or something that is more readable than "one master table tmcmembers and child table table tmcchildren. i did the inserts in the master & child table...(means i got multiple records in the child table for one master record) corresponding to a user name. this happens everywhere right".

[ckb]

hi,
schema

Table structure for table tmcchildren

#

CREATE TABLE tmcchildren (
id int(11) NOT NULL auto_increment,
e_mail varchar(250) NOT NULL default '',
tmc_no text,
child_name text,
child_sex text,
child_dob date default NULL,
PRIMARY KEY (id,e_mail)
) TYPE=MyISAM AUTO_INCREMENT=8 ;

Table structure for table tmcmembers

#

CREATE TABLE tmcmembers (
id int(11) NOT NULL auto_increment,
tmc_no text,
name varchar(250) default NULL,
sex text,
address text,
work_place text,
date_of_birth date default NULL,
region text,
tel_no_office text,
tel_no_res text,
gsm_no text,
e_mail varchar(250) NOT NULL default '',
yr_of_join_mbbs date default NULL,
yr_of_join_pg bigint(20) default NULL,
spl_name text,
spouse_name text,
spouse_sex text,
spouse_occupation text,
spouse_dr_yn text,
spouse_tmc_no text,
spouse_address text,
old_tmcno bigint(20) default NULL,
photo1 varchar(255) default NULL,
photo2 varchar(255) default NULL,
photo3 varchar(255) default NULL,
PRIMARY KEY (id,e_mail)
) TYPE=MyISAM AUTO_INCREMENT=17 ;

[yelvington]

If the relationship between member and child is one to many, your child table should have a column to hold the ID of a corresponding parent.

[ckb]

hi,

yes i have a common field in those 2 tables...