Using an array in a mysql update

Need to pull data from a table and update it using the existing data.

[garr1s0n]

So the issue here is I have to pull the data out of one field in the table (map) and add a string (link) to the beginning of it and update the field with link+map. here's the code I have so far:

<?php

$link = "http://www.example.com/page/";
$con = mysql_connect("address","username","password");
if (!$con){
die('Could not connect: ' . mysql_error());
}
else {
echo('Connection Successful');
}

mysql_select_db("databaseName", $con) or trigger_error("could not select db: " . mysql_error());

$result = mysql_query("SELECT * FROM TableName");

while($row = mysql_fetch_array($result)) {
echo("<br>");
$map = $row['columnName'];
mysql_query("UPDATE tableName SET columnName = CONCAT('$link','$map')
WHERE ColumnTwo = '0'");

\\**NOTE: All rows have columnTwo = 0
}
?>

Currently what happens is the statement updates every columnName in every row in the table with the last value in the array (15 rows):

http://www.example.com/page/map15.gif

Any ideas? Thanks a lot for your help

[edited by: dreamcatcher at 7:05 pm (utc) on Oct. 29, 2009]
[edit reason] use example.com. Thanks. [/edit]

[mvaz]

I am not sure if this could be of any help, but 0 and '0' are not the same. Could this be the reason?

$map = $row['columnName'];
Should this be columnTwo?

I could be totally wrong, but that is the only thing that I feel should be your concern.

[garr1s0n]

columnName and columnTwo are two different columns, columnTwo is a seperate column that has the value of 0 in each row. the statement is to update columnName in every row where columnTwo is 0.

also i tested the 0 vs '0' issue and it didn't make any difference.

Thanks for the input though!

[rocknbil]

I just tested this, why do you even need to do the initial select? Looks like you're selecting all records anyway - just execute the concat statement, using the column you initially selected.

update tableName set columnName=concat('$link', ' ', columnName)
where ColumnTwo='0'

You have a hard coded link, let's say it's a link from the table - this still works

update tableName set columnName=concat(linkColumn, ' ', columnName)
where ColumnTwo='0'

I added a space between the values for my tests, remove it if you don't need it.

0 and '0' are not the same

This is a confusion made by many PHP programmers. In PHP, $var = 0 casts the data type as an integer, $var = '0' casts it as a string - however, in mySQL, if the field is an integer type, it stays an integer type. So "where field=0" and "field='0'" provide identical results in mySQL, whether in a where statement, insert, update, or delete.

[garr1s0n]

i used the initial select to create an array because each row has a different value for map##.gif. does that not matter?

[jman11]

you can just update the field that you want, no need to select anything. but if you are adding a value to a current value thats in a database, just return it with this:

// your select statement
$row = mysql_fetch_array($result);
$new = $link.$row['map'];
// then update the field with teh value $new

[rocknbil]

i used the initial select to create an array because each row has a different value for map##.gif. does that not matter?

Nah, doesn't matter at all, note how it's updating the rows on the fly. Set up a test table and throw some sample records in it, it's how I made sure I knew what I was talking about before posting. :-)

if you are adding a value to a current value thats in a database, just return it with this:

Not necessary. Try the above experiments. It works with math too.

Update tablename set totals_column=integer_col1+integer_col2;