Multiple Auto_Increments?

Anon

In my user registration form, people are asked to select some options, they can select as many as they want (with in reason) from a list of about 100. The main user details are entered into a table which is gived a unique user_id, and the options they selct are entered into another table along with the user_id (for which i use mysql_insert_id), the problem is I can get the user_id to be entered for 1 option, but if they select 2 or more, it doesn't want to work. How would I got about getting the user_id copied to every record they enter?
My tables are...

Tabel 1

| user_id | user_name | address |

Table 2

| code_id | user_id | option |

So if a user registers, he is given user_id 01, which is then copied to table 2. If he selects 3 options, his user_id is copied 3 times.
Does this many ANY sense?
Any help greatly appreciated.

[deleted]

Search before you post.

You need to manually fetch the id from the first insert and put it into the other tables.

Unless you are a database guru, then you could use triggers etc, but as you are asking this I guess you are not a guru :-)

Anon

I did search, but didn't find owt. I know how to insert the last auto_increment, but not more than once. I mean if a user selects 2 options, then the user_id is grabbed fro the 1st table, and inserted into the 2nd table twice, if the user selects 3 options its inserted 3 times etc.

[deleted]

And that isn't what you want?

Don't you want a unique number for each user and use that same number for all the options for that user?

Anon

i want the user_id, to be copied to each record. If user 1 selects options a,b,c then table 1 will have

001,username,address

table 2 will get

001,option_a
001,option_b
001,option_c

if user 2 selects b,d,e,f table 1 will get..

002,username,address

table 2 will get

002,option_b
002,option_d
002,option_e
002,option_f

I got it to do just 1, but I couldn't find any help on getting it to do more! I can't get it to do more than 1 mysql_insert_id becuase table 2 has a unique primary key which auto_increments as well!

[deleted]

First, you only have to do one mysql_insert_id() to get the id from the insert into the first table.

What happens in the second is irrelevant

Second, Why the flip is the id column in the seconds table an auto_increment column if you never use the auto_increment feature anyway?

Anon

Have you try something like this:

INSERT INTO tbl2 (user_id,option) VALUES (last_insert_id(), opt1), (last_insert_id(), opt2),(last_insert_id(), opt3);

this should work for mysql... 🙂

Anon

It might work, but I'd have to code a fixed number of options people enter, but the trouble is, its a variable, some people might enter 1, some 2 some even 10. So I need a way of getting it to add user_id only when an option in added!

Anon

I do use the auto_increment feature in the 2nd table, I just didn't explain how, to simplfiy the situation. Sorry!

All I want is the user_id from the 1st table copied to the 2nd table for every option selected, (which varies, could be 1, could be 10).
Very sorry if i have not made myself clear, or have pissed anyone off! I only started programming php a week ago.

Anon

I don't see any problem there, all you have to do is formula the query first, eg:

// Do you insert into the 1st table
INSERT INTO tbl1 ...

$query_string = "INSERT INTO tbl2 (user_id, opt) VALUES ";

// Assuming the options are stored in an array
for ($i=0; $i<count($opt); $i++) {
$query_string .= ' (last_insert_id(), '.$opt[$i].'),';
}

$query_string = ereg_replace(",$", '', $query_string);

// Now execute insert....
$res = mysql_query($query_string);

toma42

Change your table to be

ref_user int,
ref_option int,
primary key (ref_user, ref_option)

That cross reference approach will work. Basicly, drop the auto increment from the option table because it serves no use at all (at least it shouldn't)