Insert multiple rows at once

[adap]

<form action="insert1.php" method="post">

<table width="77%">

<td height="63" colspan="5"><h3>Other involvement during high school, college (clubs, sports, work, volunteer, etc.): </h3></td>

</tr>

<tr>

<td width="20%"><h3>Activity</h3></td>

<td width="19%"><h3>Position</h3></td>

<td width="23%"><h3>Start Date</h3></td>

<td width="25%" height="60"><h3>End Date</h3></td>

</tr>

<tr>

 

<td height="63"><input name="Activity[]" type="text" id="Activity[]" size="15" />

<td height="63"><input name="Position[]" type="text" id="Position[]" size="15" />

<td height="63"><input name="StartDate[]" type="text" id="StartDate[]" size="15" />

<td height="63"><input name="EndDate[]" type="text" id="EndDate[]" size="15" />

</tr>

<tr>

 

<td height="63"><input name="Activity[]" type="text" id="Activity[]" size="15" />

<td height="63"><input name="Position[]" type="text" id="Position[]" size="15" />

<td height="63"><input name="StartDate[]" type="text" id="StartDate[]" size="15" />

<td height="63"><input name="EndDate[]" type="text" id="EndDate[]" size="15" />

</tr>

<tr>

 

<td height="63"><input name="Activity[]" type="text" id="Activity[]" size="15" />

<td height="63"><input name="Position[]" type="text" id="Position[]" size="15" />

<td height="63"><input name="StartDate[]" type="text" id="StartDate[]" size="15" />

<td height="63"><input name="EndDate[]" type="text" id="EndDate[]" size="15" />

</table>

<p> </p>

<p>

 

<input type="submit" name="Submit" id="Submit" value="Submit" />

 

</p>

</form>

 

 

 

Here is the insert1.php file

<?php

$con = mysql_connect("localhost","Application","*******");

if (!$con)

{

die('Could not connect: ' . mysql_error());

}

 

mysql_select_db("CpaApp", $con);

 

 

//Assign each array to a variable

foreach($_POST['Activity'] as $row=>$Act)

{

$Activity=$Act;

$Position=$_POST['Position'][$row];

$StartDate=$_POST['StartDate'][$row];

$EndDate=$_POST['EndDate'][$row];

}

 

 

//enter rows into database

foreach($_POST['Activity'] as $row=>$Act)

{

$Activity=mysql_real_escape_string($Act);

$Position=mysql_real_escape_string($_POST['Position'][$row]);

$StartDate=mysql_real_escape_string($_POST['StartDate'][$row]);

$EndDate=mysql_real_escape_string($_POST['EndDate'][$row]);

}

 

 

$involv = "INSERT INTO Involvement (Activity, Position, StartDate, EndDate)

VALUES ('.$Activity.','.$Position.','.$StartDate.','.$EndDate.')";

 

 

if (!mysql_query($involv,$con))

{

die('Error: ' . mysql_error());

}

echo "$row record added";

 

mysql_close($con)

?>

 

Hi, this is my code. The problem is.

When i try to 5 rows at a time in the database, but it only inserts every

5th record. For example:

1. AA

2. AB

3. AC

4. AD

5. AE

 

Only the fifth record which is AE stored in my mysql database.

 

How can I insert multiple rows at once ???

 

Will be thankful if someone could help me.

 

Adap

 

[Mchl]

Please DO use code

 and 

tags around code you insert. It helps reading a lot.

 

Your insert query is run only for variables for fith and the last record. You should rather do it this way.

 

$sql = "
  INSERT INTO Involvement (Activity, Position, StartDate, EndDate) VALUES
";

foreach($_POST['Activity'] as $row=>$Act) {
  $Activity=mysql_real_escape_string($Act);
  $Position=mysql_real_escape_string($_POST['Position'][$row]);
  $StartDate=mysql_real_escape_string($_POST['StartDate'][$row]);
  $EndDate=mysql_real_escape_string($_POST['EndDate'][$row]);

  $sql .= "('$Activity','$Position','$StartDate','$EndDate'),"
}

$sql = substr($sql,0,-1); //this removes last comma

if (!mysql_query($sql,$con)) {
  die(mysql_error().": $sql");
}

echo "$row record added";

mysql_close($con);

[adap]

$sql .= "('$Activity','$Position','$StartDate','$EndDate'),"

For this statement, it says is parse error.

Any idea how to solve it?

 

[Andy-H]

<form action="insert1.php" method="post">
<table width="77%">
<td height="63" colspan="5"><h3>Other involvement during high school, college (clubs, sports, work, volunteer, etc.): </h3></td>
</tr>
<tr>
<td width="20%"><h3>Activity</h3></td>
<td width="19%"><h3>Position</h3></td>
<td width="23%"><h3>Start Date</h3></td>
<td width="25%" height="60"><h3>End Date</h3></td>
</tr>
<tr>

<td height="63"><input name="Activity[]" type="text" id="Activity[]" size="15" />
<td height="63"><input name="Position[]" type="text" id="Position[]" size="15" />
<td height="63"><input name="StartDate[]" type="text" id="StartDate[]" size="15" />
<td height="63"><input name="EndDate[]" type="text" id="EndDate[]" size="15" />
</tr>
<tr>

<td height="63"><input name="Activity[]" type="text" id="Activity[]" size="15" />
<td height="63"><input name="Position[]" type="text" id="Position[]" size="15" />
<td height="63"><input name="StartDate[]" type="text" id="StartDate[]" size="15" />
<td height="63"><input name="EndDate[]" type="text" id="EndDate[]" size="15" />
</tr>
<tr>

<td height="63"><input name="Activity[]" type="text" id="Activity[]" size="15" />
<td height="63"><input name="Position[]" type="text" id="Position[]" size="15" />
<td height="63"><input name="StartDate[]" type="text" id="StartDate[]" size="15" />
<td height="63"><input name="EndDate[]" type="text" id="EndDate[]" size="15" />
</table>
<p> </p>
<p>

<input type="submit" name="Submit" id="Submit" value="Submit" />

</p>
</form>

 

 

 

<?php
$con = mysql_connect("localhost","Application","*******");
if (!$con)
{
die('Could not connect: ' . mysql_error());
}

mysql_select_db("CpaApp", $con);

//enter rows into database
foreach($_POST['Activity'] as $row=>$Act)
{
$Activity=mysql_real_escape_string($Act);
$Position=mysql_real_escape_string($_POST['Position'][$row]);
$StartDate=mysql_real_escape_string($_POST['StartDate'][$row]);
$EndDate=mysql_real_escape_string($_POST['EndDate'][$row]);



$involv = "INSERT INTO Involvement (Activity, Position, StartDate, EndDate)
VALUES ('.$Activity.','.$Position.','.$StartDate.','.$EndDate.')";


if (!mysql_query($involv,$con))
{
die('Error: ' . mysql_error());
}
}
echo $row . " record(s) added";

mysql_close($con)
?>

[adap]

Thanks for all the helps. It works. Million thanks!

By the way, would like to ask.

How to make sure that the ID will go into the database accordingly?

For example, if i already have 10 datas with id 1-10.

Then i would like to add more. When i key in 11,12,13.

It might randomly slotted into my database.

Any idea?

[Andy-H]

I'm not sure thats possible without adding another query and a call to mysql_num_rows/fetching COUNT(*) and adding one for the id insert variable; in effect adding unnecessary server overhead. If you wish to search for certain records you can make use of the mysql search function, which can be used in PHPmyadmin (assuming your server uses it).