Page 1 of 1
How do you update any field in the database?
Posted: Tue Sep 15, 2009 12:00 pm
by simonmlewis
I have a database where I have imported text from a user, and there are multiple fields where the spelling errors are the same.
I would normally do this:
Code: Select all
UPDATE products SET title = 'not available at this time' WHERE title = 'not averlbe at this time'
But I can't because there are more than 5 fields, could be more, with the same spelling error. So I want to do this query, but on ANY field where the text states *.
Anyone know how to do this?
Re: How do you update any field in the database?
Posted: Tue Sep 15, 2009 12:40 pm
by d.unicorn
update <table_name> set <table_field1> = <table_value1>, <table_field2> = <table_value2>, ..... where <primary_key_field> = primary_key_value
Re: How do you update any field in the database?
Posted: Tue Sep 15, 2009 1:08 pm
by simonmlewis
What if I have 500 rows, and don't know which field has got the spelling error?
I need to do a global check for a particular phrase, and change it to the correct phrase.
Your method appears to be telling me to manually tell each row..... what if I have 50 rows??
Re: How do you update any field in the database?
Posted: Tue Sep 15, 2009 1:51 pm
by gigabit
Code: Select all
UPDATE products SET table_field = replace(table_field,'replace_that','with_this');
Re: How do you update any field in the database?
Posted: Tue Sep 15, 2009 2:20 pm
by simonmlewis
What is table_field ?
Re: How do you update any field in the database?
Posted: Tue Sep 15, 2009 4:46 pm
by gigabit
The field in the table youre updating.
Re: How do you update any field in the database?
Posted: Wed Sep 16, 2009 12:59 am
by simonmlewis
Sorry, but I did say in my first message that this could be ANY field. Any field(s) or 50 rows.
I don't want to have to search through all rows of all fields to find the spelliung error.
I want to run one query that searches everywhere for that particular spelling error phrase and change it.
Re: How do you update any field in the database?
Posted: Wed Sep 16, 2009 1:38 am
by CoderGoblin
Changing it once in the database is going to be problematic as you have to find out where the spelling errors are. As far as I know there is no way around this intensive problem. It therefore makes sense to check it at the entry point, not fix it once the horse has bolted. I assume you are filling the database in from somewhere. Check and correct the spelling where the user enters the data.
You may want to look at
Pspell as one method of doing this.
Re: How do you update any field in the database?
Posted: Wed Sep 16, 2009 4:22 am
by simonmlewis
If you can find where the errors are in a particular field, why can't you find where the errors are across the entire database?
I thought it would be something like this:
Code: Select all
UPDATE products SET * = 'Not Current Available' WHERE * = 'Not curently averible'
Would this not work?
Re: How do you update any field in the database?
Posted: Wed Sep 16, 2009 4:35 am
by onion2k
Do you need to do it in SQL, or would a PHP script work?
Code: Select all
$tables = mysql_query("SHOW TABLES");
while ($t = mysql_fetch_array($tables)) {
//Columns
$columns = mysql_query("DESCRIBE `".$t[0]."`");
while ($c = mysql_fetch_object($columns)) {
mysql_query("UPDATE `table` SET `".$c->Field."` = 'Not Current Available' WHERE `".$c->Field."` = 'Not curently averible'");
}
//End Columns
}
That will do every table in the database. If you just need to do one table just use the bit between the 'Columns' comments and replace $t[0] with the table name.
(I realise I'm mixing mysql_fetch_array() and mysql_fetch_object() ... the reason is that "SHOW TABLES" returns a field based on the database name so it's easier to access it numerically.)
Re: How do you update any field in the database?
Posted: Wed Sep 16, 2009 4:41 am
by simonmlewis
Hi - it does not matter where I do it, as long as there is a way.
I just tried the REPLACE method, but it seems I would have to do a separate query for every flamin field, which is just impractical.
I will try your method and let you know.
Thx.
Re: How do you update any field in the database?
Posted: Wed Sep 16, 2009 4:44 am
by simonmlewis
is the "SHOW TABLES" and "DESCRIBE '" a PHP method or something I have to complete myself?
Re: How do you update any field in the database?
Posted: Wed Sep 16, 2009 4:49 am
by simonmlewis
Code: Select all
<?php
include "dbconn.php";
$tables = mysql_query("SHOW TABLES");
while ($t = mysql_fetch_array($tables)) {
//Columns
$columns = mysql_query("DESCRIBE `".$t[0]."`");
while ($c = mysql_fetch_object($columns)) {
mysql_query("UPDATE `products` SET `".$c->Field."` = 'Not Current Available' WHERE `".$c->Field."` = 'Not curently averible'");
}
//End Columns
mysql_close($sqlconn);
}
?>
This produces tons of errors, but I wonder if I should be completing other areas of the query?
Re: How do you update any field in the database?
Posted: Wed Sep 16, 2009 4:50 am
by onion2k
You're closing your connection too early.