[RESOLVED] Delete data from table where var NOT LIKE

dcjones

Hi All,

I have a table where I need to delete all the records that do not meet a criteria.

The table has a field named "Flight" and I want to delete all the records that do not have "FR" in the content of the field "Flight"

I am using:


$sql = mysql_query("DELETE* FROM RemoveAllButFR WHERE Flight NOT LIKE 'FR%'");

This do not work but from all that I have read it should. Can anyone see where I am going wrong.

bradgrafelman

For one, you should always be checking to see if [man]mysql_query/man returns boolean FALSE, indicating an error has occurred and, if so, outputting and/or logging the MySQL error message (see [man]mysql_error/man) and perhaps the query itself (to aid in debugging) as it was sent to the MySQL server.

As for your problem, there is no "DELETE*" command in MySQL or standard SQL; perhaps you meant DELETE instead?

dcjones

Hi Brad,

Many thanks for your reply.

The "DELETE*" was a typo, the complete statement is below. It dos return "Query was empty" but the table does contain records where the flight field has "FR" in the content.

Example:

EZY8660
HV6144
FR8297
JK336
LS880
FR5578

$sql = mysql_query("DELETE * FROM RemoveAllButFR WHERE Flight NOT LIKE 'FR%'"); 

mysql_select_db($database_flightq, $flightq);
$Result1 = mysql_query($sql, $flightq) or die(mysql_error());

bradgrafelman

You're now attempting to execute the query twice, with the second call being incorrect ([man]mysql_query/man expects a string as its first paremeter, and you are not passing it a string).

dcjones

Hi Brad,

It was my stupid mistake, I had a "*" in the query. Now looks like

$sql = mysql_query("DELETE FROM RemoveAllButFR WHERE Flight NOT LIKE 'FR%'");

This has produced the result I was looking for.

Again, many thanks for your input.