Inserting data into two tables in php

[raazaq]

Hi,

 

I am new to this forum and php. I have been trying to insert data into two separate tables but so far unable to do so. With the code I might be able to explain it bit clearer.

 

I have got two tables user and registeruser with the following columns; user (customerid (pk), name, telephone, email, address, zip) and registeruser (username, password, customerid). I am trying to register user by inserting data into tables user and userid. my code is as follows:

 

// Insert data into mysql

 

$sql="INSERT INTO user (userid, name, telephone, email, address, zip)VALUES(NULL, '$name', '$telephone', '$email', '$address', '$zipcode')";

$result=mysql_query($sql);

 

$sql = "INSERT INTO registeruser (username, password, userid)VALUES('$username', '$password', NULL)";

$result = mysql_query( $sql);

 

But this doesnot work. Any suggestions, how to insert data into two separate tables using PHP?

 

Thanks

[JD*]

change this line:

 

$result=mysql_query($sql);

 

to this

 

$result=mysql_query($sql) or die(mysql_error());

 

To see if you're receiving an error

[Kenny Pollock]

Personally I don't use variables when I use mysql_query()

 

Also, you're using the same variables twice

 

Try this:

$sql1 = "INSERT INTO user (userid, name, telephone, email, address, zip) VALUES (NULL, '$name', '$telephone', '$email', '$address', '$zipcode')";
mysql_query( $sql1 );

$sql2 = "INSERT INTO registeruser (username, password, userid) VALUES ('$username', '$password', NULL)";
mysql_query( $sql2 );

[raazaq]

Thanks for all your replies  .

 

I have tried to use both options posted above (i.e. die(mysql_error()); and mysql_query( $sql ) function) and they seem to improve the scenario. Apart from userid column in table user and registeruser. It is a primary key in user table and foreign in other and it set to 'auto increment'. But its not incrementing. My new code is as below:

 

$sql1="INSERT INTO user (userid, name, telephone, email, address, zip)VALUES(NULL, '$name', '$telephone', '$email', '$address', '$zipcode')";
mysql_query($sql1) or die(mysql_error());

$sql2 = "INSERT INTO registeruser (username, password, userid)VALUES('$username', '$password', NULL)";
mysql_query( $sql2) or die(mysql_error());

[duclet]

First of all, did you set up the table to autoincrement?

[raazaq]

Yes I have.... Infact <b>userid</b> in 'user' table is set to autoincrement and its increasing, whereas 'registeruser' table is unaffected. Any solutions? Thx....

[Kenny Pollock]

Looks like you'll want to use PHP's mysql_insert_id() function in the second statement.

 

It will use the ID from the first query and use it in the second query.

 

$sql1 = "INSERT INTO user (userid, name, telephone, email, address, zip) VALUES (NULL, '$name', '$telephone', '$email', '$address', '$zipcode')";
mysql_query( $sql1 );

$sql2 = "INSERT INTO registeruser (username, password, userid) VALUES ('$username', '$password', mysql_insert_id())";
mysql_query( $sql2 );

[raazaq]

I have tried the code with 'mysql_insert_id()' function but am afraid it is displaying the following error: "Duplicate entry '0' for key 1"... any suggestions?

[BlueSkyIS]

i would avoid using 2 tables and append username and password fields on the user table.

[sasa]

what is pk in your table registeruser

userid is fk, isn't it?