在多个表中添加记录PHP MYSQL

Question

I have two tables, users and comments. I need to add firstname and lastname to users and the comments to the comments table with the user id as a foreign key in comments. I am using phpmyadmin to do the add the foreign key constrain and relationships.

This is my html form:

<form action="dbconnect.php" method="post">
Firstname: <input type="text" name="firstname"><br />
Lastname: <input type="text" name="lastname"><br />
Comments: <textarea cols="30" rows="5" name="comments"></textarea><br />
<input type="submit">
</form>

This is my php insert code:

mysql_select_db("test", $db_server);

$sql="INSERT INTO users (Firstname, Lastname)
VALUES
('$_POST[firstname]','$_POST[lastname]')";

$sql="INSERT INTO comments (Comment)
VALUES
('$_POST[comments]')";


if (!mysql_query($sql,$db_server))
    {
    die('Error' . mysql_error());   
    }
echo "1 record added" ;

mysql_close($db_server);

and this is the error i'm getting:

Error: Cannot add or update a child row: a foreign key constraint fails (test.comments, CONSTRAINT comments_ibfk_2 FOREIGN KEY (useridfk) REFERENCES users (id) ON DELETE CASCADE ON UPDATE CASCADE)

I am new to php and phpmyadmin so any help is appreciated.

Answer 1

First of all, you need to run the INSERT query on the users table so the user record is created.

mysql_select_db("test", $db_server);

$sql="INSERT INTO users (Firstname, Lastname)
VALUES
('$_POST[firstname]','$_POST[lastname]')";

mysql_query($sql);

if (!mysql_query($sql,$db_server))
    {
    die('Error' . mysql_error());   
    }
echo "1 record added" ;

You then need to retrieve the id of the newly created record on the users table

$newIdQuery = mysql_query("SELECT LAST_INSERT_ID() AS userId FROM users");
$newId = mysql_fetch_assoc($newIdQuery);

Then you can use that userId to add the comments, with the user id foriegn key properly populated.

Note: When building MySQL queries using the mysql_* functions, if you wrap a php variable in { } it will insert the value of the php variable into the SQL statement.

$sql="INSERT INTO comments (id, Comment)
VALUES
({$newId["userId"]}, '$_POST[comments]')";


if (!mysql_query($sql,$db_server))
    {
    die('Error' . mysql_error());   
    }
echo "1 record added" ;

mysql_close($db_server);