CODEWITHSUNDEEP
phpmysql
CSharpMinor
CSharpMinor

Reputation: 232

PHP/MySQL UPDATE SET WHERE, a query of two tables

This code goes to "Else" and I get "Column 'server' in field list is ambiguous"

$otoon = mysql_real_escape_string($_POST['otoon']);
$oserver = mysql_real_escape_string($_POST['oserver']);
$server = mysql_real_escape_string($_POST['server']);

$sql="UPDATE regusers_test
INNER JOIN alt_toon_test ON (alt_toon_test.toonname = regusers_test.toonname)
SET server='$server'
WHERE username='$user' AND server='$oserver' AND toonname='$otoon'";

if (!empty($server)) {
  if (mysql_query($sql,$con)) {
    echo "Server updated to: " . $server;
  } else {
        echo "<h1>Error</h1>";
}
mysql_close($con);

I even tried changing $sql to

$gql="UPDATE regusers_test, alt_toon_test SET server='$server' WHERE username='$user' AND server='$oserver' AND toonname='$otoon'";

But i still get the same error. I'm banging my head against the wall here. I know what causes the error. I have 2 fields with the same name. I just don't know how to get past this.

Upvotes: 0

Views: 80

Answers (1)

Michael Krikorev
Michael Krikorev

Reputation: 2156

You have to specify the table names in your SET and WHERE clause. That is, something like this:

$sql="UPDATE regusers_test
INNER JOIN alt_toon_test ON (alt_toon_test.toonname = regusers_test.toonname)
SET regusers_test.server='$server'
WHERE regusers_test.username='$user' AND regusers_test.server='$oserver' AND regusers_test.toonname='$otoon'";

Would be even shorter query if you used aliases:

$sql="UPDATE regusers_test a
INNER JOIN alt_toon_test b ON (b.toonname = a.toonname)
SET a.server='$server'
WHERE a.username='$user' 
  AND a.server='$oserver' 
  AND a.toonname='$otoon'";

You should also consider switching to mysqli or PDO extensions since mysql_query() is deprecated and will be removed in future updates of PHP versions.

Upvotes: 1

Related Questions