Jump to content

Archived

This topic is now archived and is closed to further replies.

corillo181

mysql on-to-one realasion

Recommended Posts

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

Share this post


Link to post
Share on other sites
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"

Share this post


Link to post
Share on other sites
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..

Share this post


Link to post
Share on other sites
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..

Share this post


Link to post
Share on other sites
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'";

Share this post


Link to post
Share on other sites
@ 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]

Share this post


Link to post
Share on other sites
[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..

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.