How to delete multiple rows from MYSQL table using PHP

Question

I'm trying to learn php and ran into a problem. I'd appreciate any help.

I have a database that looks like:

ID USER  AGE
1  name1 18
2  name2 19
3  name3 20
etc etc

I want to delete multiple records. This is the code I am using for that:

<?php
$username = "root";
$password = "";
$hostname = "localhost"; 

//connection to the database
$dbhandle = mysql_connect($hostname, $username, $password) 
 or die("Unable to connect to MySQL");
echo "Connected to MySQL<br>";

//select a database to work with
$selected = mysql_select_db("photo",$dbhandle) 
  or die("Could not select examples");

//execute 
echo "CONNECTED";
echo "</br>";

//Action
$delete_query = ("DELETE FROM _users WHERE age in(17,18,19)");

$result = mysql_query($delete_query);

echo 'Deleted';

mysql_close($dbhandle);
?>

When I am deleting by querying the age column, it deletes multiple rows okay.

However, when I do this:

$delete_query = ("DELETE FROM _users WHERE USER in(name1,name2,name3)");

Nothing happens. Is it because those are strings? How can I fix it?

Thanks!

Marc B · Accepted Answer · 2011-08-12 16:19:40Z

2

The query is incorrect:

DELETE FROM _users WHERE USER in ('name1','name2','name3');

strings must be quoted, otherwise MySQL will see them as field names (or keywords). You have no error handling in your script, or you'd have seen the error messages. Always write a query as follows:

$result = mysql_query(...) or die(mysql_error());

this'll show you exactly WHY the query failed (or just continue on if there's nothing wrong).

answered Aug 12, 2011 at 16:19

Marc B

362k44 gold badges433 silver badges508 bronze badges

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

2 Comments

Kelvin Jayanoris Over a year ago

Just because I am curious, if the strings list was large (say 50 or so items), would you still recommend doing it this way? Or is there a better way to handle bulk items.

Marc B Over a year ago

50 isn't all that 'big', unless they're long strings. there is a length limit to queries based on the max_allowed_packet setting. it's more a question of efficiency. query compile time v.s. transport overhead. at some point it might be cheaper to split things into 2+ queries instead of a single huge one. but only benchmarking would tell you what that point would be.

Cfreak · Accepted Answer · 2011-08-12 16:18:45Z

1

DELETE FROM _users WHERE USER in(name1,name2,name3)

MySQL thinks that name1, name2, and name3 are columns. Quote them.

answered Aug 12, 2011 at 16:18

Cfreak

19.4k6 gold badges52 silver badges64 bronze badges

Comments

genesis · Accepted Answer · 2011-08-12 16:19:21Z

1

use single quote '

 $delete_query = ("DELETE FROM _users WHERE `USER` in('name1','name2','name3')");

answered Aug 12, 2011 at 16:19

genesis

51.1k20 gold badges99 silver badges127 bronze badges

2 Comments

genesis Over a year ago

@RikudoSennin: Why it wouldn't ?

Madara's Ghost Over a year ago

Now it would (didn't have quotes on the strings).

Robert · Accepted Answer · 2011-08-12 16:19:06Z

0

You need to encase the strings in quotes: WHERE USER in('name1', 'name2', 'name3')

answered Aug 12, 2011 at 16:19

Robert

8,7672 gold badges29 silver badges34 bronze badges

Comments

Alex Howansky · Accepted Answer · 2011-08-12 16:19:09Z

0

Users is a string, you need to quote the values:

$delete_query = ("DELETE FROM _users WHERE USER in('name1','name2','name3')");

answered Aug 12, 2011 at 16:19

Alex Howansky

54k8 gold badges85 silver badges102 bronze badges

Comments

Madara's Ghost · Accepted Answer · 2011-08-12 16:19:33Z

0

Yes, it's because they are strings. Strings need to be quoted properly, otherwise MySQL won't see them as strings. Use

$delete_query = ("DELETE FROM _users WHERE USER in('name1','name2','name3')");

answered Aug 12, 2011 at 16:19

Madara's Ghost

176k52 gold badges274 silver badges314 bronze badges

Collectives™ on Stack Overflow

How to delete multiple rows from MYSQL table using PHP

6 Answers 6

2 Comments

Comments

2 Comments

Comments

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

6 Answers 6

2 Comments

Comments

2 Comments

Comments

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related