Hi!
Im new to MySQL RDMBS.
I know the basic CRUD if you know what i mean.
Create Read Update Delete.
I have 2 tables:
Category table and Product Table
Products should be categorized.
Product table has PK as an ID and FK to Category Table
How to INSERT INTO product table linking to specified Catageory Table?
How to SELECT products with same FK? as well as UPDATE and DROP?
Thanks in advance!
How to INSERT INTO product table linking to specified Category Table?
1. Make sure your category is in your category table
2. If it is, get it's ID
3. Insert your product with that category ID
add link field in table example link_id, in link_id field contain the id of FK or PK of other table you want to link to this table.
All table should have a link_id so its easy for you to manipulate data.
sorry for my bad english..
Depending on your html form you use to update. If you have a drop down with all the category names and ids you could just do a simple insert.
ex:
//assuming $_POST['category'] as the category ID
$query = "INSERT INTO products (prodName, categoryID) VALUES (
'{$_POST['prodName']}',
'{$_POST['category']}'
)";
//else
$query = "INSERT INTO products (prodName, categoryID) VALUES (
'{$_POST['prodName']}',
(SELECT ID from category WHERE catName = 'hello')
)";something like that. note that you cannot delete a category item if it exists on the product table, you will have to delete the ones on the product table first.
Does this mean that I insert the FK ID only and query to them using WHERE clause? OK Then. Does this also mean that if I delete a category, it will also delete the product cascaded to it? Thank you!
Does this also mean that if I delete a category, it will also delete the product cascaded to it?
This behaviour can be defined in the table structure.
can you please further explain?
I mean what I am thinking is:
If I want to delete a category, I can simply use delete clause cant I?
or if I delete category, I also have to delete the products associated with it using where clause referencing the fk_id from category?
I am using a reference by the way with myISAM not InnoDB if that makes sense
When you create your table, you can define FK relations as part of the structure. There you can specify if a delete should cascade. If you do not define it there, you will have to delete them manually.
That makes sense! Thanks!
Does this mean that if I update the category, all of the products in it will also be updated when I specify cascade on update or cascade on delete from the table structure? which table should have a cascade structure by the way? the category which is the parent? or the product which is the child? thanks!
I think our discussion is pointless. Just saw that you are using MyIsam. This is what the manual says about it:
The InnoDB storage engine supports checking of foreign key constraints, including CASCADE, ON DELETE, and ON UPDATE. ... For storage engines other than InnoDB, MySQL Server parses the FOREIGN KEY syntax in CREATE TABLE statements, but does not use or store it.
So you'll have to do it manually.
Thank's for reminding me that I have to change my database to innodb. Thats what I thought I should do.
We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.