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!

Answer 1

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).

Answer 2

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

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