[SOLVED] deleting from multiple tables

[rockindano30]

hello all, well i have spent hours and hours and hours on this problem and can seem to figure it out any help?

 

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.

Code:

<?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.

 

and this is the error i get in my 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

 

Thanks in advance.

[rockindano30]

finally figured it out myself!!!!!!!!!!!!!!!!!!!!!!

 

thank you all