How to INSERT multiple rows into MySQL at once?

Joseph_Sliker

I'm so close I can almost taste it! But I'm stuck again.
My input form starts at this page:
http://www2.milwaukee.k12.wi.us/psi/xphp/scoreenter5.htm
(Choose value "Happy Trails (Fictional)")
Enter grade "3"
Room "P032"
and a date before clicking Submit:
That will take you to:
http://www2.milwaukee.k12.wi.us/psi/xphp/scoreenter5.php
which presents the user with a data entry sheet for a whole specific classroom
The Submit button takes you to a page that shows the data that was submitted...but the data does not actually get put in the database...that's what I need to do..
I obviously need an INSERT INTO statement, but I think it needs to be somehow rolled into a FOREACH loop and I just can't figure out the syntax

Here is the code for the final "insert" php page:

<HTML>
<HEAD>
<TITLE>php result from scoreenter5.php form</TITLE>
</HEAD>
<BODY>
<?php
// Set the variables for the database access:
$Host = 'localhost';
$User = '****';
$Password = '*****';
$DBName = '*****';
$TableName = 'DATAexp';

//This is from scorenter5.php
$SCHOOLID = $HTTP_POST_VARS['SCHOOLID'];
$GRADE = $HTTP_POST_VARS['GRADE'];
$ROOM = $HTTP_POST_VARS['ROOM'];
$MPSID = $HTTP_POST_VARS['MPSID'];
$LAST = $HTTP_POST_VARS['LAST'];
$FIRST = $HTTP_POST_VARS['FIRST'];
$TESTDATE = $HTTP_POST_VARS['TESTDATE'];
$SCORE1 = $HTTP_POST_VARS['SCORE1'];
$SCORE2 = $HTTP_POST_VARS['SCORE2'];
$SCORE3 = $HTTP_POST_VARS['SCORE3'];
$SCORE4 = $HTTP_POST_VARS['SCORE4'];

for ($i=0; $i<count($SCHOOLID); $i++)
{
echo "('$SCHOOLID[$i]', '$GRADE[$i]', '$ROOM[$i]', '$MPSID[$i]', '$LAST[$i]', '$FIRST[$i]', '$TESTDATE[$i]', '$SCORE1[$i]', '$SCORE2[$i]', '$SCORE3[$i]', '$SCORE4[$i]'),";
}
;

print "<table align= \"center\" border=\"1\">";
print "<tr><td colspan=11><font face=\"arial\">You have just submitted the following data:</font></td></tr>";
print "<tr><td>SCHOOLID</td><td>GRADE</td><td>ROOM</td><td>MPSID</td><td>LAST</td><td>FIRST</td><td>TESTDATE</td><td>SCORE1</td><td>SCORE2</td><td>SCORE3</td><td>SCORE4</td></tr>";
for ($i=0; $i<count($FIRST); $i++)
{
print "<tr><td>$SCHOOLID[$i]</td><td>$GRADE[$i]</td><td>$ROOM[$i]</td><td>$MPSID[$i]</td><td>$LAST[$i]</td><td>$FIRST[$i]</td><td>$TESTDATE[$i]</td><td>$SCORE1[$i]</td><td>$SCORE2[$i]</td><td>$SCORE3[$i]</td><td>$SCORE4[$i]</td></tr>";
}
print "</table>";

$Link = mysql_connect($Host, $User, $Password);

//$Query = THIS IS WHERE I AM STUCK!


if (mysql_db_query($DBName, $Query, $Link)) {
 	print ("Got it. Thanks for the data!<BR>\n");
} else {
 	print ("Sorry. Didn't get the data.  Joe messed up the script!<BR>\n");
} 

mysql_close($Link);

?>
OKAY
</BODY>
</HTML>

Your help would again be much appreciated.😃

nnichols

Try something like this -

$sql = 'INSERT INTO tbl_name (col1, col2, col3, etc) VALUES';

