Accessing Relational Data...

Questions about the MySQL, PostgreSQL, and most other databases, as well as using it with PHP can be asked here.

Moderator: General Moderators

Post Reply
User avatar
Elmseeker
Forum Contributor
Posts: 132
Joined: Sun Dec 22, 2002 5:48 am
Location: Worcester, MA

Accessing Relational Data...

Post by Elmseeker »

Hi all,

I am not very good with DB stuff so I am wondering what happens if you have relational data in another table...Meaning, I have a table that contains information for parents:

Code: Select all

CREATE TABLE p_login (
  ID bigint(20) NOT NULL auto_increment,
  rname varchar(25) NOT NULL default '',
  email varchar(100) NOT NULL default '',
  u_name varchar(25) NOT NULL default '',
  u_pass varchar(32) NOT NULL default '',
  kid0 bigint(20) NOT NULL default '0',
  kid1 bigint(20) NOT NULL default '0',
  kid2 bigint(20) NOT NULL default '0',
  kid3 bigint(20) NOT NULL default '0',
  kid4 bigint(20) NOT NULL default '0',
  kid5 bigint(20) NOT NULL default '0',
  kid6 bigint(20) NOT NULL default '0',
  kid7 bigint(20) NOT NULL default '0',
  kid8 bigint(20) NOT NULL default '0',
  kid9 bigint(20) NOT NULL default '0',
  u_level tinyint(4) NOT NULL default '0',
  PRIMARY KEY  (ID),
  UNIQUE KEY u_name (u_name),
  UNIQUE KEY kid0 (kid0),
  UNIQUE KEY kid1 (kid1),
  UNIQUE KEY kid2 (kid2),
  UNIQUE KEY kid3 (kid3),
  UNIQUE KEY kid4 (kid4),
  UNIQUE KEY kid5 (kid5),
  UNIQUE KEY kid6 (kid6),
  UNIQUE KEY kid7 (kid7),
  UNIQUE KEY kid8 (kid8),
  UNIQUE KEY kid9 (kid9),
  UNIQUE KEY ID_2 (ID),
  KEY ID (ID)
) TYPE=MyISAM;
And another containing information for kids:

Code: Select all

CREATE TABLE k_main (
  ID bigint(20) NOT NULL auto_increment,
  u_name varchar(25) NOT NULL default '',
  u_pass varchar(25) NOT NULL default '',
  u_level tinyint(4) NOT NULL default '0',
  rl_name varchar(50) NOT NULL default '',
  email varchar(50) NOT NULL default '',
  p_email varchar(50) NOT NULL default '',
  p_id bigint(20) NOT NULL default '0',
  t_email varchar(50) NOT NULL default '',
  t_id bigint(20) NOT NULL default '0',
  home_school tinyint(4) NOT NULL default '0',
  pet0 bigint(20) NOT NULL default '0',
  pet1 bigint(20) NOT NULL default '0',
  pet2 bigint(20) NOT NULL default '0',
  pet3 bigint(20) NOT NULL default '0',
  pet4 bigint(20) NOT NULL default '0',
  sh_addr bigint(20) NOT NULL default '0',
  sw_addr bigint(20) NOT NULL default '0',
  sch_approved tinyint(4) NOT NULL default '0',
  vbucks bigint(20) NOT NULL default '0',
  PRIMARY KEY  (ID),
  KEY ID (ID),
  UNIQUE KEY ID_2 (ID),
  UNIQUE KEY u_name (u_name),
  UNIQUE KEY email (email)
) TYPE=MyISAM COMMENT='Childrens Login and main data is stored here.';
What I am wondering is if I relate p_login[kid*] to k_main[ID] will it create a 2 dimesional array for accessing the childs data? I mean, will I be able to access kid1's pet name, after querying the DB and fetching the p_login array, by using something like $row['kid1']['petp0']?

If not, could someone please explain how to access the childs data without having to do a second query if possible? Thanks!
aybra
Forum Commoner
Posts: 56
Joined: Sun Nov 24, 2002 12:52 am

Post by aybra »

you'd make the table to where each kid had a unique ID and there pet had the same one, then do a cross table query and find all the rows where 'ID' = '$uniqueid'
User avatar
Elmseeker
Forum Contributor
Posts: 132
Joined: Sun Dec 22, 2002 5:48 am
Location: Worcester, MA

Post by Elmseeker »

I'm sorry, you completely lost me...petID's will not be the same as child ID's nor will child ID's be the same as parent ID's
aybra
Forum Commoner
Posts: 56
Joined: Sun Nov 24, 2002 12:52 am

Post by aybra »

oops
Last edited by aybra on Mon Jan 13, 2003 5:46 pm, edited 1 time in total.
aybra
Forum Commoner
Posts: 56
Joined: Sun Nov 24, 2002 12:52 am

Post by aybra »

No but if you want to select a child and put him with his pet, I'm assuming the pet is HIS pet and not a cat dog frog lizzard kinda list, but specificly his pet dog rover?


but all you would do is put an identifier in each table that married them together, like 'ID2' and then make sure the Child's pet dog rover has an 'ID3' that matches the childs number.

Code: Select all

$query = "
SELECT * FROM `child`, `parent`, `pet`  
WHERE `ID1` = '$number' 
AND `ID2` = '$number'  
AND `ID3` = '$number' 
ORDER BY `petname` 
ASC";
Then run your query similiar to that..
User avatar
Elmseeker
Forum Contributor
Posts: 132
Joined: Sun Dec 22, 2002 5:48 am
Location: Worcester, MA

Post by Elmseeker »

Ok, I am using phpMyAdmin (of course) and it has a relational view, I can't just go into phpMyAdmin relational view on the childs table and point the pet fields to pet table ID's?

And even if I can, I still don't have a clue as to how to use 1 query to read ALL of the needed data from both tables...
aybra
Forum Commoner
Posts: 56
Joined: Sun Nov 24, 2002 12:52 am

Post by aybra »

You would pull all the fields from all the tables using a query kinda like the one in my previous post.

as long as you have a pets, child and parents table
fractalvibes
Forum Contributor
Posts: 335
Joined: Thu Sep 26, 2002 6:14 pm
Location: Waco, Texas

Post by fractalvibes »

TRy some thing like this:

Parent table:

ParentID
.....other parent columns(get rid of references to children!)


ChildrenTable
ChildID
ParentID
....other Children columns(get rid of refences to pets!)

Pet Table
PetId
ChildId
....other pet-related columns

Now to a parent and all children for a parent:

Select A.ParentID
,B.ParentId
, B.ChildID
, other fields you want....
From ParentTable A
,ChildTable B
Where A.ParentID = B.ParentID


to add also all pets for all children for parent:
Select A.ParentID
, B.ParentID
, B.ChildID
,C.PetId
,C.ChildId
, other fields you want....
From ParentTable A
,ChildTable B
,PEtTable C
Where A.ParentID = B.ParentID
AND B.ChildId = C.ChildID


Easy as Pie!
That way each parent can have an arbitrarily large number of children (arrg!)
and each child can have an arbitrarily large number of Pets (Yay!)

Phil J.
Pakcik_Kantin
Forum Newbie
Posts: 19
Joined: Mon Jan 13, 2003 8:23 pm
Location: Kuala Lumpur

Normalize your table

Post by Pakcik_Kantin »

Instead of lacking your head with relations, try to cut down your tables.

Table 1. Login and kids id only
Table 2. Kids information

Your can have more records of kids, rather than you can have up to 9 kids at this time.

your qurey would be easy like this.

select table2.*table.* from table1,table2 where table.*1.kidsid=table2.kidsid

That is what i understand about your problem
Post Reply