[SOLVED] Help needed creating a mysql query

[acctman]

can someone assist me with creating a php/mysql or sql query i can put in phpmyadmin... that will delete picture comments from users that no longer exist on my site.

 

table: rate_members

field: m_id

 

table: rate_picture_comments = rpc

field: com_user

 

Loop through the entire rpc Select 'com_user' (userid) from rate_picture_comments and then go into the rate_members table and see if 'com_user' exist by checking the 'm_id' field. If no match for com_user to m_id is found then process a sql delete row for com_user mysql_query("DELETE FROM rate_pictures_comments WHERE com_user=$id") something like that.

[rhodesa]

** Always Backup your Database First **

 

-Start with a SELECT

SELECT FROM rate_picture_comments WHERE com_user NOT IN ( SELECT m_id FROM rate_members )

 

if those are in deed the records you want to delete, then:

DELETE FROM rate_picture_comments WHERE com_user NOT IN ( SELECT m_id FROM rate_members )

[acctman]

** Always Backup your Database First **

 

-Start with a SELECT

SELECT FROM rate_picture_comments WHERE com_user NOT IN ( SELECT m_id FROM rate_members )

 

if those are in deed the records you want to delete, then:

DELETE FROM rate_picture_comments WHERE com_user NOT IN ( SELECT m_id FROM rate_members )

 

thanks. when i ran the first query in phpmyadmin, i received this error.

#1064 - 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 'FROM rate_picture_comments WHERE com_user NOT IN ( SELECT m_id FROM rate_members' at line 1

 

[rhodesa]

What are the tablenames and fields again? And what two fields link the tables?

 

 

[kickstart]

Hi

 

It is just a missing * after the select.

 

All the best

 

Keith

[acctman]

thanks. everything worked.