MySQL/PHP copy table data issue

[nsandberg]

Ok I am new to PHP/MySQL, but not new to programming.  What I am developing is an online application for a school to keep track of missing assignments that students have.  I have a table that lists the students first name, last name, homeroom, and last period teachers.  Each student in the table has an ID.  What I want to do is have teachers click on "Add Assignment" and they would enter the assignment details and the subject and then using radio buttons select the students that are missing such assignment.  Then on "Submit" the student data (first name, last name, homeroom, and last period teachers) would get copied into a new table along with the description of the assignment and the subject each as their own ID.  This is so the teacher only has to enter the description and the subject once, but can assign it to multiple students. 

 

I have spent the last couple of weeks looking for examples that can help go in the right direction, but have had no luck on getting the data copied to the new table.  Any help would be great!  Thanks in advance.

[mo]

So, you basically want to have the selected students data inserted into a table along with the assignment details that the teacher entered?

 

If so, just loop through the selected radio buttons, use the user_ids that are assigned to the individual radio buttons to query the user table and insert this data and the data from the screen that the teacher entered into your target table.

[nsandberg]

That is correct, but I am having major issues.  I got it to work somewhat as it only enters the assignment details into the new table, but it does not copy the students data that were selected.

 

Here is my code, I know that I am missing something, but I am so new to PHP/MySQL that I cannot see it.  If you can make sense of this great, if not I am sorry.  I usually don't code like everyone is watching and I know that I should.  If you can help please do.

 

SQL Query to get students data:

$student_data = mysql_query("SELECT * FROM student ORDER BY lastName") or die(mysql_error());

while($students = mysql_fetch_array($student_data))
{
?>
<div class="student_checkbox"><input class="checkbox" name="checkbox[]" type="checkbox" id="checkbox[]" value="<? echo $students['id'] . $students['lastName'] . $students['firstName'] . $students['AP'] . $students['8th']; ?>"></div>

<?
print("<div class=\"student_lastName\">" . $students['lastName'] . "</div>");
print("<div class=\"student_firstName\">" . $students['firstName'] . "</div>");
print("<div class=\"student_AP\">" . $students['AP'] . "</div>");
print("<div class=\"student_8th\">" . $students['8th'] . "</div>");
print("<br>");

}
print("</div>"); //End of <DIV class="student_wrapper">

 

Loop to submit the data based on the checkboxes and INSERT data into new table

if (isset($_POST['submit'])){
$checkbox=$_POST['checkbox'];

for($i=0;$i<$count;$i++){
$ID = $checkbox[$i];

$first = $students['firstName'];
$last = $students['lastName'];
$AP_teacher = $students['AP'];
$last_teacher = $students['8th'];
$description = $_POST['description'];
$subject = $_POST['subject'];


$sql = ("INSERT INTO assignments SET id = '$ID', firstName = '$first', lastName = '$last', AP = '$AP_teacher', 8th = '$last_teacher', description = '$description', subject = '$subject'");

$result = mysql_query($sql);

}

  if ($result) {
    echo('<p>New assignments added</p>');
echo '<META HTTP-EQUIV="Refresh" Content="5; URL=../index.php">';    
exit;
	    
  } else {
    echo('<p>Error adding new assignment: ' . mysql_error() . '</p>');
  }
  

}

[mo]

Try something like this:

 

<?php
$student_data = $students['id']."~".$students['lastName']."~".$students['firstName']."~".$students['AP'];
?>

<div class="student_checkbox">
<input class="checkbox" type="checkbox" name="student[]" id="student[]" value="<? echo $student_data; ?>"></div>

//The submit logic:--------------------------------------
<?php
if(is_array($_POST['student']))
{
foreach($_POST['student'] as $val => $value) {
	list($id,$last_name,$first_name,$student_ap) = split('~',$value);

	$sql = ("INSERT INTO `assignments` (`id`,`firstName`,`lastName`,`AP`,`subject`)
	                VALUES ($id,'$first_name','$last_name','$student_ap','".$_POST['subject']."'");

	$result = mysql_query($sql);
}
}
?>

[radar]

Really what you should do is have a seperate table only for the missing assignments with the information about that assignment and having a s_id in that table which would contain the $id of each student... -- then when you're ready to display it, you use a join and you have id from the students table, and s_id from the assignments table, and if you really wanted a c_id in the assignments table which would include the class involved.