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

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

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!

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

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