Jump to content


Photo

mysql on-to-one realasion


  • Please log in to reply
7 replies to this topic

#1 corillo181

corillo181
  • Members
  • PipPipPip
  • Advanced Member
  • 896 posts

Posted 15 May 2006 - 08:29 AM

ok i know if i got 2 tables

cus_id

and

who_id

to make then a one to one i have to have one id in other..

cus_id
who_id..

my question is how are they realated..

how will mysql know when to put data from the other table into the other one..

making them both primary?

explain please..

#2 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,023 posts

Posted 15 May 2006 - 08:58 AM

If it is a one-to-one relationship then just make the primary key of each table the "cus_id". You don't need "who_id"
If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#3 corillo181

corillo181
  • Members
  • PipPipPip
  • Advanced Member
  • 896 posts

Posted 15 May 2006 - 09:33 AM

hm and what if i wanted a one to many or many to many..

my question is.. how do mysql knows when to get keys from other table.. if when i do a table that i want it to do with another table if i just make mor eprimary keys for that table.. withn the same id name of my previews tables..

heres a exaplem..

i got a few tables and i'm about to make a comment table..

so if i want the to be a many to many what should i do..
========================================
||comment_id||user_id|pic_id||userpic_id||and whatever||
========================================
thats my question.. should i make all of those primary keys so mysql can read from the other tables.. or there is another syntax for mysql to read from other tables..

#4 corillo181

corillo181
  • Members
  • PipPipPip
  • Advanced Member
  • 896 posts

Posted 16 May 2006 - 12:35 AM

it works.. but now i'm having problem select it from the tables i thought this would wokr fine but is not..

<?php
include 'includes/db.php';

$con="SELECT testa.name,testb.last FROM testb,testa WHERE a_id='4'";
$query=mysql_query($con);
while($row=mysql_fetch_array($query)){
print_r($row);

}

a_id is the same id in both table.. is the main id of table a and the connecting id for table be with table a..

#5 .josh

.josh
  • Staff Alumni
  • .josh
  • 14,871 posts

Posted 16 May 2006 - 01:04 AM

well i'm not 100% sure since i'm just now getting into relational tables and all, but i think that you need to list the tables after the FROM statement in the same order as you select the columns that are in them, like this:

$con="SELECT testa.name,testb.last FROM test[!--coloro:red--][span style=\"color:red\"][!--/coloro--]a[!--colorc--][/span][!--/colorc--],test[!--coloro:red--][span style=\"color:red\"][!--/coloro--]b[!--colorc--][/span][!--/colorc--] WHERE a_id='4'";
Did I help you? Feeling generous? Buy me lunch! 
Please, take the time and do some research and find out how much it would have cost you to get your help from a decent paid-for source. A "roll-of-the-dice" freelancer will charge you $5-$15/hr. A decent entry level freelancer will charge you around $15-30/hr. A professional will charge you anywhere from $50-$100/hr. An agency will charge anywhere from $100-$250/hr. Think about all this when soliciting for help here. Think about how much money you are making from the work you are asking for help on. No, we do not expect you to pay for the help given here, but donating a few bucks is a fraction of the cost of what you would have paid, shows your appreciation, helps motivate people to keep offering help without the pricetag, and helps make this a higher quality free-help community :)

#6 corillo181

corillo181
  • Members
  • PipPipPip
  • Advanced Member
  • 896 posts

Posted 16 May 2006 - 01:16 AM

nope it doesn't work still..

#7 Barand

Barand
  • Moderators
  • Sen . ( ile || sei )
  • 18,023 posts

Posted 16 May 2006 - 09:08 AM

@ Crayon - that doesn't matter (see examples below)

@ Richard,


First, a table can have only one primary key which uniquely identifies each record.

Keys contained in a record which refer to other tables are "foreign keys".

One-to-one
-------------
Usually used for security purposes to separate sensitive information from public data so access to sensitive data can be restricted.
    Employee         Payroll
    ----------       ----------
    emp_id(PK) <---> emp_id (PK)
    name             salary
    phone            tax-to-date
    department       etc
    etc
    
    SELECT e.department, SUM(p.salary) 
    FROM employee e INNER JOIN payroll p
        ON e.emp_id = p.emp_id
    GROUP BY e.department


One-to-many
-----------
The many table has a foreign key matching the PK of the one table

    Customer           Order
    ---------          -----------
    cust_id(PK)<--+    ord_id (PK)
    name          |    product
    address       |    qty
    discount      |    price
    etc           +--  cust_id (FK)
                  
    SELECT o.product, o.qty, o.price, c.discount
    FROM customer c INNER JOIN order o
        ON c.cust_id = o.cust_id
    WHERE c.cust_id = '$cid'


Many-to-many
--------------
One customer can order many products, one product can be ordered by many customers.

In this case you need a link table between customer and product containing customer and product as foreign jeys. The order table performs that function in the example.

    Customer           Order                 Product
    ---------          -----------           ---------
    cust_id(PK)<--+    ord_id (PK)    +--->  prod_id (PK)
    name          |    prod_id (FK) --+      prod_name
    address       |    qty
    discount      |    price
    etc           +--  cust_id (FK)
                  
    SELECT p.prod_name, o.qty, o.price, c.discount
    FROM customer c INNER JOIN order o
        ON c.cust_id = o.cust_id
        INNER JOIN product p
        ON o.prod_id = p.prod_id 
    WHERE c.cust_id = '$cid'

If you are still using mysql_ functions, STOP! Use mysqli_ or PDO. The longer you leave it the more you will have to rewrite.

Donations gratefully received






moon.png

|baaGrid| easy data tables - and more
|baaChart| easy line, column and pie charts

#8 corillo181

corillo181
  • Members
  • PipPipPip
  • Advanced Member
  • 896 posts

Posted 16 May 2006 - 01:36 PM

[img src=\"style_emoticons/[#EMO_DIR#]/laugh.gif\" style=\"vertical-align:middle\" emoid=\":laugh:\" border=\"0\" alt=\"laugh.gif\" /] you the greates man.. you chould turn this in to a tutorial is the bast example i seen in the 3 days i bene looking for this problem and it was as simple as that..

thank you

i'm very excited now i can move forward..

thanx again..




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users