Issue searching 2 tables with query

General PHP Help
#1

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]
#2

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'";
#3

22 views, no solutions?

#4

Why are you inner joining?

#5

searching both tables for a single match.

#6

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.

#7

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

#8

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

#9

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

#10

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’…

#11

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

#12

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

#13

I’m still getting the same error.

#14

someone’s got to know this…?

#15

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.

#16

Server version: 5.0.96-log

straight from phpMYAdmin

#17

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

#18

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