New To Mac-Forums?

Welcome to our community! Join the discussion today by registering your FREE account. If you have any problems with the registration process, please contact us!

Get your questions answered by community gurus Advice and insight from world-class Apple enthusiasts Exclusive access to members-only contests, giveaways and deals

Join today!

 
Start a Discussion
 

Mac-Forums Brief

Subscribe to Mac-Forums Brief to receive special offers from Mac-Forums partners and sponsors

Join the conversation RSS
Web Design and Hosting Creating sites, scripting, and hosting discussions.

mysql "primary key" help


Post Reply New Thread Subscribe

 
Thread Tools
funky
Guest
 
Posts: n/a

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!
QUOTE Thanks
funky
Guest
 
Posts: n/a

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!
QUOTE Thanks
metsfan489
Guest
 
Posts: n/a

Quote:
Originally Posted by funky
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!
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/ .
QUOTE Thanks
fbrain
Guest
 
Posts: n/a

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....
QUOTE Thanks
KuruMonkey
Guest
 
Posts: n/a

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)
QUOTE Thanks

Post Reply New Thread Subscribe


« Javascript crashes IE 5.2.3 on Mac OS X | invalid file on yahoo. »
Thread Tools

Currently Active Users Viewing This Thread: 1 (0 members and 1 guests)
 
Posting Rules
You may not post new threads
You may not post replies
You may not post attachments
You may not edit your posts
BB code is On
Smilies are On
[IMG] code is On
HTML code is Off
Trackbacks are On
Pingbacks are On
Refbacks are Off
Forum Jump

Similar Threads
Thread
Thread Starter
Forum
Replies
Last Post
Installing mySQl on Mac OS X sealey Web Design and Hosting 7 06-02-2005 10:45 PM
Removing MySQL from Panther 3.6 vawolf OS X - Operating System 0 12-07-2004 09:25 PM
MySQL ftjogoh Web Design and Hosting 3 11-21-2004 11:29 AM
Any MySQL users (gurus) out there? mgm_03 OS X - Development and Darwin 5 05-03-2004 05:51 PM
MySQL password retardation mrtojamura OS X - Development and Darwin 0 02-19-2004 08:13 PM

All times are GMT -4. The time now is 04:12 AM.

Powered by vBulletin
Copyright ©2000 - 2014, Jelsoft Enterprises Ltd.
X

Welcome to Mac-Forums.com

Create your username to jump into the discussion!

New members like you have made this community the ultimate source for your Mac since 2003!


(4 digit year)

Already a member?