CODEWITHSUNDEEP
mysql
nikoh
nikoh

Reputation: 101

Delete from two tables (look if data exists)

I'm having trouble to delete data from my two tables in one query. I've found several similar threads but none that has the same core problem as me.

So, why is this different?

The target: I need help on how to delete all the project data from my project database, and also all the hours that is eventually attached to it in the hours database.

With my current query it only deletes if it finds a match in both the project and the hours databases. So how could I achieve it to delete all the data as requested above?

Right now my query looks like this:

DELETE hours, projects
FROM hours 
INNER JOIN projects  
WHERE hours.projecthours_id= projects.project_id and hours.projecthours_id = ".$mysqli->real_escape_string($_GET['id']).";

This is my two tables (example data and tables):

#hours
---------------------------------------
hours_id  projecthours_id  amount_hours 
1         12               15
2         8                4
3         9                2
4         15               11

#projects
---------------------------------------
project_id  project_name  
1           project 1
2           project 2
3           project 3
4           project 4

Upvotes: 0

Views: 39

Answers (3)

nikoh
nikoh

Reputation: 101

Didn't get it to work thorugh one query. So made them as two separate ones instead.

$query = "DELETE FROM hours WHERE hours.projecthours_id = ".$mysqli->real_escape_string($_GET['id'])."";

$mysqli->query($query);
$query = "DELETE FROM projects WHERE projects.project_id = ".$mysqli->real_escape_string($_GET['id'])."";

Upvotes: 0

Madhur Bhaiya
Madhur Bhaiya

Reputation: 28834

  • Use Left Join instead of Inner Join.
  • Also, your where condition should be on projects.project_id instead of hours.projecthours_id, as you are trying to delete all from projects table.

Try:

DELETE hours, projects
FROM hours 
LEFT JOIN projects ON projects.project_id = hours.projecthours_id
WHERE projects.project_id = ".$mysqli->real_escape_string($_GET['id']).";

Upvotes: 1

Akshay
Akshay

Reputation: 3866

Try left outer join instead of inner join and where goes to on.

Try this

DELETE hours, projects
FROM hours 
LEFT OUTER JOIN projects on hours.projecthours_id= projects.project_id
WHERE hours.projecthours_id = ".$mysqli->real_escape_string($_GET['id']).";

Upvotes: 1

Related Questions