How to delete related table records?

[ElegantElephant]

I don't know if it's obvious but I'm not entirely sure how to delete joined table records? For example. I have a project which also has associated contracts, files etc in other tables. They are all linked using the project foreign key.

How do I construct a query to that I delete the actual project record in the projects table AS WELL AS the other entries in the other tables?

[qazplm114477]

Deleting from multiple tables have constraints. you can do something like this.

Here`s a code that I`ve used

function delete_employee(){
        //connect to use database
	$dbc=connect();
	
       //enable transactions
 	mysqli_autocommit($dbc, FALSE);
	
   
	$emp_id = $_POST['emp_id'];
		
       //default success = true
	$success = true;
	
        //delete from the first table 
	$query = "delete from employee
				where id = '".$emp_id."'";
	
		if($result = mysqli_query($dbc, $query) or die(mysqil_error()))
		{
			if(mysqli_affected_rows()>0)
			{
				//if everything went through delete from second table
				$query = "delete from training_date
							where employee_id = '".$emp_id."'";
				
				$result = mysqli_query($dbc, $query) or die(mysqli_error());
				    
					if (($result == false) && (mysqli_affected_rows() == 0))
					{
						$success = false;
						
					}
					else {$success = true;}
					
			}#end if(mysql_affected_rows()>0) 1
			
			if (($result == false) && (mysqli_affected_rows() == 0))
			{
				
				$success = false;	
				
			}#end if (($result == false) && (mysql_affected_rows() == 0)) 2
		
		}#end 1st if
		
		else
		{
		$success = false;	
		}
		
		if($success==true){
			//if everything went fine. Confirm the delete
			mysqli_commit($dbc);

			mysqli_close($dbc); 
			
		}
	
		else{
                        //If something went wrong, cancel everything
			mysqli_rollback($dbc);
			
			mysqli_close($dbc);
			echo `failed to delete`;
		}
}

I hope this helps.

Note: if you are not using mysqli, then to begin transactions you have to use this

$query  = "SET AUTOCOMMIT=0";
    $result = mysql_query($query) or die(mysql_error());

    $query  = "BEGIN";
    $result = mysql_query($query) or die(mysql_error());

//Instead of this

mysqli_autocommit($dbc, FALSE);

[sandimcp]

Thank you for this code. It's very clear and helpful.