How do you update any field in the database?

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

How do you update any field in the database?

Post 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?
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
d.unicorn
Forum Newbie
Posts: 2
Joined: Tue Sep 08, 2009 1:23 pm

Re: How do you update any field in the database?

Post by d.unicorn »

update <table_name> set <table_field1> = <table_value1>, <table_field2> = <table_value2>, ..... where <primary_key_field> = primary_key_value
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: How do you update any field in the database?

Post 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??
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
gigabit
Forum Newbie
Posts: 4
Joined: Tue Sep 15, 2009 1:48 pm

Re: How do you update any field in the database?

Post by gigabit »

Code: Select all

UPDATE products SET table_field = replace(table_field,'replace_that','with_this');
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: How do you update any field in the database?

Post by simonmlewis »

What is table_field ?
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
gigabit
Forum Newbie
Posts: 4
Joined: Tue Sep 15, 2009 1:48 pm

Re: How do you update any field in the database?

Post by gigabit »

The field in the table youre updating.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: How do you update any field in the database?

Post 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.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
CoderGoblin
DevNet Resident
Posts: 1425
Joined: Tue Mar 16, 2004 10:03 am
Location: Aachen, Germany

Re: How do you update any field in the database?

Post 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.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: How do you update any field in the database?

Post 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?
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: How do you update any field in the database?

Post 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.)
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: How do you update any field in the database?

Post 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.
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: How do you update any field in the database?

Post by simonmlewis »

is the "SHOW TABLES" and "DESCRIBE '" a PHP method or something I have to complete myself?
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
simonmlewis
DevNet Master
Posts: 4435
Joined: Wed Oct 08, 2008 3:39 pm
Location: United Kingdom
Contact:

Re: How do you update any field in the database?

Post 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?
Love PHP. Love CSS. Love learning new tricks too.
All the best from the United Kingdom.
User avatar
onion2k
Jedi Mod
Posts: 5263
Joined: Tue Dec 21, 2004 5:03 pm
Location: usrlab.com

Re: How do you update any field in the database?

Post by onion2k »

You're closing your connection too early.
Post Reply