Run MySQL INSERT Query multiple times (insert values into multiple tables)

Question

Hi, basically, I have 3 tables; users and projects, then I have 'users_projects' 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'];
    $pname = $_POST['pname'];
    $pdeadline = $_POST['pdeadline'];
    $pdetails = $_POST['pdetails'];

    $userid = $_POST['userid'];

$sql = "INSERT INTO projects (projectid, pname, pdeadline, pdetails) VALUES
   ('{$projectid}','{$pname}','{$pdeadline}','{$pdetails}')";


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

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

Answer 1

You simply forgot to execute the sql between each query. Add the

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

between each query and you are supposed to be fine.

b.t.w (1) it always helpful to test with a console open with tail -f on the sql log (under /var/log/mysql/ )
b.t.w.(2) You are having heavy security issues in your code.
b.t.w (3) You might want to consider using PDO/Mysqli and not the old mysql extension. b.t.w (4) It would make your life simpler to use some kind of wrapper (a good class) to approach the DB and not do it directly everywhere in your code.

Answer 2

Yeah, two things I would check would be

1) are the queries being executed? Like the other poster mentiond, are you executing the SQL queries in between setting the SQL?

2) if you print/debug/display somehow the variables that you are inserting, are they getting populated? If you're seeing things get inserted, but some of the data is blank then something might be blowing up before that and those variables would be blank.

Answer 3

I may be misunderstanding but are you putting header("Location: main.php"); in the middle of you script?

Answer 4

$projectid=mysql_insert_id($connection); 

I called this after my first query, this will get the AutoIncrement value from your projects table and store it in $projectid, then the second query will use it. so after execution of my first query, I put the above code there, without changing anything else!!

Answer 5

You seem to be trying to execute mysql_query() only once. You have two queries, so it needs to be used twice, once on each query:

$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());

Alternatively, you could use mysqli_multi_query(), but that might require a significant rewrite of your code to use the mysqli interface.