Hey, so I have a form and i want to break down the form into 2 sections when inserting into my database. I have made 2 tables to hold my values.

this is my SQL statements.

$sql_insert1 = "INSERT INTO `agent_users`
	$sql_insert2 = "INSERT INTO `agent_company`

	mysql_query($sql_insert1,$link) or die("Insertion Failed:" . mysql_error());
	$user_id = mysql_insert_id($link);  
	$md5_id = md5($user_id);
	mysql_query("UPDATE `agent_users` SET md5_id='$md5_id' WHERE id='$user_id'");
	mysql_query($sql_insert2,$link2) or die("Insertion Failed:" . mysql_error());
	$user_id2 = mysql_insert_id($link);  
	//$new_id = mysql_query("SELECT `id` FROM `agent_users` WHERE id='$user_id'");
	mysql_query("UPDATE `agent_company` SET company_id='$user_id'");

I was trying to insert my values into my agent_users table and then also insert their company information into my agent_company table.

My logic was in order for me to connect these tables to one member or user what ever you want to call it. to be connected by the agent_user `id` field, which is set to auto_increment.

What would I need to do get the `id` number to match up with the agent_company `id` number? hmmm, both id's are set to auto and primary keys.

Also, i made a company_id and thought of setting it up as a foreign key but I don't really understand foreign keys just yet... so do i need it right now?? or its something that i must have to connect both these tables to one user/member?

any help is appreciated!


Follow steps given below
1) Keep same column in both table with same data type say AGENT_ID.
2) keep this as primary key in both tables
3) In agent_users table keep it as auto_increment, DO NOT set agent_id as auto_increment in agent_company table.
4) In your mysql code first insert into agent_users table
5) save id in $user_id variable using mysql_insert_id($link)
6) now insert into agent_comapany table like

"insert into agent_company (agent_id,exp_age_yr, exp_age_month.........)
values ('$user_id}',$data[exp_age_yr]', '$data[exp_age_mo]',)";


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.