for ($i=0; $i<count($FIRST); $i++) {
if $i = 0
	$sql .= " ('$SCHOOLID[$i]', '$GRADE[$i]', '$ROOM[$i]', '$MPSID[$i]', '$LAST[$i]', '$FIRST[$i]', '$TESTDATE[$i]', '$SCORE1[$i]', '$SCORE2[$i]', '$SCORE3[$i]', '$SCORE4[$i]')";
else
	$sql .= " ,('$SCHOOLID[$i]', '$GRADE[$i]', '$ROOM[$i]', '$MPSID[$i]', '$LAST[$i]', '$FIRST[$i]', '$TESTDATE[$i]', '$SCORE1[$i]', '$SCORE2[$i]', '$SCORE3[$i]', '$SCORE4[$i]')";
}

// then do insert
$res = mysql_query($sql, $db);

Hope this helps 😉

Joseph_Sliker

Your reply makes sense, but it still does not work. After a little trial and error I inserted it as:

//$Query = //THIS IS WHERE I AM STUCK!

//contribution PHPbuilder response:

$sql = 'INSERT INTO $TableName (SCHOOLID, GRADE, ROOM, MPSID, LAST, FIRST, TESTDATE, SCORE1, SCORE2, SCORE3, SCORE4) VALUES';

for ($i=0; $i<count($FIRST); $i++) {
if $i = 0
    $sql.=",('$SCHOOLID[$i]', '$GRADE[$i]', '$ROOM[$i]', '$MPSID[$i]', '$LAST[$i]', '$FIRST[$i]', '$TESTDATE[$i]', '$SCORE1[$i]', '$SCORE2[$i]', '$SCORE3[$i]', '$SCORE4[$i]')";
else
    $sql.=",('$SCHOOLID[$i]', '$GRADE[$i]', '$ROOM[$i]', '$MPSID[$i]', '$LAST[$i]', '$FIRST[$i]', '$TESTDATE[$i]', '$SCORE1[$i]', '$SCORE2[$i]', '$SCORE3[$i]', '$SCORE4[$i]'),";
}

// then do insert
$res = mysql_query($sql, $DBName);

//old stuff
if (mysql_db_query($DBName, $sql, $Link)) {
 	print ("Got it. Thanks for the data!<BR>\n");
} else {
 	print ("Sorry. Didn't get the data.  Joe messed up the script!<BR>\n");
} 

mysql_close($Link);

Is this supposed to be "for" or "foreach"

nnichols

it is supposed to be 'for'. I took it straight from the code that you use to display the data - assuming that it displays the data correctly.

nnichols

You should change this -

// then do insert 
$res = mysql_query($sql, $DBName); 

//old stuff 
if (mysql_db_query($DBName, $sql, $Link)) { 
print ("Got it. Thanks for the data!<BR>\n"); 
} else { 
print ("Sorry. Didn't get the data. Joe messed up the script!<BR>\n"); 
} 

mysql_close($Link);

to this -

// then do insert 
if (mysql_db_query($DBName, $sql, $Link)) { 
print ("Got it. Thanks for the data!<BR>\n"); 
} else { 
print ("Sorry. Didn't get the data. Joe messed up the script!<BR>\n"); 
} 

mysql_close($Link);

otherwise it will try to do the insert twice.

When you test the success/failure of the query you should

echo 'Error: ' . mysql_error();

if it fails. That way you should get a useful error message back.

Joseph_Sliker

Thanks for putting up with this and being so responsive.

Your last suggestion seems to have gotten me a little closer but it still does not quite work. The problem seems to be something with the if/else statement. If I eliminate that, the query seems to run (but still does not insert the data). If I leave the if/else statement in, the page does not run at all.

here is what I have plugged in at the moment:

//$Query = //THIS IS WHERE I AM STUCK!
//contribution

$sql = 'INSERT INTO $TableName (SCHOOLID, GRADE, ROOM, MPSID, LAST, FIRST, SCORE1, SCORE2, SCORE3, SCORE4) VALUES';

