mysql "primary key" help

Can someone help me with mySQL "primary keys"?

Say I have user "funky" who is in table "ab_users". How do I give im an id in a second table called "ab_urls", and link that to table "ab_users" ?

I get the basic idea of needing an id for the tables, but if someoe could give me a basic mySQL query to use to create the tables, I'd appreciate it. I do not want to create them manually in phpmyadmin, because I don't "get it" yet.

I need to "get it" before I implement it. Please help. Any pointers would be great!

Now an example:

user: funky ab_userid: 1
he has a ab_url_id: 5
has three urls in table ab_urls:

if someone pulled ab_url_id: 5

url_name:
http://mac-forums.com/forums/fakethread.php
url_option1:
yes
url_option2:
no


url_name:
http://apple.com/osx
url_option1:
yes
url_option2:
yes


url_name:
http://mac-forums.com/forums/fakethread.php
url_option1:
no
url_option2:
no


How do I implement this?
Thanks!

This question still stands. I'm looking for help linking tables with keys. I only have weekends free, so I will respond when I can.

Thank you!

funky said:

This question still stands. I'm looking for help linking tables with keys. I only have weekends free, so I will respond when I can.

Thank you!

Click to expand...

For one thing, I think if you could clarify your problem a bit, I'd be able to do. Are u trying to rite a script using php to pull all the data together linking them or are u trying to link them using mysql?

Also I think there are some other forums that could help you better with this, that are more dedicated to web design. One example would be http://forum.yaxay.com/ .

There is nothing special you need to do when creating the table apart from ensure a field in each matches up.

The you perform a join in the query when you read the tables out.


Unless you want to do some funky stuff with foreign keys....

your example is a little difficult to follow; you could be wanting to relate the urls to users or the user to urls in that example (or both!).

For example, if your User has some Urls..
you need the following mysql to create your tables:

CREATE TABLE ab_user
(
uid VARCHAR(64) NOT NULL,
PRIMARY KEY (uid)
)
TYPE = myIsam;

CREATE TABLE ab_url
(
uid VARCHAR(64) NOT NULL,
url_id VARCHAR(64) NOT NULL,
address VARCHAR(255) NOT NULL,
url_option_a BOOLEAN,
url_option_b BOOLEAN,
PRIMARY KEY (url_id)
)
TYPE = myisam;

To select the list of Urls for the user, you would use:

SELECT ab_url.address, ab_url.url_option_a, ab_url.url_option_b FROM ab_url, ab_user WHERE (ab_user.uid = ab_url.uid AND ab_user.uid = 'the_user_id');

In this case users 'own' urls, and a user can have multiple urls, but an url is owned by a single user. (Youy don't need the url_id from your example in this case)

On the other hand, if URLs can have multiple users, and a user can have multiple urls too, you need the following (I have allowed each user to set their own settings for the two url_option fields for each url in this case, too)

CREATE TABLE ab_user
(
uid VARCHAR(64) NOT NULL,
PRIMARY KEY (uid)
)
TYPE = myIsam;

CREATE TABLE ab_url
(
url_id VARCHAR(64) NOT NULL,
address VARCHAR(255) NOT NULL,
PRIMARY KEY (address)
)
TYPE = myIsam;

CREATE TABLE ab_user_url
(
uid VARCHAR(64) NOT NULL,
url_id VARCHAR(64) NOT NULL,
option_a BOOLEAN,
option_b BOOLEAN,
PRIMARY KEY (uid, url_id)
)
TYPE = myIsam;

Now, to get the list of all URLs for the user, and that user's options for each URL, we use:

SELECT ab_url.address, ab_user_url.option_a, ab_user_url.option_b FROM ab_user_url, ab_url WHERE ab_user_url.uid = 'your_user' AND ab_user_url.url_id = ab_url.url_id;

I'm assuming this is closest to what you're after, since its actually using the primary key to build a relation. The things to note are; primary keys must be UNIQUE in the table. They must also be non-null fields. In the table ab_user_url the
PRIMARY KEY (uid, url_id)
forms a Compund Key; the combination of the two fields is what must be unique, so each user can be in the table any number of times, as can the url; but the pair comes only once - its that which allows you to filter the table to give only the join you want.

Once you begin to build compound keys (especially when its a field from table a and a field from table b, to build a compund key in table c, which relates the other tables together), it starts to become obvious why joins are called joins, and what foreign keys are (they are literally a declaration that field X in table B will have some value that could appear in field Y of table A - allowing you to ensure that the foreign key does make joins that work)

Some caveats; the version of mysql I use for work doesn't handle foreign keys, therefore I don't go fiddling with versions that do, therefore I've got no idea how mysql actually handles foreign keys or the various JOIN keywords. Starting down that path would jkust make my job painful

Also I didn't stop to check datatypes (BOOLEAN) and reserved words, so this might well not actually function; but I think the principles are all there. If you want to clarify what you need in the case I've gone off on a tangent, I'll be happy to try to give better examples or explanations. (also, on reflection, I think my first example is rotten)