Jump to content

Archived

This topic is now archived and is closed to further replies.

stevens

How to update 1 field in all records of db?

Recommended Posts

Hi I hope you can help me.

I need to update 1 field in every record of my database, how can i do this?

I have the following fields in my table:
id
name
address
email

I want to update the 'name' field in every record in the table (around 400) each with a different name.

Could i use something like this:

[quote]$query = "UPDATE table SET name = 'tom', name = 'bob', name = 'john', etc...";[/quote]

Somehow i dont think the above query will work  :-[, lol. So how can i do this? thanks.


Share this post


Link to post
Share on other sites
no you would need to change each name individually BUt you can do it in one query.

$qry = "UPDATE table SET name = 'tom' WHERE id='1'";
$qry = mysql_query($qry);

That is your query to update an individual record. Now you could have:

$qry = "UPDATE table SET name = 'tom' WHERE id='1';";
$qry .= "UPDATE table SET name = 'dick' WHERE id='2';";
$qry .= "UPDATE table SET name = 'harry' WHERE id='3';";

$qry = mysql_query($qry);

so if you generate you query string in a doo loop you could do it all pretty quickly.

Share this post


Link to post
Share on other sites
[quote author=king arthur link=topic=103338.msg411422#msg411422 date=1154991347]
How are the list of names defined?
[/quote]
???

currently i do not have a list of names to input. I was thinking of creating a text field where i could enter the names seperated by a comma and then input this into the query. Is that what you mean?

Share this post


Link to post
Share on other sites
[quote author=ToonMariner link=topic=103338.msg411424#msg411424 date=1154991425]
no you would need to change each name individually BUt you can do it in one query.

$qry = "UPDATE table SET name = 'tom' WHERE id='1'";
$qry = mysql_query($qry);

That is your query to update an individual record. Now you could have:

$qry = "UPDATE table SET name = 'tom' WHERE id='1';";
$qry .= "UPDATE table SET name = 'dick' WHERE id='2';";
$qry .= "UPDATE table SET name = 'harry' WHERE id='3';";

$qry = mysql_query($qry);

so if you generate you query string in a doo loop you could do it all pretty quickly.
[/quote]
So i could create a text area and input 400 names seperated by a comma. Then do an explode function on the commas and i would get 400 variables i could insert into the 400 query's. Is that correct?

Share this post


Link to post
Share on other sites
Ok, well are all the id fields in contiguous order? Then you could do it as ToonMariner says, with a loop incrementing the id for each query. If not, then pull all the id's out first and then update the rows with names for each id you pulled out.

Share this post


Link to post
Share on other sites
[quote author=king arthur link=topic=103338.msg411436#msg411436 date=1154991765]
Ok, well are all the id fields in contiguous order? Then you could do it as ToonMariner says, with a loop incrementing the id for each query. If not, then pull all the id's out first and then update the rows with names for each id you pulled out.
[/quote]
Ok I have managed to get all my current names into a text field seperated by a comma (,) Now i need to insert these into the table (yes the id fields are in contiguous order).

How can insert these into the db? at the moment they are all stored together in the variable $names seperated by a comma. If i do an explode to seperate each of the names i am going to have to do it 400 times, there must be an easier way?

Share this post


Link to post
Share on other sites
[quote author=ToonMariner link=topic=103338.msg411424#msg411424 date=1154991425]
so if you generate you query string in a doo loop you could do it all pretty quickly.
[/quote]
How would i do this in a do loop?

Share this post


Link to post
Share on other sites
[code]
$names_array = explode(",", $names);

$result = mysql_query("select id from mytable order by id");

$i = 0;
while($row = mysql_fetch_assoc($result))
{
$r = mysql_query("update mytable set name=" . $names_array[$i] . " where id=" . $row["id"]);
}
[/code]

See if that works. Obviously change "mytable" to the name of your table.

Share this post


Link to post
Share on other sites
[quote author=king arthur link=topic=103338.msg411447#msg411447 date=1154994071]
[code]
$names_array = explode(",", $names);

$result = mysql_query("select id from mytable order by id");

$i = 0;
while($row = mysql_fetch_assoc($result))
{
$r = mysql_query("update mytable set name=" . $names_array[$i] . " where id=" . $row["id"]);
}
[/code]
See if that works. Obviously change "mytable" to the name of your table.[/quote]
Hi, no errors show, but it does not update the database, any ideas?

Thanks.

This is my code:
[quote]$words = $_POST['words'];

$connection = mysql_connect($hostname, $user, $pass) or die ("Unable to connect!");
mysql_select_db($db, $connection);

if ($words)
{

$word_array = explode(",", $words);

$result = mysql_query("SELECT id FROM details ORDER BY id");

$i = 0;
while($row = mysql_fetch_assoc($result))
{
$r = mysql_query("UPDATE details SET word=" . $word_array[$i] . " WHERE id=" . $row["id"]);
}

}[/quote]

Do i need to do $i + 1 in the mysql_fetch_assoc? so its adds 1 each row?

Share this post


Link to post
Share on other sites
Couple of changes:
[code]
$words = $_POST['words'];

$connection = mysql_connect($hostname, $user, $pass) or die ("Unable to connect!");
mysql_select_db($db, $connection);

if ($words)
      {

$word_array = explode(",", $words);

$result = mysql_query("SELECT id FROM details ORDER BY id");

$i = 0;
while($row = mysql_fetch_assoc($result))
{
$r = mysql_query("UPDATE details SET name='" . $word_array[$i++] . "' WHERE id=" . $row["id"]);
}

}
[/code]

I forgot to increment $i and forgot the single quotes around the word. If your column in the details table is called "name" then it is "SET name='" in the query, not "SET word='".

Share this post


Link to post
Share on other sites
Still doesnt work ??? yet there is no error message either.

Im lost now, I have no idea why it is not working.

Thanks for your help though.

Share this post


Link to post
Share on other sites
Ok let's see if there is a problem with the queries, change this line
[code]
$result = mysql_query("SELECT id FROM details ORDER BY id");
[/code]

to
[code]
$result = mysql_query("SELECT id FROM details ORDER BY id") or die(mysql_error());
[/code]

and this line
[code]
$r = mysql_query("UPDATE details SET name='" . $word_array[$i++] . "' WHERE id=" . $row["id"]);
[/code]

to
[code]
$r = mysql_query("UPDATE details SET name='" . $word_array[$i++] . "' WHERE id=" . $row["id"]) or die(mysql_error());
[/code]

and see what it says.

Share this post


Link to post
Share on other sites
Works perfectly now!! There was an error as it couldnt find "name" field, so i changed it to "word" and it worked! my fault for changing the name field to word half way through coding!!

Thanks a million for your help.

Share this post


Link to post
Share on other sites

×

Important Information

We have placed cookies on your device to help make this website better. You can adjust your cookie settings, otherwise we'll assume you're okay to continue.