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

Question

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

The queries themselves (apart from the SQL injection problems) seem to be fine. It would probably help to see more source code to see how the queries are being executed. — Kibbee
– Kibbee, Commented Mar 15, 2010 at 17:37
I have updated this question with more information as requested! — Yvonne
– Yvonne, Commented Mar 15, 2010 at 18:11
project ID is going into the projects table correctly? It doesn't change between the two queries, so you may want to examine the usersprojects table and make sure that you defined the second column with the right type. — Chad Birch
– Chad Birch, Commented Mar 15, 2010 at 18:15
Yes project ID is going into project table correctly (as the orimary key). In the usersprojects table user ID and project ID are both set as INT types (I am using phpmyadmin). I still can't see why this would happen though? Would it be anything to do with the fact that project ID is being called in twice within the same script??? Also, the project ID field is the only data not being sent across from the html form, but this is obviously because of its auto incremented.. — Yvonne
– Yvonne, Commented Mar 15, 2010 at 18:26
Sorry just to add, as I am pretty new to this stuff, I have done some searching on foreign keys in MySQL, I am using the table structure of MyISAM, do you know if this would effect the outcome? As I have read that InnoDB is the best type for foreign key insertions? However, the only difference is in beind the fact you can't manually insert values into foreign key tables, but hey I don't know!! :( — Yvonne
– Yvonne, Commented Mar 15, 2010 at 18:30

Itay Moav -Malimovka · Accepted Answer · 2010-03-15 22:59:07Z

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.

edited Mar 15, 2010 at 22:59

answered Mar 15, 2010 at 17:42

Itay Moav -Malimovka

53.8k68 gold badges198 silver badges292 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Peter Tirrell · Accepted Answer · 2010-03-15 17:48:38Z

0

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.

answered Mar 15, 2010 at 17:48

Peter Tirrell

3,0134 gold badges33 silver badges54 bronze badges

2 Comments

Yvonne Over a year ago

Can you have a look again, I have added more information to the post, I thought it was where I hadn't executed the query each time like you said...

Yvonne Over a year ago

Hi, do you know how I can do this? (print or debug) variables? Its only the project ID not being stored in usersprojects table, so close!!!

bsandrabr · Accepted Answer · 2010-03-15 18:17:14Z

0

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

answered Mar 15, 2010 at 18:17

bsandrabr

1432 silver badges8 bronze badges

1 Comment

Yvonne Over a year ago

No sorry I did have this for a split second when I was editing the example code above! That would bhave been quite embaressing :)

Yvonne · Accepted Answer · 2010-03-15 19:21:18Z

0

$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!!

answered Mar 15, 2010 at 19:21

Yvonne

1557 silver badges15 bronze badges

Comments

eswald · Accepted Answer · 2010-03-15 19:48:10Z

0

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.

answered Mar 15, 2010 at 19:48

eswald

8,4145 gold badges31 silver badges28 bronze badges

Collectives™ on Stack Overflow

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

5 Answers 5

Comments

2 Comments

1 Comment

Comments

Comments

Your Answer

Linked

Hot Network Questions

Collectives™ on Stack Overflow

5 Answers 5

Comments

2 Comments

1 Comment

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Linked

Related