Jump to content

How to update 1 field in all records of db?


stevens

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.


Link to comment
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.
Link to comment
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?
Link to comment
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?
Link to comment
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?
Link to comment
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.
Link to comment
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?
Link to comment
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='".
Link to comment
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.
Link to comment
Share on other sites

This thread is more than a year old. Please don't revive it unless you have something important to add.

Join the conversation

You can post now and register later. If you have an account, sign in now to post with your account.

Guest
Reply to this topic...

×   Pasted as rich text.   Restore formatting

  Only 75 emoji are allowed.

×   Your link has been automatically embedded.   Display as a link instead

×   Your previous content has been restored.   Clear editor

×   You cannot paste images directly. Upload or insert images from URL.

×
×
  • Create New...

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.