How to update 1 field in all records of db?

[stevens]

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.

[king arthur]

How are the list of names defined?

[ToonMariner]

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.

[stevens]

[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?

[stevens]

[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?

[king arthur]

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.

[stevens]

[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?

[stevens]

[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?

[king arthur]

[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.

[stevens]

[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?

[king arthur]

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='".

[stevens]

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.

[king arthur]

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.

[stevens]

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.