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.

 

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.

 

[thomashw]

Why not just use $emp_id instead of $emp_id['emp_id']? Is $emp_id['emp_id'] even set?

[rockindano30]

no thats one of the problems, it does not set.

[thomashw]

I don't see where you're trying to set it anywhere...

 

You have it set as $emp_id but not $emp_id['emp_id']. Try changing $emp_id['emp_id'] to $emp_id.

 

 

[rockindano30]

i have tried $emp_id by itself before and can't get it.

 

this is where i'm trying to set it or get it

and at the start of the code i also get it using this

$emp_id=$_GET["emp_id"];

 

	   //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"];   //////getting this emp_id from the query
		?>
            <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>

[Grodo]

Your not escaping your quotes and not passing a value this is my guess

 

<?php print"<a href='delete.php?emp_id={$emp_id['emp_id']}'>Remove</a><br />";?></td>

 

try this

 

<?php print"<a href='delete.php?emp_id={$emp_id['.$emp_id.']}'>Remove</a><br />";?></td>

 

I am still learning php but that what i assume is going on

[thomashw]

Yes, but it's not even being passed in the URL because $emp_id['emp_id'] isn't a variable. You're setting $emp_id to equal the value, not $emp_id['emp_id'].

 

Is there a column in your table called emp_id? If so, try changing

 

<?php
print"<a href='delete.php?emp_id={$emp_id['emp_id']}'>Remove</a><br />";
?>

 

to

 

<?php
echo '<a href="delete.php?emp_id=' . $emp_id . '">Remove</a><br />';
?>

[rockindano30]

no still does not pass it. i even try printing out the $emp_id to see if it gets it from table and it does not display it

[rockindano30]

k there is a colum in both table employees has emp_id as pk and emp_keys has is as fk.

 

and still nothing passing through

[rockindano30]

ah.... this is getting me can't see the error....... ahhhhhhhhh

[rockindano30]

could it be cause i am not selecting it into the query?

[rockindano30]

hello where did everybody go????