Insert multiple records from array

neztip · Jul 8, 2008

Hello All!

I'm a complete newb in PHP/MySQL/Apache and I'm volunteering for a non-profit organization, I had expected to spend a couple of hours on the site and I'm about 15 hours in already.

I'm trying to create an attendance sheet and post it to an event table. The code to create the 'form' is:

<?php
$connect = mysql_connect("localhost","un","pw");
if (!$connect)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("cha", $connect);
$query = mysql_query("SELECT pkid, concat(firstname, ' ', lastname) as name FROM people order by name" ) or die (mysql_error());

echo "<html>";
echo "<body>";
echo "<form action='/insertattend.php/' method='post'>";
echo "<TABLE BORDER='1' ALIGN='CENTER' CELLPADDING='2' CELLSPACING='2' WIDTH='50%'>
<tr>
<th></th>
<th>Student</th>
<th>Attended</th>
</tr>";

while($row = mysql_fetch_array($query))
{
echo "<tr><td>";
echo '<input type="hidden" name="pkid[]" value="' . $row['pkid'] . '" >';
echo "</td>";
echo "<td>" . $row['name'] . "</td>";
echo "<TD><select name='attended[]'><option value=1>Yes</option><option value=0>No</option></select></TD></TR>";
}
echo "</table>
<TABLE BORDER='0' ALIGN='CENTER' CELLPADDING='4' CELLSPACING='4' WIDTH='50%'>
<TR><TD ALIGN='CENTER'><input type='submit' value= 'Submit Attendance' /></TD></TABLE></form></body></html>";
?>

Then the post script is:

<?php
$connect = mysql_connect("localhost","un","pw");
if (!$connect)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("cha", $connect);

$type = $_POST["pkid"];
foreach($type as $key => $value){
echo $key.": ".$value;
}
?>

This gives me the following: 0: 11: 102: 93: 154: 355: 116: 47: 38: 259: 1310: 3411: 2412: 613: 3614: 2615: 516: 717: 1618: 819: 1420: 1221: 3722: 2723: 1724: 1825: 2826: 3827: 2928: 1929: 3930: 4031: 2032: 3033: 4134: 3135: 2136: 2237: 3238: 4239: 3340: 2341: 2

I'm not sure what those values are. I would expect the list of pkid's: 1:2:3:4:5:6... as there are only 45 children in the database.

I also need to insert other values i.e. whether or not they attended (yes or no), what program they're in will be a hidden value passed from the form as well as the site and lesson code.

Is this possible in PHP?

I truly appreciate any help and this functionality for the np-org I'm volunteering for will save massive amounts of time.

JP

neztip · Jul 8, 2008

It was the SQL that was bad. I didn't set the defaults. Now my only question is how can I place multiple post values in the array. Again, any help is much appreciated.

This is what I now have in the post:

<?php
$connect = mysql_connect("localhost","un","pw");
if (!$connect)
{
die('Could not connect: ' . mysql_error());
}
mysql_select_db("cha", $connect);

$type = $_POST["pkid"];
foreach($type as $key => $value){
mysql_query("INSERT INTO event (peopleid, programid, eventdate, attended, siteid, eventid)
values ($value, 1, curdate(), 1, 1, 1)");
}
?>

I need to replace the 1's with hidden input values from the form.

JP

leatherback · Jul 9, 2008

A few comments:

You echo out 'attended[]', and a hidden field for the ID. In itself, this is fine. however, you could adjust the code somewhat: Also echo out the record number:

echo "attending[".$row['pkid']."]";

This will create an array where the keys are preset to your PKID values.

I presume that in your table you want to only store the people with 'yes':

// An array to store the records that need inserting
$temp = array()

// check each submitted redio:
foreach($_POST['pkid'] as $pkid => $attending)
  {
  if($attending == 1)
    {
    // store the PKID for insert, and make sure it is an integer
    $temp[] = intval($pkid);
    }
  }

// generate the mysql:

// Store the current date
$t = curdate()

// Generate a string to place between the PKID values for full record inserts
// Any value you would like to be added in stead of the 1 you can replace here by using:
// $val = intval($_GET['SomeFieldID'])
// $glue = " , 1, $t, 1, 1, $val), ( "
$glue = " , 1, $t, 1, 1, 1), ( ";

// This will merg the individual entries in the $temp array (All PKID of attending ==1)
// into record inserts
$queriesubset = '(' . [man]implode[/man]($glue, $temp) . ", 1, $t, 1, 1, 1)";

// create the full SQL
$sql = "INSERT INTO event (peopleid, programid, eventdate, attended, siteid, eventid)
values ".$querysubset;

neztip · Jul 11, 2008

This is where I'm at:

Form Code:

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

mysql_select_db("cha", $connect);
$query = mysql_query("SELECT people.pkid, concat(firstname, ' ', lastname) as name
	FROM `people`
	inner join programenroll p on p.peopleid = people.pkid
	where $_POST[areapkid] = p.AreaID and $_POST[sitepkid] = p.SiteID
	and $_POST[prgmpkid] = p.PrgmID
	and $_POST[instpkid] = p.InstructorID") or die (mysql_error());

echo "<html>";
echo "<body>";
echo "<form action='/inserta.php/' method='post'>";
echo "<TABLE BORDER='1' ALIGN='CENTER' CELLPADDING='2' CELLSPACING='2' WIDTH='50%'>
<tr>
<th></th>
<th>Student</th>
<th>Attended</th>
</tr>";
while($row = mysql_fetch_array($query))
  {
	echo "<tr><td><input type='hidden' name='pkid' value=".$row['pkid']."></td>";
  	echo "<td>" . $row['name'] . "</td>";
	echo "<TD><select name=attending[".$row['pkid']."] ";
	echo "<option value=0>No</option>";
	echo "<option SELECTED value=1>Yes</option>default = Yes</select></TD></TR>";
	echo "<input type='hidden' name='areapkid' value=$_POST[areapkid]>";
	echo "<input type='hidden' name='sitepkid' value=$_POST[sitepkid]>";
	echo "<input type='hidden' name='prgmpkid' value=$_POST[prgmpkid]>";
	echo "<input type='hidden' name='instpkid' value=$_POST[instpkid]>";
	echo "<input type='hidden' name='eventpkid' value=$_POST[eventpkid]>";
 }
echo "</table>
<TABLE BORDER='0' ALIGN='CENTER' CELLPADDING='4' CELLSPACING='4' WIDTH='50%'>
<TR><TD ALIGN='CENTER'><input type='submit' value= 'Submit Attendance' /></TD></TABLE></form></body></html>";
?>

Post:

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

print_r($_POST);

$temp = array();

foreach($_POST['pkid'] as $pkid => $attending){
  if($attending == 1)
    {
    // store the PKID for insert, and make sure it is an integer
    $temp[] = intval($pkid);
    }
  }
/*
foreach($type as $key => $pkid, $typeb as $key => $attended){
mysql_query("INSERT INTO event (peopleid, programid, eventdate, attended, siteid, eventid)
values ($pkid, 1, curdate(), $attended, 1, 1)");
}
*/
?>

And the results + error:

Array ( [pkid] => 5 [attending] => Array ( [1] => 1 [2] => 1 [3] => 1 [4] => 1 [5] => 1 ) [areapkid] => 1 [sitepkid] => 1 [prgmpkid] => 1 [instpkid] => 1 [eventpkid] => 4 )
Warning: Invalid argument supplied for foreach() in C:\webs\CHA\inserta.php on line 13

Not sure where to go from here any further help would be much appreciated.
JP

neztip · Jul 13, 2008

All I can say is keep it simple....

Thanks for any help.

Insert multiple records from array

Nneztip

Nneztip

Lleatherback

Nneztip

Nneztip