PHP MySQL INSERT Query problem

not storing single value in database

[scankified]

Hi, basically, I have 3 tables; users and projects (which is a many-to-many relationship), then I have 'usersprojects' to allow the one-to-many formation. When a user adds a project, I need the project information stored and then the 'userid' and 'projectid' stored in the usersprojects table. It sounds like its really straight forward but I'm having problems with the syntax I think!?

As it stands, I have this as my INSERT queries (values going into 2 different tables):

$projectid = $_POST['projectid'];
 $projectname = $_POST['projectname'];
 $projectdeadline = $_POST['projectdeadline'];
 $projectdetails = $_POST['projectdetails'];

 $userid = $_POST['userid'];

$sql = "INSERT INTO projects (projectid, projectname, projectdeadline, projectdetails) VALUES
 ('{$projectid}','{$projectname}','{$projectdeadline}','{$projectdetails}')";

$result = mysql_query($sql, $connection)
 or die("MySQL Error: ".mysql_error());


$sql = "INSERT INTO usersprojects (userid, projectid) VALUES
 ('{$userid}','{$projectid}')";

$result = mysql_query($sql, $connection)
 or die("MySQL Error: ".mysql_error());
header("Location: managerhome.php");
exit();

What happens is the project information is stored all correctly, and the user ID is stored in usersprojects table, but for some reason the project ID is not being stored in usersprojects, this is now the only problem and I have looked clsely at everything, including even the field types for project ID in the usersproject table, however I cannot see anything wrong with it. Anyone have any ideas?

[scankified]

Sorry I forgot to mention that the value being stored as project ID is actually '0', so it must be that the auto number of project ID after submitting the form, is not being recognised by the 2nd query?

[Anyango]

Welcome to WebMasterWorld scankified

What value gets stored for projectid in userprojects? 0?

[Anyango]

Do you want to save this project id

$projectid = $_POST['projectid'];

in userprojects, or you want to save the projectid created after execution of your first query ?

[scankified]

Hi, thanks for your welcome :)

Yes the value that is stored in usersprojects table is '0' for project ID. (user ID is stored fine)... The project ID is stored fine in projects table with its auto incremented number.. I want this same number to be stored in usersprojects, but from what I gather its not being saved or seen by the second query to store it in usersprojects. Thanks for your help

[Anyango]

$projectid=mysql_insert_id($connection);

call that after your first query, this will get the AutoIncrement value from your projects table and store it in $projectid, then your second query will use it.
so after execution of your first query, try that function call. without changing anything else

[scankified]

This worked perfectly, thank you so much for your help! I knew what I was looking for, just didn't know the syntax :)

[Anyango]

Thats why i was tempted to reply fast, you seemed to know what you wanted :)