for ($i=0; $i<count($FIRST); $i++) {
/*if $i = 0
    $sql .= "('$SCHOOLID[$i]', '$GRADE[$i]', '$ROOM[$i]', '$MPSID[$i]', '$LAST[$i]', '$FIRST[$i]', '$SCORE1[$i]', '$SCORE2[$i]', '$SCORE3[$i]', '$SCORE4[$i]')";
else*/
    $sql .= "('$SCHOOLID[$i]', '$GRADE[$i]', '$ROOM[$i]', '$MPSID[$i]', '$LAST[$i]', '$FIRST[$i]', '$SCORE1[$i]', '$SCORE2[$i]', '$SCORE3[$i]', '$SCORE4[$i]'),";
}
echo 'Error: ' . mysql_error();

// then do insert
if (mysql_query($DBName, $sql, $Link)) { 
print ("Got it. Thanks for the data!<BR>\n"); 
} else { 
print ("Sorry. Didn't get the data. Joe messed up the script!<BR>\n"); 
}

mysql_close($Link);

(You may note that I took the TESTDATE field out of the code at this point because I think I am passing that data incorrectly...no hyphens.)

nnichols

Try this and tell me exactly what it does/doesn't do -

<HTML>
<HEAD>
<TITLE>php result from scoreenter5.php form</TITLE>
</HEAD>
<BODY>
<?php
// Set the variables for the database access:
$Host = 'localhost';
$User = '****';
$Password = '*****';
$DBName = '*****';
$TableName = 'DATAexp';

//This is from scorenter5.php
$SCHOOLID = $HTTP_POST_VARS['SCHOOLID'];
$GRADE = $HTTP_POST_VARS['GRADE'];
$ROOM = $HTTP_POST_VARS['ROOM'];
$MPSID = $HTTP_POST_VARS['MPSID'];
$LAST = $HTTP_POST_VARS['LAST'];
$FIRST = $HTTP_POST_VARS['FIRST'];
$TESTDATE = $HTTP_POST_VARS['TESTDATE'];
$SCORE1 = $HTTP_POST_VARS['SCORE1'];
$SCORE2 = $HTTP_POST_VARS['SCORE2'];
$SCORE3 = $HTTP_POST_VARS['SCORE3'];
$SCORE4 = $HTTP_POST_VARS['SCORE4'];

for ($i=0; $i<count($SCHOOLID); $i++) {
	echo "('$SCHOOLID[$i]', '$GRADE[$i]', '$ROOM[$i]', '$MPSID[$i]', '$LAST[$i]', '$FIRST[$i]', '$TESTDATE[$i]', '$SCORE1[$i]', '$SCORE2[$i]', '$SCORE3[$i]', '$SCORE4[$i]'),";
	}

print "<table align= \"center\" border=\"1\">";
print "<tr><td colspan=11><font face=\"arial\">You have just submitted the following data:</font></td></tr>";
print "<tr><td>SCHOOLID</td><td>GRADE</td><td>ROOM</td><td>MPSID</td><td>LAST</td><td>FIRST</td><td>TESTDATE</td><td>SCORE1</td><td>SCORE2</td><td>SCORE3</td><td>SCORE4</td></tr>";
for ($i=0; $i<count($FIRST); $i++) { 
	print "<tr><td>$SCHOOLID[$i]</td><td>$GRADE[$i]</td><td>$ROOM[$i]</td><td>$MPSID[$i]</td><td>$LAST[$i]</td><td>$FIRST[$i]</td><td>$TESTDATE[$i]</td><td>$SCORE1[$i]</td><td>$SCORE2[$i]</td><td>$SCORE3[$i]</td><td>$SCORE4[$i]</td></tr>";
	}
print "</table>";

$Link = mysql_connect($Host, $User, $Password);

//$Query = //THIS IS WHERE I AM STUCK!
//contribution

