Issue searching 2 tables with query

For some reason, my code goes directly to “Error” I’m not really sure why. I test my SQL connection, no issues. The information on the tables is there. Maybe you can spot what I’ve done incorrectly?

[php]
$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 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 “

Error

”;
}
mysql_close($con);
[/php]

just wanted to make a correction

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

is actually

$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'";

22 views, no solutions?

Why are you inner joining?

searching both tables for a single match.

Do both tables have a field called username (or server/toonname) - because if so, when outputting mysql_error(), you will most likely be getting the ambiguous column selector error.

ok yes i get the ambiguous error. lemme research how to fix then. thanks for input

ok did some research, and there is no clear or concise measure to take in order to fix this. any ideas?

Yes, the reason it is “ambiguous” is because when it goes to look up the username column, it doesn’t know which one to check as there are 2 available (1 per table) - so to make it unambiguous you must specify which you are selecting; ie. regusers_test.username or alt_toon.username

This won’t however, help with what you are trying to achieve - you will instead need to use the CASE:

UPDATE regusers_test AS rt INNER JOIN alt_toon_test AS att ON att.`toonname` = rt.`toonname` SET rt.`server` = CASE WHEN rt.`username`='$user' AND rt.`server`='$oserver' AND rt.`toonname`='$otoon' THEN '$server' ELSE rt.`server` att.`server` = CASE WHEN att.`username`='$user' AND att.`server`='$oserver' AND att.`toonname`='$otoon' THEN '$server' ELSE att.`server` WHERE ( rt.`username`='$user' AND rt.`server`='$oserver' AND rt.`toonname`='$otoon' ) OR ( att.`username`='$user' AND att.`server`='$oserver' AND att.`toonname`='$otoon' )

Note: This is untested

now i get:

You have an error in your SQL syntax; check the manual that corresponds to your mySQL server version for the right syntax to use near ‘att.‘server’=Case When att.‘username’=’$user’…

I can’t find this in the PhP Manual, can someone someone point me in the right direction?

I think the CASE statements needed “END” at the end, ie:

rt.`server` = CASE WHEN rt.`username`='$user' AND rt.`server`='$oserver' AND rt.`toonname`='$otoon' THEN '$server' ELSE rt.`server` END att.`server` = CASE WHEN att.`username`='$user' AND att.`server`='$oserver' AND att.`toonname`='$otoon' THEN '$server' ELSE att.`server` END

I’m still getting the same error.

someone’s got to know this…?

The PHP manual will not help you with issues regarding SQL. Since you’re using MySQL, take a look here and choose the version of MySQL server you are using.

Server version: 5.0.96-log

straight from phpMYAdmin

Perhaps this will help regarding the SQL Syntax: 13.6.5.1. CASE Syntax

I’ve never done what you mentioned, and after a quick look on the internet I saw that people had used CASE statements in UDPATE statements - if you still need this done, just use multiple queries instead of trying to achieve it with one. Alternatively, if you can clear any important/excess data and post a dump of the table, I will attempt to work on it with a copy of your set up

Sponsor our Newsletter | Privacy Policy | Terms of Service