mySQL row delete..

[mikey3521]

I have a table with the following values:

 

id    to    from    subject    read

 

with the following info

 

1    2    6          hey!            1

2    2    4        wjat?            1

3    8    5        test!            1

 

the "from" is the users 'id' on a different table, now on running a script, I want it to search this table for any row that has "2" in the from field, so any row with a msg user 2 has sent, and delete it. Heres what I have.

 

("DELETE FROM messages WHERE to=2");

 

It returns no erros, the rest of the script runs, and it dosn't delete the rows??

[GingerRobot]

You say it returns no errors, but do you actually check?

 

You should do:

mysql_query("DELETE FROM messages WHERE to=2") or die(mysql_error());

 

[AndyB]

subject is OK as a field name.  to, from, and read are all reserved words and you should not use them as field names or table name.  The right way to fix the problem is to avoid using reserved words as names.  The sloppy way is to surround each one with `backticks`.

 

Do it right - see http://dev.mysql.com/doc/refman/5.0/en/reserved-words.html

[chocopi]

dont you need to say what you want deleted eg *

so it would be:

 

mysql_query("DELETE * FROM messages WHERE to=2") or die(mysql_error());

[AndyB]

Folks, you can suggest queries until the cows come home but none of them will work until the OP changes the field names so that no reserved words are used.

[GingerRobot]

Andy, just out of interest - why is using backticks a sloppy way? Personally i find queries so much easier to read if all the names of tables/fields etc are enclosed in backticks and all values enclosed in single quotes.

[AndyB]

I call it sloppy because 99/100 times people need it to fix a problem caused by using reserved words as field names so it becomes a work-around to overcome their initial lack of thought, care, or knowledge

[GingerRobot]

I see what you mean, but given the context of the table, aren't to and from the most appropriate names for the fields? It seems a whole stack easier to put backticks around the words than change the to msgto and msgfrom to me. Guess thats just personal opinion though.