$sql = 'INSERT INTO $TableName (SCHOOLID, GRADE, ROOM, MPSID, LAST, FIRST, SCORE1, SCORE2, SCORE3, SCORE4) VALUES';

for ($i=0; $i<count($FIRST); $i++) {
if $i = 0
	$sql .= "('$SCHOOLID[$i]', '$GRADE[$i]', '$ROOM[$i]', '$MPSID[$i]', '$LAST[$i]', '$FIRST[$i]', '$SCORE1[$i]', '$SCORE2[$i]', '$SCORE3[$i]', '$SCORE4[$i]')";
else
	$sql .= ", ('$SCHOOLID[$i]', '$GRADE[$i]', '$ROOM[$i]', '$MPSID[$i]', '$LAST[$i]', '$FIRST[$i]', '$SCORE1[$i]', '$SCORE2[$i]', '$SCORE3[$i]', '$SCORE4[$i]')";
} 

// then do insert 
if (mysql_query($DBName, $sql, $Link))
	print ("Got it. Thanks for the data!<BR>\n");

else
	print ("Sorry. Didn't get the data. Joe messed up the script!<BR>\nMySQL error: " . mysql_error() . "<BR>\nQuery: $sql<BR>\n");

mysql_close($Link);

?>
OKAY
</BODY>
</HTML>

Good luck! 😉

Joseph_Sliker

very slight progress here.

When I use the code (just as you gave it), it returns a blank page...totally blank (If I use Netscape 4.7 the response is that the document contains no data).

BUT
when I take out the section of your loop from "if" to "else", the following takes up the bottom of the returned page (note, I only entered one score for each kid in different columns...just to see if it ends up in the right place):

Sorry. Didn't get the data. Joe messed up the script!
MySQL error:
Query: INSERT INTO $TableName (SCHOOLID, GRADE, ROOM, MPSID, LAST, FIRST, SCORE1, SCORE2, SCORE3, SCORE4) VALUES, ('195', '3', 'P032', '9248943', 'Englemann', 'Abraham', '1', '', '', ''), ('195', '3', 'P032', '9889290', 'Herpert', 'Al', '', '2', '', ''), ('195', '3', 'P032', '9605066', 'Duck', 'Darnold', '', '', '3', ''), ('195', '3', 'P032', '8536697', 'Day', 'Dee', '', '', '', '4'), ('195', '3', 'P032', '9105145', 'Jones', 'Filene', '5', '', '', ''), ('195', '3', 'P032', '7824451', 'Caroll', 'Joan', '', '6', '', ''), ('195', '3', 'P032', '8180574', 'Chevez', 'Mike', '', '', '7', ''), ('195', '3', 'P032', '9961189', 'Jones', 'Mike', '', '', '', '8'), ('195', '3', 'P032', '8321000', 'Berma', 'Stuffy', '9', '', '', ''), ('195', '3', 'P032', '9859290', 'Jefferson', 'Theloneous', '', '10', '', ''), ('195', '3', 'P032', '9829290', 'Garganin', 'Usama', '', '', '11', ''), ('195', '3', 'P032', '8892820', 'Defleur', 'Zeus', '', '', '', '12')
OKAY

I believe I understand what you are trying to accomplish with the if/else loop....the last entry will not have a trailing or leading comma.

nnichols

I've just noticed an error in the code I wrote -

for ($i=0; $i<count($FIRST); $i++) {
if $i = 0
    $sql .= "('$SCHOOLID[$i]', '$GRADE[$i]', '$ROOM[$i]', '$MPSID[$i]', '$LAST[$i]', '$FIRST[$i]', '$SCORE1[$i]', '$SCORE2[$i]', '$SCORE3[$i]', '$SCORE4[$i]')";
else
    $sql .= ", ('$SCHOOLID[$i]', '$GRADE[$i]', '$ROOM[$i]', '$MPSID[$i]', '$LAST[$i]', '$FIRST[$i]', '$SCORE1[$i]', '$SCORE2[$i]', '$SCORE3[$i]', '$SCORE4[$i]')";
}

