mysql on-to-one realasion

[corillo181]

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..

[Barand]

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"

[corillo181]

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..

[corillo181]

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

[code=php:0]<?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);

}[/code]

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..

[.josh]

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'";

[corillo181]

nope it doesn't work still..

[Barand]

@ 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.
[code]
    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[/code]


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

[code]    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'[/code]


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.

[code]    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'[/code]

[corillo181]

[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..