Page 1 of 1
mySQL updating one field
Posted: Sat Jan 10, 2004 4:13 pm
by ronack
Is there a way to update one field for every record in a in a db of over 1000 without having to do every field? (does this question make since?)
I'm new at this so keep it simple....
Re: mySQL updating one field
Posted: Sat Jan 10, 2004 4:24 pm
by Straterra
ronack wrote: a db of over 1000
1000...what? Rows, collumns, tables, databases?
Posted: Sat Jan 10, 2004 5:22 pm
by ronack
Sorry Rows
Posted: Sun Jan 11, 2004 8:41 am
by almostsane
using PHP,
Code: Select all
<?php
$db = mysql_pconnect("hostname", "username");
mysql_select_db("database");
$result = mysql_query("select * from tablename"); //get exact number of rows
$row_num = mysql_num_rows($result);
for ($i = 0; $i < $row_num; $i++)
{
$query = "update tablename set fieldname = newvalue where Key = ".(string)$i;
mysql_query($query);
}
?>
that might work? can someone else comfirm before he tries it as i'm still kinda a noob to php and mysql
Posted: Sun Jan 11, 2004 9:29 am
by ronack
Thanks, I can probably use this, but I'm just looking for an sql statement to do it at the phpmyadmin panel. Like something else instead of
INSERT INTO `table` VALUES (27, 'text', 'text', NULL, '2004-01-06 00:00:00', 'blank.gif', 55, 'blank.gif', '', '', '', '', 0, 0, '', '');
This will insert one record.
The records are already there and I need to update one field. Maybe Changing blank.gif to image.gif on all the records.
Posted: Sun Jan 11, 2004 11:32 am
by JAM
Selfexplainatory:
Code: Select all
update table set imagefield = 'FOO.gif' where imagefield = 'BAR.gif'
Loose the where clause to update all fields.
http://www.mysql.com/doc/en/UPDATE.html
Posted: Sun Jan 11, 2004 11:40 am
by ronack
Ok thanks
You have all been a great help.
Posted: Sun Jan 11, 2004 6:01 pm
by almostsane
yeah i realised after i went to bed you could just use
[mysql_man]
update table set imagefield = 'FOO.gif' where imagefield = 'BAR.gif'
[/mysql_man]