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
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.
Re: [Jeffncou] MySQL Console
By Jason - February 28, 2011
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
- 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! :)
interactivetools.com
Re: [Dave] MySQL Console
By JeffC - March 1, 2011
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
Re: [Jeffncou] MySQL Console
By Dave - March 1, 2011
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!
interactivetools.com