John Davidson

php - how to insert same ID on dropdown choice

0 comments
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.

Rate this post

5 of 5 based on 8456 votes

Comments




© 2024 Hayatsk.info - Personal Blogs Platform. All Rights Reserved.
Create blog  |  Privacy Policy  |  Terms & Conditions  |  Contact Us