MySQL Console

5 posts by 3 authors in: Forums > CMS Builder: Plugins & Add-ons
Last Post: March 1, 2011   (RSS)

By JeffC - February 28, 2011

I am looking for a quick solution to deleting the contents of a field in a database. For example:

Lets say I have a contacts database with 10,000 entries. Each entry has a field called telephone. I would like to delete all phone numbers that start with the area code 01803. I do not want to delete the entire entry, ie I want to keep the name, address and telephone fields, just delete the contents of the telephone field.

Would this be possible with MySQL console. If so is is straight forward.
Jeff

Re: [Jeffncou] MySQL Console

By Jason - February 28, 2011

Hi,

Yes, you could accomplish this with the MySQL console. All the plugin does is give you a way to execture SQL commands directly on your database.

You should only attempt this if you are comfortable with MySQL. It is also VERY important that you always back up your database before attempting to run queries directly on your data.

Hope this helps.
---------------------------------------------------
Jason Sauchuk - Project Manager
interactivetools.com

Hire me! Save time by getting our experts to help with your project.
http://www.interactivetools.com/consulting/

Re: [Jeffncou] MySQL Console

By Dave - February 28, 2011

Some good advice for modifying records is to follow these steps:

- Backup your database under: Admin > General
- Do a SELECT query to see what you are matching
- Then do a DELETE or UPDATE query

Your SQL would probably be something like this:

SELECT * FROM cms_yourTable WHERE phone LIKE "01803%"

UPDATE cms_yourTable SET phone = "" WHERE phone LIKE "01803%"

Hope that helps! And be careful! :)
Dave Edis - Senior Developer
interactivetools.com

Re: [Jeffncou] MySQL Console

By Dave - March 1, 2011

Hi Jeff,

Usually it executes in a fraction of a second.

The first select query will show you what is matched, and you can also limit the columns returned to make it easier to see just answer:

SELECT answer FROM cms_contacts WHERE answer LIKE "Tomato%"

Then the update won't return anything (it might show records updated at the bottom).

Then run select again and it shouldn't return any results since you've removed "Tomato" from all those records.

Note that in LIKE queries % means "anything". So "Tomato%" means "Starts with Tomato and followed by anything else". If you wanted to match records with tomato anywhere in the string you'd use "%Tomato%".

Hope that helps!
Dave Edis - Senior Developer
interactivetools.com