working with linked tables

[theEclipse]

I am looking to learn how to use linked tables in mysql, but I think that I am going to need some tutorial on linked table basics before I dive into the mysql manual on the subject.

Does anyone know of any mysql linked table tutorials?

 

[TonyGroves]

There is really no such thing as linked tables in MySQL, or any other relational database. Tables are independent entities. However, table definitions can refer to other tables by using "foreign keys", which may result in the other tables being automatically affected by changes in the table being worked on; this can give the impression that the tables are linked.

Anyway, you didn't come here for a pedantic lecture, you want to know about tutorials on the subject. The MySQL manual has quite a good tutorial section, which no doubt covers foreign keys. You could also search the web for "MySQL foreign keys tutorial" or "MYSQL referential integrity tutorial".

 

[theEclipse]

Hmm...btw, nice word, pendantic.

So then maybe I dont know what I am looking for. Here is what I am trying to do: I want to have one table contain a list of field options for another table.

For example: (and this is truely what I am working towards) I would like to have a table with user permission levels (customer, employee, manager, admin, ect). Another table, which holds specific user information has a field something like "user permission level" that references the first table. Then, when I am filling out an insert into my database I can select from a dropdown what I want the new user permissions to be.

I can do all the php to make this work, its the mysql that I am not familiar with. Is "foreign keys" what I am looking for?

 

[TonyGroves]

You don't need foreign keys for that.

Your first table, permission levels, might look something like:
[tt]
CREATE TABLE permlevels
(
id CHAR(10) PRIMARY KEY,
description CHAR(50) NOT NULL
);
[/tt]
Then your user table might look like:
[tt]
CREATE TABLE users
(
id INT PRIMARY KEY,
name CHAR(30) NOT NULL,
permlevel_id CHAR(10) NOT NULL
);
[/tt]
To assemble your drop-down list, you can simply retrieve all the records from the permlevels table. Then when an entry from that list is selected, use that as the value for permlevel_id in the user's record.