[RESOLVED] assignment problem!

[msimonds]

One of the scripts that I am writing will delete a teacher if he/she quits.

The problem that I am having is that I am not updating the students that are affected by this and assigning them a new teacher_id of '0' when this happens and I cannot figure out what I need to do. I can picture it in my head but don't know what to code in order to do it.

the delete_teacher.php:

<?php

/******************************************
 * This script removes teachers from the  *
 * little saints and scholars database    *
 *       !USE WITH CARE                   *
 ******************************************
*/



// Address error handing.
ini_set ('display_errors', 1);
error_reporting (E_ALL & ~E_NOTICE);


//Setup the datbase connection
include("./include/config.php");
include("./include/opendb.php");

//setup vairables coming from $_POST
$teacher_id 	    =	trim($_POST['id']);
$teacher_name 	    = 	trim($_POST['teacher_name']);
$teacher_contact	=	trim($_POST['teacher_contact']);
$teacher_email      =   trim($_POST['teacher_email']);


if (isset ($_POST['submit']))  // Handle the form.
{
	// Define the query.
	$query = "DELETE FROM teachers WHERE teacher_id=$teacher_id LIMIT 1";
	$r = mysql_query ($query); // Execute the query.

// Report on the result.
if (mysql_affected_rows() == 1)
{
	echo '<p>The teacher has been Fired or Quit and was deleted.</p>';
    echo '<a href="view_teacher.php">View Teacher List</a>';
} else {
	echo "<p>Could delete the entry because: <b>" . mysql_error() . "</b>. The query was $query.</p>";
}

}
else
{ // Display the entry in a form.

// Check for a valid entry ID in the URL.
if (is_numeric ($_GET['id']) )
{

	// Define the query.
	$query = "SELECT * FROM teachers WHERE teacher_id={$_GET['id']}";
	if ($r = mysql_query ($query))
    { // Run the query.

		$row = mysql_fetch_array ($r); // Retrieve the information.

		// Make the form.
        echo "<form action=\"delete_teacher.php\" method=\"post\">
                <p>Are you sure you want to delete this entry?</p>
                <p><h3>{$row['teacher_name']}</h3>
                <input type=\"hidden\" name=\"id\" value=\"{$row['teacher_id']}\" />
                <input type=\"submit\" name=\"submit\" value=\"Delete this Entry!\" /></p>
            </form>";
	}
    else
    { // Couldn't get the information.
		echo "<p>Could retrieve the entry because: <b>" . mysql_error() . "</b>. The query was $query.</p>";
	}

} else
{ // No ID set.
	echo '<p><b>You must have made a mistake in using this page.</b></p>';
}

} // End of main IF.


?>

The two tables are tied together by the "teacher_id".

can someone give me some ideas what to add to the script and where!

TIA

Mike

[msimonds]

here is the student table:

TABLE `students` (
  `student_id` int(10) unsigned NOT NULL auto_increment,
  `student_fname` varchar(100) NOT NULL default '',
  `student_lname` varchar(100) NOT NULL default '',
  `student_bdate` varchar(10) NOT NULL default '',
  `student_age` int(3) NOT NULL default '0',
  `student_address1` varchar(100) NOT NULL default '',
  `student_address2` varchar(100) default NULL,
  `student_city` varchar(100) NOT NULL default '',
  `student_state` varchar(100) NOT NULL default '',
  `student_zip` int(5) NOT NULL default '0',
  `student_father` varchar(100) default NULL,
  `student_mother` varchar(100) default NULL,
  `student_contact` varchar(12) NOT NULL default '',
  `teacher_id` int(2) NOT NULL default '0',
  `student_email` varchar(100) default NULL,
  `student_allergies` varchar(100) default NULL,
  PRIMARY KEY  (`student_id`)
) TYPE=MyISAM AUTO_INCREMENT=37 ;

here is the teacher table:

TABLE `teachers` (
  `teacher_id` int(10) unsigned NOT NULL auto_increment,
  `teacher_name` varchar(255) NOT NULL default '',
  `teacher_contact` varchar(12) NOT NULL default '',
  `teacher_email` varchar(100) NOT NULL default '',
  PRIMARY KEY  (`teacher_id`)
) TYPE=MyISAM AUTO_INCREMENT=11 ;

[Piranha]

The easy way to solve this problem would be to use two queries, one to assign the students to another teacher:

UPDATE students SET teacher_id = 0 WHERE teacher_id = $teacher_id;

Then delete the teacher the way you do it:

DELETE FROM teachers WHERE teacher_id=$teacher_id LIMIT 1;

Of course there are more "correct" ways to do it, then you do it in your database. And I recommend you to add that security as well. But to get it working the above should work without any problems.

[msimonds]

so like this?

if (isset ($_POST['submit']))  // Handle the form.
{
	// Define the query.
	$query = "DELETE FROM teachers WHERE teacher_id=$teacher_id LIMIT 1";
	$r = mysql_query ($query); // Execute the query.

$query2 = "UPDATE students SET teacher_id = 0 WHERE teacher_id = {$_GET['id']}";
$r2 =    mysql_query ($query2); // Execute the query.

// Report on the result.
if (mysql_affected_rows() == 1)
{
	echo '<p>The teacher has been Fired or Quit and was deleted.</p>';
    echo '<a href="view_teacher.php">View Teacher List</a>';
} else {
	echo "<p>Could delete the entry because: <b>" . mysql_error() . "</b>. The query was $query.</p>";
}

}

[msimonds]

Yeah I tested it and it works perfectly!!!!

thanks Piranha, I appreciate it a lot!!!