Register

If this is your first visit, please click the Sign Up now button to begin the process of creating your account so you can begin posting on our forums! The Sign Up process will only take up about a minute of two of your time.

View Poll Results: Is Mysql hard to learn or is Obe just being lazy?

Voters
2. You may not vote on this poll
  • It very hard, poor Obe is doing his best

    1 50.00%
  • Its easy, Obe should get off his butt and learn it

    1 50.00%
Results 1 to 4 of 4
  1. #1
    Junior Member
    Join Date
    Aug 2004
    Posts
    2
    Member #
    7216
    I'm new to all this database stuff and after finding Mysql control center I was really hoping to be able to scrape through without having to use the proper mySQL.

    Excuse the dumbness of the following question but I'm really shooting in the dark on this one.

    I've made 2 tables.
    to keep things simple lets just say i have 2 coloums in each.
    first colum in the first table is auto incrementing numbers and serves as the primary key, the second coloum is "products" (don't worry, i'm not setting up a shop, you're safe).

    Now i want the second table to have 2 coloums aswell, 1 to store comments, and the other to show what products these comments are for.

    And this is where i'm stumped. How do I get the products coloum in the first table to be kind of linked to the comments in the second table for that product?

    I've tried searching for some documentation for this program and I can't find any, I've read something about foreign keys but I've read somewhere else that this isn't the best method, then they list all this code which I haven't got a clue about

    Any help would be VERY much appreciated on this.

    BTW i'm using dreamweaver to retrieve the results, should I be making dreamweaver get these records from the other table?

    oh i don't know....

  2.  

  3. #2
    Senior Member rosland's Avatar
    Join Date
    Jul 2003
    Location
    Norway
    Posts
    1,944
    Member #
    2096
    MySQL comes packed with support for two types of transactional DB's.
    InnoDB, and a inferior version from Berkley which I can't recall the name of.
    They both support a lot of additional advanced features (like table-row locking), that are handy when dealing with transactions.

    These are the only ones to fully support foreign keys.
    (you can make a foreign-keys connection through other methods, but you won't have features like "On update cascade", and their likes.)

    Your question is easily answered (sorry I don't have time to type it out for you as of now), but you will have an auto update of your secondary table whenever you alter something in your primary table. Your two tables can easily be merged through a Join-query, where they share a common key.
    S. Rosland

  4. #3
    Senior Member rosland's Avatar
    Join Date
    Jul 2003
    Location
    Norway
    Posts
    1,944
    Member #
    2096
    If you have no other transactional needs, or multitable update needs, you can link two or more ordinary (myISAM) tables together through a simple join query.

    Ex tables:

    products
    id | product

    comments
    id | prod_id | comment


    Let's say you have Audi, Volvo, BMW, Ford stored in the products table (with autoincremented ID's 1,2,3,4)

    In the comments table, you entered 2 comments for Audi and 3 comments for BMW. The prod_id in the comments table, references the id of the products table.

    To list all comments connected to their respective car, you can use this query:
    Code:
    SELECT  p.product, c.comments FROM `products` AS p 
    INNER JOIN comments AS c ON p.id=c.prod_id ORDER BY product
    To list only the comments for BMW:
    Code:
    SELECT  p.product, c.comments FROM `products` AS p 
    INNER JOIN comments AS c ON p.id=c.prod_id WHERE product='BMW' ORDER BY commments
    (The "products AS p" part, is only a way of short-handing the table names, so you don't have to type out their full name everytime you reference one of the table columns. When you select from multiple tables, you have to specify which table the column belongs to.
    i.e. the prod_id column from the comments table would be referenced 'comments.prod_id', or c.prod_id when you use the abreviation method.
    )
    S. Rosland

  5. #4
    Junior Member
    Join Date
    Aug 2004
    Posts
    2
    Member #
    7216
    AHHH, thank you! It works a treat now!

    This is perfect, very VERY much appreciated.

    Thanks.


Remove Ads

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •  
All times are GMT -6. The time now is 03:03 AM.
Powered by vBulletin® Version 4.2.3
Copyright © 2019 vBulletin Solutions, Inc. All rights reserved.
vBulletin Skin By: PurevB.com