deleting from multiple tables

[rockindano30]

Hello all,

 

Well I'm trying to get my script to work and I'm stuck and can't get around it or figure it out. I have three tables.

 

employees(emp_id, Fname, Lname, email, dept_id) //emp_id is pk and dept_id is fk

departments(dept_id,deptname,phone) // dept_id pk

emp_keys (kid,emp_id, access, key_num) //kid is pk emp_id is fk

 

now i want to delete an employee from employees and also delete their key(s) from key table.

 

this is my code in my search page.

 

<?php 
			$search=addslashes($search);
			$search2=addslashes($search2);


			//get the mysql and store them in $result
			$query = "select Fname,Lname, email, dept, group_concat(key_num separator ' - ') as key_num, group_concat(accss separator ' - ') as accss FROM departments, employees, emp_keys where Fname like '%$search%' and Lname like '$search2' and departments.dept_id= employees.dept_id and emp_keys.emp_id=employees.emp_id group by employees.emp_id";				
			$result = mysql_query($query); 
			$num_result = mysql_num_rows($result);


			if ($num_result < 1)
			 print "<p>No employee found by that name.<br />Please make sure that you typed in the employee's name correctly.<br />Or there is no keys asigned to that employee.</p>";
			else {
		//grab all the content
		while($r=mysql_fetch_array($result))
		{	
		   //the format is $variable = $r["nameofmysqlcolumn"];
		   $dept=$r["dept"];			   
		   $Fname=$r["Fname"];
		   $Lname=$r["Lname"];
		   $email=$r["email"];
		   $key_num=$r["key_num"];
		   $accss=$r["accss"];
		   $emp_id=$r["emp_id"];
		?>
            <p style="padding:0px; text-decoration:underline">Your Search Results are below:</p><br  />
              <table width="100%" border="0" cellpadding="1" cellspacing="0" class="table1">
                <tr><td width="20%"><strong>Department:</strong></td>
                  <td width="80%"><font face="Arial, Helvetica, sans-serif"><?php print $dept; ?></font></td>
                </tr>
                <tr><td width="20%"><strong>Name:</strong></td>
                  <td width="80%"><font face="Arial, Helvetica, sans-serif"  style="color:#CC6600"><?php print $Fname." ".$Lname; ?></font></td>
                </tr>
                <tr><td width="20%"><strong>Email:</strong></td>
                  <td width="80%"><font face="Arial, Helvetica, sans-serif"><?php print $email; ?></font></td>
                </tr>
                <tr><td width="20%"><strong>Key #:</strong></td>
                  <td width="80%"><font face="Arial, Helvetica, sans-serif"><?php print $key_num; ?></font></td>
                </tr>
                <tr><td width="20%"><strong>Key Access:</strong></td>
                  <td width="80%"><font face="Arial, Helvetica, sans-serif"><?php print $accss; ?></font></td>
                </tr>
                <tr><td width="20%"> </td>
                  <td width="80%">
                  <?php print"<a href='delete.php?emp_id={$emp_id['emp_id']}'>Remove</a><br />";?></td>
                </tr>
              </table>
          <?php
		  }//end while loop
		}
	}
		?>

 

this is my code in my delete.php page

            <?php
///bla bla bla connect to bd.....
					 //select which database you want to edit
				mysql_select_db("bldg_keys"); 

				$emp_id = $_GET[emp_id];
				$dept_id=$_GET[dept_id];

				$query = "DELETE Fname,Lname,email,dept,key_num,accss FROM emp_keys, departments, employees WHERE departments.dept_id=employees.dept_id and emp_keys.emp_id=employees.emp_id";


//					$query = "DELETE FROM employees WHERE emp_id=$empid";
				$result = mysql_query($query) or die ("Error in query: $query.".mysql_error());				
				print "<p>Employee has been removed permanently from our database.<br />";
				print "Click <a href=\"index.php\">HERE</a> to go to home page.</p>";
?>

 

im trying to pass the emp_id from search.php page to delete. but i'm stuck cause it is not getting the emp_id. can anybody see whats wrong here? i can't.

 

 

Thanks in advance.

 

 

[rockindano30]

one more thing i get this error at delete.php page:

 

Error in query: DELETE Fname,Lname,email,dept,key_num,accss FROM emp_keys, departments, employees WHERE departments.dept_id=employees.dept_id and emp_keys.emp_id=employees.emp_id.Unknown table 'fname' in MULTI DELETE