Deleting multiple rows from MYSQL table based on column data in PHP

Question

I have a table called user_favs like this :

 id | user_id | post_id
 ----------------------
 50 | 291     | 1027
 51 | 10      | 180
 52 | 771     | 1027
 53 | 92      | 133
 54 | 523     | 1027

How would I set up a database query in php to delete all rows that contain post_id 1027?

I know how to do it for single entries :

DELETE FROM user_favs WHERE post_id = 1027

But I don't know how to delete all rows that contain post_id 1027.

Is it something like :

DELETE * FROM user_favs WHERE post_id = 1027

I don't want to try it without knowing in case I mash the whole thing up ;)

If you're really unsure of what a statement or series of statements do, make a backup of your database with mysqldump, and try it out on the copy. — Ghedipunk
– Ghedipunk, Commented Oct 9, 2015 at 22:22

Nir Levy · Accepted Answer · 2015-10-09 22:17:36Z

3

this:

DELETE FROM user_favs WHERE post_id = 1027

will actually not delete only one row. it will delete all rows that matches the criteria post_id=1027

answered Oct 9, 2015 at 22:17

Nir Levy

13k3 gold badges23 silver badges41 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

Grant Over a year ago

Ahh so that's exactly what I want! And now I read it back I feel very stupid as I can now see exactly why it would do that. I'll grab the dunce hat...

Nir Levy Over a year ago

yeap. there's no 'delete one row' in sql, it works with criteria - everything that matches the criteria will be deleted, whether it's one row, zero or a million.

Nelson · Accepted Answer · 2015-10-09 22:20:48Z

1

I am pretty sure that the first statement will work. Either way, I would recommend you to do a backup of your db before doing any change, even more, I would recommend you to do this type of trials in a vagrant box or in a database you dont care as the one you could create in your localhost. instead of deleting, to check your code you could do: SELECT * FROM user_favs WHERE post_id = 1027 and see if it worked, if the results are the tuples you want to delete, whoala!

answered Oct 9, 2015 at 22:20

Nelson

745 bronze badges

Collectives™ on Stack Overflow

Deleting multiple rows from MYSQL table based on column data in PHP

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related