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]