Results 1 to 8 of 8
  1. #1
    Join Date
    Dec 2003
    Location
    Miami, FL
    Posts
    3,262

    Two MySQL Tables

    I'm writing a small script to hold client billing information + hosting information (domain name, package, cost, add'tl domains). I would rather put them in different tables (client_info & hosting_info). I was curious what I would do to link them? Would there be a column that is identical to both of them that iwll identify that row 3 belongs to row 3 ?

    Thanks

  2. #2
    Join Date
    Oct 2003
    Location
    Israel
    Posts
    132
    You should link them by a ID, name or email, something like this:

    TABLE 1
    --------------------------
    | ID | NAME | EMAIL |
    --------------------------
    --------------------------
    | 1 | John Doe | [email protected] |
    --------------------------


    TABLE 2
    --------------------------
    | ID | DOMAIN | PACAKGE |
    --------------------------
    --------------------------
    | 1 | john.com | Medium |
    --------------------------


    That will also allow you to execute a joint query, something like this:

    SELECT table1.email, table2.domain
    FROM table1, table2
    WHERE ID = '1'

    Hope I helped.

    - Tomer
    Christmas time is among us, get your loved ones Designer Fragrances

  3. #3
    Join Date
    Aug 2004
    Location
    Tulsa, Oklahoma
    Posts
    168
    Yes, they need to have some sort of linking value. Int the example below both have orderid in common:

    Orders table
    --------------------------------
    orderid int

    OrderDetails Table
    orderid

    To get information from both tables, use:
    SELECT * FROM Orders o
    INNER JOIN OrderDetails od ON o.OrderID = od.OrderID
    AND (additional criteria you need to meet, if any)

    Although you should replace the * with the fields you need. You can also replace "inner" with "left outer" if you don't always have a matchinf record in "OrderDetails", but still want it to return info from "Orders".
    Mike Bell
    Image Hosting

  4. #4
    Join Date
    Dec 2003
    Location
    Miami, FL
    Posts
    3,262
    So would ID be auto_increment on both tables and that would work?

    Thanks!

  5. #5
    Join Date
    Oct 2003
    Location
    Israel
    Posts
    132
    Originally posted by Rob83
    So would ID be auto_increment on both tables and that would work?

    Thanks!
    Not a good idea, it can get mixed up.

    Put auto_increment on the first table, and use mysql_insert_id to insert the first ID into the second table.

    Good Luck

    - Tomer
    Christmas time is among us, get your loved ones Designer Fragrances

  6. #6
    Join Date
    Dec 2003
    Location
    Miami, FL
    Posts
    3,262
    Originally posted by Tomer
    Not a good idea, it can get mixed up.

    Put auto_increment on the first table, and use mysql_insert_id to insert the first ID into the second table.

    Good Luck

    - Tomer
    That's it! THanks!!

  7. #7
    Join Date
    Oct 2003
    Location
    Israel
    Posts
    132
    No problem.

    - Tomer
    Christmas time is among us, get your loved ones Designer Fragrances

  8. #8
    Join Date
    Jul 2005
    Posts
    62
    You're trying to implement foreign keys on your database. Just for a bit of background, you might want to check out http://dev.mysql.com/doc/mysql/en/in...straints.html. Note that the standard MyISAM table types won't support it.

    That ought to give you a background on what you're doing, etc..

    -Jeff

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •