Help with multiple arrays going into a database

[grozanc]

I'm trying to dump three pieces of information into a database from a form, two of the pieces are coming from an array. I can get one piece of information from an array in, but I can't get the second. My latest attempt is to create the entry with the first array and then update that entry with the second array but that isn't working either. I'm a noob when it comes to this stuff so if this is explained somewhere please point me in the right direction. I've listed what I have below.

 

The first foreach array works and the values are dumped into the database. I can't get the second foreach array to update the previous filed. I'm sure there is a better way to do this, but I don't have a clue. Any help would be appreciated, and yes, I did search google, but I guess I'm not using the right key words!

 

Thanks,

Gary

 

<SELECT NAME="oasis_id[]">
<OPTION VALUE=""> </OPTION>
<OPTION VALUE=\"".$oasis_id.".Present\"> Present </OPTION>
<OPTION VALUE=\"".$oasis_id.".Absent\"> Absent </OPTION>
</SELECT>
<INPUT TYPE="TEXT" NAME="particpation_points[]" >
<INPUT TYPE="SUBMIT" VALUE="Submit" >

 

$class = $_POST['class'];
$attendance_date = $_POST['attendance_date'];

foreach($_POST['oasis_id'] as $oasis_id) {	
$query = "INSERT INTO $class (attendance_date, attendance) values ('$attendance_date','$oasis_id')";
mysql_query($query) or die('Error, query failed : ' . mysql_error());
}

foreach ($_POST['particpation_points'] as $particpation_points) {
$query2 = "UPDATE $class SET particpation_points='$particpation_points' WHERE oasis_id='$oasis_id' && attendance_date='$attendance_date'";
mysql_query($query2) or die('Error, query failed : ' . mysql_error());
}

 

[DavidAM]

Typically, I do this by "forcing" the array index value. The code you have

<INPUT TYPE="TEXT" NAME="particpation_points[]" >

will create an array: $_POST['particpation_points'][seq#] -- where the number is just an incrementing value from zero. You will have no way to really "know" which array element goes with which "user_id". I would write it something like this:

 

<INPUT TYPE="TEXT" NAME="particpation_points[12]" >

Here, the "12" represents the database ID value of the user's record I will update with the value provided.

 

So the code to generate the form would be something like this:

// Database query to get the students of the class ...
while ($row = mysql_fetch_assoc($resource)) {
print('<SELECT NAME="attendance[' . $row['id'] . ']">
  <OPTION VALUE=""> </OPTION>
  <OPTION VALUE="1"> Present </OPTION>
  <OPTION VALUE="0"> Absent </OPTION>
  </SELECT>
  <INPUT TYPE="TEXT" NAME="particpation_points[' . $row['id'] . ']" >');
}
print('<INPUT TYPE="SUBMIT" VALUE="Submit" >');

 

Then to handle the data after posting:

 

foreach ($_POST['attendance'] as $ID => $value) {
  $sql = "INSERT INTO $class (oasis_id, attendance_date, attendance, particpation_points) 
values ($ID, '$attendance_date','$value', " . $_POST['participation_points'][$ID] . ")";
  // Execute Query
}

 

Or something along those lines. Since we "forced" the array index, we only need one loop.

 

You need to sanitize the inputs before shoving them into the query -- This is just a example of the process flow

 

You can, and probably should, build a single insert statement for all of the data, so you can do a single trip to the database.