Hot to update mysql_fetch_array

Question

As you will see I am fetching the column, and trying to update column with new data. $result2 line is my problem, I don't think I can add $row[0] in there. How do I do it?

$result = mysql_query("SELECT link FROM items LIMIT 3");

while($row = mysql_fetch_array($result))

    {

    $url=($row[0]);

$rez2 = get_final_url($url);

$result2 = mysql_query("UPDATE items SET link = $rez2 WHERE id = $row[0] LIMIT 1")

or die(mysql_error());

Answer 1

$row[0] is in fact valid in double-quoted strings. I think your problem is a misspelling: first you assign $rez2 a value and then in the query you use $res2.

Answer 2

What does get_final_url($url); do? If it doesn't surround link with quotes, and handle proper string escaping (i.e. mysql_real_escape_string), your query won't work.

Answer 3

You should use quotes:

mysql_query("UPDATE items SET link = '{$res2}' WHERE id = $row[0]");

And it would be ideal to use mysql_escape_string() function.

So:

$rez2 = mysql_escape_string(get_final_url($url));

Also you're trying to use $row[0] as link and as id. Most likely you want $row[0] element to be an ID, and something like $row[n] where n > 0 to be a link. But if you still want to use link you should query in the following manner:

$result2 = mysql_query("UPDATE items SET link = '$res2' WHERE link = {$row[0]}");

And do not forget to escape $row

It is a good idea to use mysql_fetch_assoc() function - in this case you'll get an associative array, so you'll be able access elements by sql column names. And as result you could do something like:

$result = mysql_query("SELECT id, link FROM items LIMIT 3");

while($row = mysql_fetch_assoc($result))

{

   $url=($row['link']);

   $rez2 = mysql_escape_string(get_final_url($url));

   $result2 = mysql_query("UPDATE items SET link = '{$res2}' WHERE id = {$row['id']}")

   or die(mysql_error());
}

Also if ID is a primary key you do not need LIMIT 1 in the update query.