an SQL Query to insert one Child for Multiple Parents

more of an SQL question than PHP

[Anyango]

(if this is not the correct place to ask this, mods please move this)

leme be very specific

lets say we have two table

Table 1) Category [fields= categoryid,categoryname]
Table 2) Sub-Category[fields= subcategoryid,categoryid,subcategoryname]

lets say

There are 5 entries in Category Table and that are

1,Books
2)Games
3)Toys
4)Computers
5)Cars

now, if i want a subcategory of category "Cars", simply i add a subcategory in it with categoryid=5

thats no problem.

i want a MYSQL query that could allow me to add a subactegory for all those 5 categories in one Query.

so that query enters 5 new rows in table "subcategory"

where subcategoryid,subcategoryname remains same but categoryid is 1,2,3,4,5 respectively

any ideas?

[philestine]

i would prob try something like, select all rows from catergry, start a loop grab your fields and put another query inside your loop echoing thoes fields and adding the new ones.

query > select * from whatever > while > grab varibles > query > insert into whatever2 var1 var2 etc > end loop

hope this helps, this is only theory btw

[arran]

Hi Anyango,

Try something like:

INSERT INTO Sub-Category VALUES
(<subcat_id>,1,<subcat_name>),
(<subcat_id>,2,<subcat_name>),
(<subcat_id>,3,<subcat_name>),
(<subcat_id>,4,<subcat_name>),
(<subcat_id>,5,<subcat_name>)
;

arran.

[edited by: coopster at 12:07 am (utc) on Mar. 4, 2006]
[edit reason] disabled graphic smileys; fixed sidescroll [/edit]

[Habtom]

I think you need to loop it somehow. If you fix it with an SQL Query, there might come a need to add a category. So looping and adding them is better.

Habtom

[coopster]

If you are certain you are not going to have duplicate values then go with the approach arran is suggesting.

INSERT statements that use VALUES syntax can insert multiple rows. To do this, include multiple lists of column values, each enclosed within parentheses and separated by commas. Example:

INSERT INTO tbl_name (a,b,c) VALUES(1,2,3),(4,5,6),(7,8,9);

Resource: INSERT Syntax [dev.mysql.com]

If you look at the syntax of the example supplied by arran you quickly notice how you can build these statements on the fly:

$stmt = "INSERT INTO Sub-Category VALUES "; 
for [php.net] ($i = 1; $i <= 5; $i++) { 
  $stmt .= "(<subcat_id>,$i,<subcat_name>),"; 
} 
$stmt = rtrim [php.net]($stmt, ',');