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: [Dave] MySQL Console

By JeffC - March 1, 2011

Thanks Guys

Just one more question. How long would the query take to execute? I have used the code as below - with updates to the relevant fields and nothing seems to happen.

This is the query I am using. (Note: the phone number example I gave earlier was just a sample - what I actually am trying to do is remove answers to a competition. The table is cms_contacts. The answer I need to remove is "Tomato" and it is in a field called "answer"

Here is my query:

SELECT * FROM cms_contacts WHERE answer LIKE "Tomato%"
UPDATE cms_contacts SET answer = "" WHERE answer LIKE "Tomato%"

Thanks
Jeff

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