Results 1 to 5 of 5
  1. #1
    Join Date
    Mar 2010
    Posts
    7

    Unanswered: Quick newbie question!

    I'm using InnoDB with foreign keys for my mysql tables, and I have a many-many situation (I think?) where many hosts can have many payment methods and many payment methods can have many hosts.. so I created my table structure like this:

    ---------------------------------------------------
    Table 1

    host_id <---(primary)
    host_name

    Table 2

    host_id <--- (foreign key to Table 1 host_id)
    payment_id <---(foreign key to Table 3 payment_id)

    Table 3

    payment_id <---(primary)
    payment_method
    ---------------------------------------------------

    I have 2 hosts in table 1 (test data to finish building my CRUD)..

    When I go to insert a row using phpmyadmin, say for Table 2 host_id - the drop-down box gives me the options of either...

    1 -
    2 -

    or

    - 1
    - 2

    I was wondering if anybody could quickly explain which one I should be using in this instance? and if I should be altering my INSERT query to accomodate whichever one I should use? Does it even matter?

    Just a little bit confused at the min, any help appreciated

  2. #2
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    Quote Originally Posted by Kiada View Post
    Does it even matter?
    of course it matters

    when you go to insert a row into table2, you have to specify two values

    one of them is the id of the host, and the other is the id of the payment method

    never mind for a moment the interface that phpmyadmin offers you when you are inserting data, think about what you need to do in terms of what's necessary for that table

    then the choices should become obvious

    maybe you were getting mixed up because you have only two hosts, numbered 1 and 2, as well as only two payment methods, also numbered 1 and 2
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  3. #3
    Join Date
    Mar 2010
    Posts
    7
    Thanks for the reply!

    Apologies if I'm being really stupid and the answer should be obvious, maybe I'm just having one of 'them' moments.. but I still don't really understand which option I should be selecting..

    http://i44.tinypic.com/2nge6fr.jpg --- the options for 'host_id' (theres 2 hosts in the hosts table).


    http://i44.tinypic.com/v74eoz.jpg --- the options for 'payment_id' (theres actually 3 payment methods, not 2 sorry).

    is it the option with the minus before the option, or after?

    Thanks again!

  4. #4
    Join Date
    Apr 2002
    Location
    Toronto, Canada
    Posts
    20,002
    hmm, that's definiteley weird

    i stopped using phpmyadmin many years ago (when i started using HeidiSQL) so i can't test this for myself

    why don't you try inserting a few rows by choosing various combinations of these values, and then dump the resulting table rows (under the Export tab in phpmyadmin) to see what happened
    rudy.ca | @rudydotca
    Buy my SitePoint book: Simply SQL

  5. #5
    Join Date
    Mar 2010
    Posts
    7
    I decided I had a 50/50 chance of getting it right, and so far it works fine! But thanks for the heads up on the HeidiSQL - checking it out now

    *edit: wow, this HeidiSQL is much better than phpmyadmin. Cheers

Posting Permissions

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