if exsists use id if not insert and use id.

[brown2005]

i have two tables....

 

1) people

 

people_id

people_name

 

2) names

 

names_id

names_name

 

now when someone enters their name on the register form and process i want to check if the name is in the names table and if so use that id as people_name in the people table, else insert the new name in the names table and use the id.

 

 

[cypher86]

you want to do it in php or in mysql?

[brown2005]

what would be easier?

 

I will have the registration form.. then on submit will go to register-process.php which sets up the registration.

[trq]

You already described what you must do, have you tried writing some code that actually does what you describe?

[cypher86]

php way:

mysql_query('insert into names values (.......)');
$result=mysql_query('select names_id from name where names_name=.....');
$row=mysql_fetch_array($result);
mysql_query('insert into people values(....,$row[names_id])')

 

mysql way

create trigger your_trigger
after insert on names
for each row
insert into people values (new.names_id,new.names_name)
end

 

the above code iif the name does not exists on names.

to apply completely your request you have to add somes if here and there in the code.

[brown2005]

$postedname = $_POST['name'];

$name = mysql_query("SELECT names_id, names_name FROM names WHERE names_name='$postedname'");

while($row = mysql_fetch_array($name)){

if ( mysql_row_count($name) == 0 ) {

	mysql_query("INSERT INTO names(names_id,names_name) VALUES('','$postedname')");

	$insert_name = mysql_insert_id();

}else{

	$insert_name = $row['names_id'];


}

}

 

then $insert_name would go into the people table

[trq]

Is this solved then or do you have a question?

[brown2005]

is how I have wrote the code the best/fastest way of doing it? or is there a better way?

 

[trq]

That is pretty much the standard way of doing it. You could take  a look at MySql's INSERT ... ON DUPLICATE KEY UPDATE

 

See http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html