I am trying to have 3 tables Category Subcategory and CatOption
- Name of table:
Category
columns:Id, Category
- Name of table:
Subcategory
Columns:id, cat_id, sub
- Name of table:
CatOption
columns:id ,sub_id, caption
I created 3 drop down list who are fetching data based on ID
Now I need to insert data in that tables so it can look like this
example
Category 1 Sky
Subcategory 1 1(copy from category id) Blue
Subcategory 2 1 (copy from category id) Grey
CatOption 1 1 (copy from subcategory id) sunny
CatOption 2 1 (copy from subcategory id) with clouds
These values are just for example user will insert all data
EDIT: I was wondering I had one idea can I base my search on category name not id so under cat_id I would have category like in the example below
Category 1 Sky
Subcategory 1 Sky (copy from category) Blue
Subcategory 2 Sky (copy from category) Grey
CatOption 1 Blue (copy from subcategory) sunny
CatOption 2 Blue (copy from subcategory) with clouds
My Answer:
To achieve this, you can modify your tables and queries to use the category name instead of the category id for referencing. Here's an example of how you can do this:
1. Modify your tables as follows:
- Category table: Id, Category_Name
- Subcategory table: id, cat_name, sub_Name
- CatOption table: id, sub_name, caption
2. When inserting data into the tables, you can use the category name instead of the category id. Here's an example of how you can insert data:
php
// Insert data into Category table
$category_name = "Sky";
$query = "INSERT INTO Category (Category_Name) VALUES ('$category_name')";
// Execute the query
// Insert data into Subcategory table
$sub_name = "Blue";
$query = "INSERT INTO Subcategory (cat_name, sub_Name) VALUES ('$category_name', '$sub_name')";
// Execute the query
// Insert data into CatOption table
$caption = "sunny";
$query = "INSERT INTO CatOption (sub_name, caption) VALUES ('$sub_name', '$caption')";
// Execute the query
3. When fetching data from the tables, you can join the tables based on the category name. Here's an example of how you can fetch data:
php
$query = "SELECT c.Category_Name, s.sub_Name, co.caption
FROM Category c
JOIN Subcategory s ON c.Category_Name = s.cat_name
JOIN CatOption co ON s.sub_Name = co.sub_name";
// Execute the query and fetch the results
By using the category name instead of the category id for referencing, you can achieve the desired result of inserting data into the tables based on the category name.
Rate this post
5 of 5 based on 6688 votesComments