Gossamer Forum
Home : Products : Gossamer Links : Development, Plugins and Globals :

What is the MySQL Command?

Quote Reply
What is the MySQL Command?
What is the MySQL command to delete all listings in a field that contain a word?

It should look something like the following?:

DELETE FROM links_Links WHERE (Title = "bad word")

Thanks for your time!
Daniel
-http://www.csnhome.com
Quote Reply
Re: [DanDaBum] What is the MySQL Command? In reply to
Well...



DELETE FROM table_name WHERE field_name LIKE '%bad_word%'



TEST it using "Select *" instead of "delete" to make sure it does what you want.


PUGDOG´┐Ż Enterprises, Inc.

The best way to contact me is to NOT use Email.
Please leave a PM here.
Quote Reply
Re: [pugdog] What is the MySQL Command? In reply to
Thanks! The command works like a charm!

Another question, would the command look like the following if I only wanted to delete from links that haven't been validated yet?:

DELETE FROM Table WHERE Field LIKE '%bad_word%' AND (isValidated = 'No')

Thanks for your time!
Daniel
-http://www.csnhome.com
Quote Reply
Re: [DanDaBum] What is the MySQL Command? In reply to
Be careful about doing this! You don't properly clean up the tables if you delete using raw sql queries. For instance, there are probably entries in the CatLinks table that don't match up to a valid link. This will mess up your link count.

I think a repair table will catch this.

Ideally, use a global, or a perl script to do the delete (a little trickier, I know).

Cheers,

Alex
--
Gossamer Threads Inc.
Quote Reply
Re: [Alex] What is the MySQL Command? In reply to
What is the CatLinks table used for? Could I just delete from both tables at the same time using this command?:

DELETE FROM links_Links AND links_CatLinks WHERE Table Like '%bad_word%' AND (isValidated = 'No')
Daniel
-http://www.csnhome.com
Quote Reply
Re: [DanDaBum] What is the MySQL Command? In reply to
Hi,

No, that wouldn't work. The CatLinks table has two columns, LinkID, and CategoryID. It maps which links are in which categories. If you delete Links, but don't delete the entries out of the CatLinks table, you end up with categories thinking they have too many links in them.

The following query would give you a list of "orphaned" id's. ID's in the category table, that don't have an entry in the links table:

SELECT LinkID
FROM lsql_CatLinks LEFT OUTER JOIN lsql_Links ON (LinkID = ID)
WHERE ID IS NULL

You then need to get all those ID's that are returned and do:

DELETE FROM lsql_CatLinks WHERE LinkID IN (1,2,3,4,5)

where 1,2,3,4,5 is a comma separated list of links. =)

You can also use the admin panel to do these sort of deletions, and it will clean up after itself. Enter in the word 'bad_word' in the title field, and make sure the operator is set to Like. Then set Max hits really high (say 500) and do a search. At the bottom is a check all box which you can check all the links, and then hit delete.

Cheers,

Alex
--
Gossamer Threads Inc.
Quote Reply
Re: [Alex] What is the MySQL Command? In reply to
Well that really stinks.

I've gone back and re-imported my old Links SQL 1 tables and started over this time by using the admin panel to delete.

If anyone has any other way to clean the links_CatLinks table, please tell me because it's full of 14000+ bad entries and I'm not about to put a common between each one of them.
Daniel
-http://www.csnhome.com
Quote Reply
Re: [DanDaBum] What is the MySQL Command? In reply to
If you are comfortable with perl, you can write a little script:

use lib '/path/to/links/admin';
use Links qw/$DB/;

Links::init('/path/to/links/admin');

my $link_table = $DB->table('Links');
my $cond = GT::SQL::Condition->new(
'Title', 'Like', '%bad_word%'
);
$link_table->delete($cond);

and that will remove all entries where Title is like %bad_word%, and clean up all the other tables as well.

Cheers,

Alex
--
Gossamer Threads Inc.
Quote Reply
Re: [Alex] What is the MySQL Command? In reply to
Ok, I've found a new problem.

I've gone through and deleted most of the bad links successfully, but that there are now a bunch of dead Users. The database was imported directly from Links SQL 1 so the Usernames are just the e-mail addresses of the bad links. But how do I delete all these bad Users? I need some command that will delete all Users without links tied to them.
Daniel
-http://www.csnhome.com