[SOLVED] SQL error, cannot find the problem

[Mattyspatty]

my friend has created this query

 

$res = mysql_query(
"SELECT `users`.`id`, `users`.`admin`, `users`.`exp`, `characters`.*, `villages`.`element`,`villages`.`explore`, 
`villages`.`village` AS `villagename`, 
`fight_styles`.`name` AS `fightstyle`, 
`bloodlines`.`name` AS `bloodline` 
FROM `users` INNER JOIN `characters`, `villages`, `fight_styles`, `bloodlines` ON (`users`.`username` = `characters`.`username`) 
AND (`villages`.`ID` = `characters`.`village`) 
AND (`fight_styles`.`ID` = `characters`.`fight`) 
AND (`bloodlines`.`ID` = `characters`.`blood`) 
WHERE `users`.`username` = '" . $_SESSION['username'] . "' 
LIMIT 1"
) or die(mysql_error());

 

 

he is having some problems working out why it no lnoger works after transferring to a new server.

we have checked the database but keep getting this error:

 

You have an error in your SQL syntax near ' `villages`, `fight_styles`, `bloodlines` ON (`users`.`username` ' at line 5

 

 

does anyone know what could be a possible cause?

[trq]

All those backticks make it terribly hard to read and are simply not required.

 

Also, try storing your query in a variable, print this variable to the screen. Makes it easier to debug.

[craygo]

I believe you can only join one table at a time. try this

 

$sql = "SELECT users.id, users.admin, users.exp, characters.*, villages.element, villages.explore, 
villages.village AS villagename, fight_styles.name AS fightstyle, bloodlines.name AS bloodline  
FROM users 
INNER JOIN characters ON users.username = characters.username  
INNER JOIN villages ON villages.ID = characters.village 
INNER JOIN fight_styles ON fight_styles.ID = characters.fight 
INNER JOIN bloodlines ON bloodlines.ID = characters.blood 
WHERE users.username = '" . $_SESSION['username'] . "' 
LIMIT 1";
$res = mysql_query($sql) or die(mysql_error());

 

Ray

[Mattyspatty]

yay! that works, must be the different PHP or MySQL version or something on the new server. thanks Ray