mysql table relationship help with example

stsintzo

perhaps someone can help me with an example I've come up with to help me to better understand how to build a relationship in Mysql.
I want to create two tables, "websites" and "category" for example.
I would have the fields as follows:

websites:

web_id
web_address
cat_id

category:

cat_id
cat_name

My goal is to have a webfront in which to input the new data. SO, I would have a webform that asks for the web address, and then a drop down menu that is populated through a self populating script ....

Example:

web address😐_____________|

Category: >computing
>sports
>php
etc.

This form would then go to the php script which would use an insert statement to input the new information.

Now I have two questions:
1) How would or SHOULD I go abouts creating the database relationship?
and
2) How do I create the insert statement?

My attempt for the creation of the relationship is this:

the websites table:
CREATE TABLE 'websites' ('web_id' INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 'web_address' TEXT NOT NULL, 'cat_id' INT NOT NULL,UNIQUE ('web_id', 'web_address'), FOREIGN KEY (cat_id) REFERENCES category (cat_id) ON DELETE CASCADE);

and then the category table:
CREATE TABLE 'category' ('cat_id' INT NOT NULL AUTO_INCREMENT PRIMARY KEY, 'cat_name' VARCHAR(20) NOT NULL, UNIQUE ('cat_id', 'cat_name'));

....... Now, I'm not sure if I've done that correctly as it has been somewhat difficult to find info regarding relational db syntax in mysql for me.

Then there is the question regarding the SQL statement in the php page that will input the info into the db from the webform.However, I'm not sure about the part where i want the category name that was selected to refer to the "cat_id" instead of the actual name in the "cat_name" field.

I have this ... question mark being the portion I'm stuck on.

INSERT into websites (web_id, web_address, cat_id) VALUES (Null, '$web_address', '???????');

Thanks for ANY help.

nnichols

When you build the category drop down box dynamically you should use the cat_id as the value for the option and the cat_name for the text. That way, when you submit the form you have the cat_id instead of having to perform a lookup based on the cat_name.

$sql = "SELECT * FROM category ORDER BY cat_name ASC";
$result = mysql_query($sql) or die('MySQL error: ' . mysql_error() . '<br>Query: ' . $sql);

while ($row = mysql_fetch_assoc($result)) {

echo '<option value="' . $row['cat_id'] . '">' . $row['cat_name'] . "</option>\n";

}

Hope this helps

nnichols

I would also suggest that you change your foreign key constraint to RESTRICT instead of DELETE. It could be disastrous if you delete a category and every related website entry because of your constraint.

stsintzo

How do you mean ? (about the RESTRICT thing that is)
(the syntax i mean)

nnichols

I am assuming that you are using InnoDB?

As it is at the moment - if you delete a category - the ON DELETE CASCADE will cause MySQL to delete all child records, being every website that has the category.

It would be better to have ON DELETE RESTRICT which would simply refuse to delete the category because it has child records.

Hope this makes sense

stsintzo

Thanks, I'm actually using Mysql ... but perhaps that's not what you were referring to? lol, I dunno ANYthing !

nnichols

The standard MySQL table handlers do not support foreign keys, they are ignored even though they don't cause errors when you run the CREATE TABLE statement.

InnoDB is an ACID compliant table handler available in MySQL. It does support transactions and foreign keys (constraints) as well as a few other features.

Read this to find out more about InnoDB - http://www.mysql.com/doc/en/InnoDB_overview.html

Hope this helps

stsintzo

So is there no way to create relationships between tables in Mysql ? or are you saying you just can't use the "FOREIGN KEY" command ?

nnichols

If you are using the standard table handler, which you are having had another look at your original CREATE TABLE statements, you have no support for handling relationships. But this shouldn't be a problem as you can easily handle the relationships with your application logic.