dhali dhali - 4 months ago 20
PHP Question

How to send data to two tables with same user ID as primary key?

I am totally new to PHP and I'm trying to create a registration page. To register, a user has to create a username, password, email, which are put into a table called

users
.

They also enter address details which is put into a table called
customer_info
.

In both tables I have created an auto increment, primary key called 'user_id'.

When the form is completed it fills out and enters the data, but the data is not banded and so there are two user_id, one in
users
and one in
customer_info
.

First I create values (from the post) that have been entered and assign them to variables. Then I put the variables into my table using the following query:

$result = mysql_query(
"INSERT INTO `users`(username, password, email) VALUES ('$value1', '$value2','$value3')"
);


and

$result = mysql_query(
"INSERT INTO `customer_info`(firstname, lastname, b_add_num, b_add_road, b_add_town, b_add_pc, p_add_num, p_add_road, p_add_town, p_add_pc) VALUES ('$value4','$value5','$value6','$value7','$value8','$value9','$value10','$value11','$value12','$value13')"
);


How would I set it so that it creates only one user id for both tables (making a connection between the sets of data)?

Is there something missing in my query, that should connect the tables?

Answer

Before anything, you should not use mysql_* extension anymore. Go towards PDO or mysqli


This technique would generate two unique ids for information that should be related together. The point is to have only one, so that it can be unique, and link information on that unique id.

The users table is the one with that unique id, user_id, which is your auto_increment column. The customer_info table can also have a info_id unique column, but must contain a user_id column, which will contain the user's user_id, linking the rows together.

It would also be a great moment to add a foreign key to your tables so that integrity of the data won't be compromised.

so after this query:

$result = mysql_query(
    "INSERT INTO `users`(username, password, email) VALUES ('$value1', '$value2','$value3')"
);

get the insert id:

$id = mysql_insert_id();

then run your other query with it:

$result = mysql_query(
    "INSERT INTO `customer_info`(user_id,firstname, lastname, b_add_num, b_add_road, b_add_town, b_add_pc, p_add_num, p_add_road, p_add_town, p_add_pc) VALUES ('$id','$value4','$value5','$value6','$value7','$value8','$value9','$value10','$value11','$value12','$value13')"
);