Query syntax error selecting from 3 tables
Posted
by
Toni Michel Caubet
on Stack Overflow
See other posts from Stack Overflow
or by Toni Michel Caubet
Published on 2012-05-31T01:31:42Z
Indexed on
2012/05/31
4:40 UTC
Read the original article
Hit count: 159
Given info about an object: id, user_id, group_id
Given info about an user: id_user, id_loc
I need to get i one query:
The name of the user (in table users)
The name of the location of the user (in table locs)
The name of the group of the object (in table groups)
I am trying like this:
SELECT usuarios.first_name as username, usuarios.id as userid, usuarios.avatar as useravatar, usuarios.id_loc, locs.name as locname, groups.name as groupname FROM usuarios,groups,locs WHRE usuarios.id_loc = locs.id AND usuarios.id = 1 AND group.id = LIMIT 1
having an error saying
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 'AND locs.id = 3 LIMIT 1' at line 3
What am i doing wrong? can i do this in one query?
-EDIT-
This is the query generator code (php+mysql):
$query_loc_group_user = 'SELECT usuarios.first_name as username,
usuarios.id as userid,
usuarios.avatar as useravatar,
usuarios.id_loc,
locs.name as locname,
groups.name as groupname
FROM usuarios,groups,locs
WHRE usuarios.id_loc = locs.id
AND usuarios.id = '.$this->id_user.'
AND group.id = '.$this->id_group.'
LIMIT 1';
In case it helps, i am trying to do in one query this
function get_info(){
$info;
$result = cache_query('SELECT first_name,last_name,avatar FROM usuarios WHERE id = '.$this->id_user);
foreach($result as $extra){
$info['username'] = $extra['first_name'].' '.$extra['last_name'];
$info['avatar'] = $extra['avatar'];
}
$result1 = cache_query('SELECT name FROM locs WHERE id = '.$this->id_user);
foreach($result1 as $extra){
$info['locname'] = $extra['name'];
}
$result2 = cache_query('SELECT name FROM locs WHERE id = '.$this->id_user);
foreach($result2 as $extra){
$info['groupname'] = $extra['name'];
}
return $info;
}
© Stack Overflow or respective owner