SQL Syntax in MYSQL Please help me the condition in DELETE in MYSQL

Databases
1 
if(isset($_POST['delete'])){
	$mysqli->query("DELETE FROM cdata WHERE VALUES
	  ('John', 'London'),
	  ('Monica', 'Manchester'),
		('Tina', 'Delhi'),
		('Mona', 'Boston'),
		('Simpos', 'New York'),
		('Alexandra', 'Las Vegas'),
		('Alexander', 'Berlin'),
		('Winston', 'London'),
		('Franklin', 'Boston'),
		('Abraham', 'Paris'); ") or
	               die($mysqli->error);
  $_SESSION['message']  = "Record has been deleted";
	$_SESSION['msg_type'] = "danger";
	header("location: index.php");
}

Please help me to correct the syntax error:

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '‘John’, ‘London’), (‘Monica’, ‘Manchester’), (‘Tina’, ‘Delhi’), (‘Mona’ at line 2

2

this will work –

DELETE 
  FROM cdata 
 WHERE name = 'John'    AND city = 'London'
    OR name = 'Monica'  AND city = 'Manchester'
    OR name = 'Tina'    AND city = 'Delhi'
    OR ...

this might work (i’m too lazy to check if MySQL supports this) –

DELETE 
  FROM cdata 
 WHERE ( name , city ) IN 
     ( ('John', 'London')
     , ('Monica', 'Manchester')
     , ('Tina', 'Delhi')
     , ...
     )
1 Like
3

Though the back of my head is screaming at Rudy to put parenthesis in those mixed AND/OR clauses… :stuck_out_tongue:

4

nope

5

Yeah, I’m sure it does work, but my brain still wants to wrap it.

1 Like
6

This also generates error:

$mysqli->query("DELETE FROM cdata WHERE ( name , location ) IN 
	  ('John', 'London'),
	  ('Monica', 'Manchester'),
		('Tina', 'Delhi'),
		('Mona', 'Boston'),
		('Simpos', 'New York'),
		('Alexandra', 'Las Vegas'),
		('Alexander', 'Berlin'),
		('Winston', 'London'),
		('Franklin', 'Boston'),
		('Abraham', 'Paris'); ") or
	               die($mysqli->error);

You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near ’ (‘Monica’, ‘Manchester’), (‘Tina’, ‘Delhi’), (‘Mona’, ‘Boston’), (’ at line 2

7

ANDs take precedence over ORs

parentheses would only add noise to the signal

i still love you, though :heart_eyes:

1 Like
8

r937’s post didnt contain a typo, he intentionally wrapped the value pairs in an extra set of parenthesis…

IN 
     ***(*** ('John', 'London')
9

because you did not reproduce it correctly

what about the first version i gave you?

1 Like
10

That is lengthy? No compact format?

11

what happened when you tested it? ™

1 Like
12

He’s given you the most compact format he can give you without knowing every piece of data in the table, or without using multiple queries.

For example; if John is the only person who’s been to london, you can delete his row by deleting WHERE location = “London”

But from the information you have provided (a very specific set of rows based on two column conditions each), that is the most compact delete statement you can make that will delete all of those rows in one query.

13

Live site here. Thanks.
Building simple app to learn CRUD.

14

did. it. work.

you’re welcome

1 Like
15

Scratch reply. Didnt consider the ids keep incrementing every time you add/delete sample data.

16

Huh? Care to explain your reasoning behind that?

Deleting records based on specific criteria is perfectly valid. Or perhaps the combination of name and location IS the unique ID. You don’t HAVE to have a unique identifier identity field to have normalized data.

17

If you look at the site it is obvious each record has a unique id number.

Although, I didnt consider that the id’s keep incrementing every time sample data is added/deleted.

Not disputing that.

18

Still doesn’t mean you HAVE to use the unique id from the table to delete it. Deleting by any criteria is one of the strengths of using a database.

1 Like
19

you don’t have to, no.

But as the OP was looking for the most compact query, a query that DELETE’d WHERE id IN(201,202,…) is considerably more compact that the offering that had to be made based upon a tuple of non-key columns.

1 Like
20

ID’s will keep on changing as the sample data once inserted can be deleted one by one, and next time when sample data will be inserted will have different ID’s.