Member Avatar for ero100

Dear Friends, Please help me with this issue.

the design of my database isnt well normalized, but due to the ammount of work done based on this queries i found it more difficult to have to change the whole design . Instead im trying just to solve it.

i have created two tables:

categories(cat_id,cat_name) where cat_id is autoincrement and the primary key.

sub_categories (sub_cat_id, sub_cat_name, cat_id) where sub_cat_id is the primary key and the cat_id is the foreign key in which link both tables.

my intention on this design was to be able to create a two level menu tree, and its work fine for that. the categories names are the top header name of the tree, and the subcategories are the links i which will display the products related to it.
for example:

car (category) type 1 (sub_cat 1) type 2 (sub_cat 2)

My PROBLEM: everytime i need to include a new product i can, insert a new sub_category on an existing category, or create a new category and then insert a new sub_cat with the name of the type of product.

it is easily to insert a new category, but to maintain the tree, i also need to insert a subcategory in which is in another table.(sub_categories table), and that is where im finding difficult.

example:
i want to create a new category and two subcategories. Based on my desing i have first to insert a new category and take its id and go to the next table(sub_category) and insert a new sub_category based on the cat_id of the categorie table. but if i want to do it through a form, im unable to.

ive tried to use the insert_id() in the first query, it its work fine when i echo it its give me me cat_id value, however when i move to the next query in which is to insert the sub_cat_name, and take the cat_id that was previously inserted, it doesnt work.

bellow is the query i have attempted it supposesed to take the cat_id generated automatically and insert it into the sub_categories table with the name inserted in the form for the sub_cat_name.

please if you can help me with that i will be very glad once im not expert in php / mysql.

By the way sorry for writting too much.

PAGE 1:

<form action="admin-add-new-cat-confirm.php" method="POST" name="add" enctype="multipart/form-data"> Category name:<br /><input name="cat_name" type="text" size="35" /> <br /> <input name="submit" type="submit" class="submit" value="Submit" /> </form>

PAGE 2:

$result_cat = mysql_query("select * from categories",$con); if ($myrow_cat = mysql_fetch_array($result_cat)) { $cat_id = $myrow_cat['cat_id']; } if (isset($_POST['cat_name'])) $cat_name = $_POST['cat_name']; $sql = "insert into categories (cat_name) values ('$cat_name')"; $result_insert = mysql_query($sql,$con); $cat_id = mysql_insert_id(); echo $cat_id; ?> <form action="admin-add-new-sub-cat.php" method="POST" name="add" enctype="multipart/form-data"> Sub-category Name:<br /><input name="sub_cat_name" type="text" size="35" /> <br /> <input name="submit" type="submit" class="submit" value="Submit" /> </form>

PAGE 3:

$result_sub_cat = mysql_query("select * from sub_categories",$con); if ($myrow_sub_cat = mysql_fetch_array($result_sub_cat)) { $sub_cat_id = $myrow_sub_cat['sub_cat_id']; } if (isset($_POST['sub_cat_name'])) $sub_cat_name = $_POST['sub_cat_name']; $cat_id = mysql_insert_id(); $sql = "insert into sub_categories (sub_cat_name, $cat_id) values ('$sub_cat_name')"; $result_insert = mysql_query($sql,$con); ?>
Edited by mike_2000_17 because: Fixed formatting
  • 3 Contributors
  • 2 Replies
  • 865 Views
  • 8 Hours Discussion Span
  • Latest Post Latest Post by metalix
Member Avatar for ijazkhattak

well i think i have solved one such problem where i used to create one heading and then its discussion inside it just like ur tree and then sub categories.
what i did is created a seperate table for headings and seperate table for its discussions. the heading have one id e.g h_id. i create it and then display it and in its onclick event or based on ur programming logic what ever u wanna use send the g_id along with arguments to create a row in discussion table while will look like

|r_id|g_id|text
|01 |3 | this is subheading/discussiong of heading number 3

when other rows are added we will use query
insert into discussion(g_id,text)values(3,this is second subheading of group id 3).

Member Avatar for metalix

I have done queries like this.
All you need to do is add the query under the first one, and have a two input form.
but if you want to add multiple sub-category's you will need two forms to stop a headache later when you want to add more sub-categorys

ex:
form

category: {__}
Number of sub-category's?: [__]
Ajax request to display that number of text fields

then for the script use a simple foreach post as name -> value
to insert all the bits in the right place. you can have as many queries as you want in a script.
If I have time tonight and you still haven't fixed it I'll seeif I can byuld you something

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.