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);


?>

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).

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.