Jump to content


Photo

How to update 1 field in all records of db?


  • Please log in to reply
13 replies to this topic

#1 stevens

stevens
  • Members
  • PipPip
  • Member
  • 20 posts

Posted 07 August 2006 - 10:52 PM

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:

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


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



I wish i'd have thought up www.500wordsite.com !

#2 king arthur

king arthur
  • Members
  • PipPipPip
  • Advanced Member
  • 335 posts
  • LocationUK HQ

Posted 07 August 2006 - 10:55 PM

How are the list of names defined?
Sir Isaac Newton said "If I have seen farther, it is by standing on the shoulders of giants". But it is not recorded as to whether he said it before or after he was hit on the head by a falling apple.

#3 ToonMariner

ToonMariner
  • Members
  • PipPipPip
  • Advanced Member
  • 3,342 posts
  • LocationNewcastle upon Tyne, UK

Posted 07 August 2006 - 10:57 PM

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.
follow me on twitter @PHPsycho

#4 stevens

stevens
  • Members
  • PipPip
  • Member
  • 20 posts

Posted 07 August 2006 - 10:58 PM

How are the list of names defined?

???

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?
I wish i'd have thought up www.500wordsite.com !

#5 stevens

stevens
  • Members
  • PipPip
  • Member
  • 20 posts

Posted 07 August 2006 - 11:00 PM

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.

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?
I wish i'd have thought up www.500wordsite.com !

#6 king arthur

king arthur
  • Members
  • PipPipPip
  • Advanced Member
  • 335 posts
  • LocationUK HQ

Posted 07 August 2006 - 11:02 PM

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.
Sir Isaac Newton said "If I have seen farther, it is by standing on the shoulders of giants". But it is not recorded as to whether he said it before or after he was hit on the head by a falling apple.

#7 stevens

stevens
  • Members
  • PipPip
  • Member
  • 20 posts

Posted 07 August 2006 - 11:29 PM

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.

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?
I wish i'd have thought up www.500wordsite.com !

#8 stevens

stevens
  • Members
  • PipPip
  • Member
  • 20 posts

Posted 07 August 2006 - 11:40 PM

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

How would i do this in a do loop?
I wish i'd have thought up www.500wordsite.com !

#9 king arthur

king arthur
  • Members
  • PipPipPip
  • Advanced Member
  • 335 posts
  • LocationUK HQ

Posted 07 August 2006 - 11:41 PM

$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"]);
}

See if that works. Obviously change "mytable" to the name of your table.
Sir Isaac Newton said "If I have seen farther, it is by standing on the shoulders of giants". But it is not recorded as to whether he said it before or after he was hit on the head by a falling apple.

#10 stevens

stevens
  • Members
  • PipPip
  • Member
  • 20 posts

Posted 07 August 2006 - 11:51 PM

$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"]);
}
See if that works. Obviously change "mytable" to the name of your table.

Hi, no errors show, but it does not update the database, any ideas?

Thanks.

This is my 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 word=" . $word_array[$i] . " WHERE id=" . $row["id"]);
}

}


Do i need to do $i + 1 in the mysql_fetch_assoc? so its adds 1 each row?
I wish i'd have thought up www.500wordsite.com !

#11 king arthur

king arthur
  • Members
  • PipPipPip
  • Advanced Member
  • 335 posts
  • LocationUK HQ

Posted 08 August 2006 - 12:06 AM

Couple of changes:
$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"]);
}

}

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='".
Sir Isaac Newton said "If I have seen farther, it is by standing on the shoulders of giants". But it is not recorded as to whether he said it before or after he was hit on the head by a falling apple.

#12 stevens

stevens
  • Members
  • PipPip
  • Member
  • 20 posts

Posted 08 August 2006 - 12:14 AM

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.
I wish i'd have thought up www.500wordsite.com !

#13 king arthur

king arthur
  • Members
  • PipPipPip
  • Advanced Member
  • 335 posts
  • LocationUK HQ

Posted 08 August 2006 - 12:27 AM

Ok let's see if there is a problem with the queries, change this line
$result = mysql_query("SELECT id FROM details ORDER BY id");

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

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

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

and see what it says.
Sir Isaac Newton said "If I have seen farther, it is by standing on the shoulders of giants". But it is not recorded as to whether he said it before or after he was hit on the head by a falling apple.

#14 stevens

stevens
  • Members
  • PipPip
  • Member
  • 20 posts

Posted 08 August 2006 - 12:45 AM

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.
I wish i'd have thought up www.500wordsite.com !




0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users