should be -

for ($i=0; $i<count($FIRST); $i++) {
if ($i == 0)
    $sql .= "('$SCHOOLID[$i]', '$GRADE[$i]', '$ROOM[$i]', '$MPSID[$i]', '$LAST[$i]', '$FIRST[$i]', '$SCORE1[$i]', '$SCORE2[$i]', '$SCORE3[$i]', '$SCORE4[$i]')";
else
    $sql .= ", ('$SCHOOLID[$i]', '$GRADE[$i]', '$ROOM[$i]', '$MPSID[$i]', '$LAST[$i]', '$FIRST[$i]', '$SCORE1[$i]', '$SCORE2[$i]', '$SCORE3[$i]', '$SCORE4[$i]')";
}

Hope this helps 😉

Joseph_Sliker

YEEEEE Hah! It worked!

I never in a million years would have figured out "=="! If I had read it in a book I'm sure I would have glossed over it and only seen a single equal sign!

Thank you so much! I can get on with my life again! (I've been obsessed with this specific problem for over a month now).

Note: I somehow figured out another problem in the code:

$sql = 'INSERT INTO $TableName (SCHOOLID, etc. etc..

had to be changed to:

$sql = 'INSERT INTO '.$TableName .'(SCHOOLID, etc. etc..

Finally, are you actually familiar with this "mysql_db_query" function? I can't find a reference to it in any books or online sources I've seen so far. I've only seen "mysql_query"...which does not seem to work, for this project anyway.

Thanks again, very very much!
😃

nnichols

when using mysql_query() you have to select the db first using mysql_select_db() -

$db = mysql_connect('host', 'user', 'pass') or die('Failed to coonect to db server.<br>Error: ' . mysql_error());
mysql_select_db('db_name') or die('Failed to select db.<br>Error: ' . mysql_error());
$sql = 'SELECT blah blah';
$res = mysql_query($sql, $db) or die('Query failed.<br>Error: ' . mysql_error());

To find out more about the functions and their arguements download the chm version of the PHP Manual from php.net.

Hope this helps 😉

Joseph_Sliker

Ah;

I found the references and found that "mysql_db_query()" has been deprecated....which I suspect means it may disappear as soon as I get used to using it....

I rewrote the offending section of the foregoing project as:

$Link = mysql_connect($Host, $User, $Password);

mysql_select_db($DBName);

//THIS IS WHERE I AM NO LONGER STUCK THANKS TO NNICHOLS CONTRIBUTION

$sql = 'INSERT INTO '.$TableName .'(SCHOOLID, GRADE, ROOM, MPSID, LAST, FIRST, TESTDATE, SCORE1, SCORE2, SCORE3, SCORE4) VALUES';

for ($i=0; $i<count($FIRST); $i++) {
if ($i == 0)
    $sql .= "('$SCHOOLID[$i]', '$GRADE[$i]', '$ROOM[$i]', '$MPSID[$i]', '$LAST[$i]', '$FIRST[$i]', '$TESTDATE[$i]', '$SCORE1[$i]', '$SCORE2[$i]', '$SCORE3[$i]', '$SCORE4[$i]')";
else
    $sql .= ", ('$SCHOOLID[$i]', '$GRADE[$i]', '$ROOM[$i]', '$MPSID[$i]', '$LAST[$i]', '$FIRST[$i]', '$TESTDATE[$i]', '$SCORE1[$i]', '$SCORE2[$i]', '$SCORE3[$i]', '$SCORE4[$i]')";
}

// then do insert 
if (mysql_query($sql, $Link))
    print ("Got it. Thanks for the data!<BR>\n");

else
    print ("Sorry. Didn't get the data. Joe messed up the script!<BR>\nMySQL error: " . mysql_error() . "<BR>\nQuery: $sql<BR>\n");

mysql_close($Link);

...and it still works.

I should get into using those "die" statements. ...so much to learn...!