Jump to content

mysql on-to-one realasion


corillo181

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

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.