John Davidson

Message:

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.