Multiple SQL insertions in one query

Question

HI All, I'm concerned, I have an array coming in from a server. The array is then translated into variables. Such as:

$fpage = $_SESSION['scores'];

$score1 = $fpage['0'];
$score2 - $fpage['1'];
//Start of Queries


 // This is what I would like to do, but Its not working: 
$sql ="INSERT INTO Score (ScoreID,ResponseID,AssessorID,CriteriaID,Score,StudentID)"
       . "VALUES ('$rand','$rand2','$adminname','1-22','$score1', '$student')"; 

           $new = mysql_query($sql, $db);
$sql2 ="INSERT INTO Score (ScoreID,ResponseID,AssessorID,CriteriaID,Score,StudentID)"
       . "VALUES ('$rand','$rand2','$adminname','1-21','$score2', '$student')"; 

           $new1 = mysql_query($sql3, $db);
$sql3 ="INSERT INTO Score (ScoreID,ResponseID,AssessorID,CriteriaID,Score,StudentID)"
       . "VALUES ('$rand','$rand2','$adminname','1-21','$score3', '$student')"; 

           $new2 = mysql_query($sql3, $db);

My question is, What is the best way of doing this. I have tried to loop the queries, but I could not get that to work. What is the best way to accomplish this?

How do you know it's not working? error messages? DB not generated? — Andreas Wong
– Andreas Wong, Commented Dec 3, 2010 at 3:45
First you need to debug your code do that by adding or die(mysql_error()); to each query likst such mysql_query($sql, $db) or die(mysql_error()); — Breezer
– Breezer, Commented Dec 3, 2010 at 3:48

Jon Seigel · Accepted Answer · 2010-12-03 03:51:54Z

4

Well, there is a typo in the middle call to mysql_query -- you passed in $sql3 instead of $sql2.

Also, you can do this:

INSERT INTO Score (ScoreID, ResponseID, AssessorID, CriteriaID, Score, StudentID)
    VALUES
        (score1, response1, assessor1, criteria1, etc.),
        (score2, response2, assessor2, criteria2, etc.),
        (score3, response3, assessor3, criteria3, etc.),
        etc.

which will only hit the database once for all the inserts instead of once per insert.

Finally, your code appears to be vulnerable to SQL injection.

edited Dec 3, 2010 at 3:51

answered Dec 3, 2010 at 3:46

Jon Seigel

12.5k8 gold badges61 silver badges93 bronze badges

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

2 Comments

Dan Grossman Over a year ago

You should do as Jon suggested as far as the extended INSERT syntax. It is much faster than issuing a separate query for each row you want to add. You will loop through your array to build the query, then send it all at once. If it's too large for a single query (depends on max_allowed_packet setting), you can break it up into several queries, but still far less than one per entry if the array is large.

Ryan Over a year ago

Thank you, the SQL injection aspect will come after my proof of concept. But that will work a lot better. Thanks again!

Collectives™ on Stack Overflow

Multiple SQL insertions in one query

1 Answer 1

2 Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

2 Comments

Your Answer

Sign up or log in

Post as a guest

Related