[V2] User List Question

[TMan]

This is the query for the user list page.

$q=$db->query("SELECT u.*,g.* FROM users u LEFT JOIN gangs g ON u.gang=g.gangID ORDER BY $by $ord LIMIT $st,50");

 

What im wondering is how to make it so it only shows the users in the same location as the user viewing the list.

I have no idea where to go I've tried this a few different ways.

 

$q=$db->query("SELECT u.*,g.* FROM users u LEFT JOIN gangs g ON u.gang=g.gangID ORDER BY $by $ord WHERE u.location={$ir['location']} LIMIT $st,50");

 

which prints out the error

QUERY ERROR: 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 'WHERE u.location=2 LIMIT 0,50' at line 1
Query was SELECT u.*,g.* FROM users u LEFT JOIN gangs g ON u.gang=g.gangID ORDER BY userid ASC WHERE u.location=2 LIMIT 0,50

 

I'm not very good with sql yet.

Any ideas?

[Magictallguy]

Re: [V2] User List Question

The ORDER clause goes AFTER the WHERE, not before

 

$sql = sprintf(
"SELECT u.*, g.* " .
"FROM users u " .
"LEFT JOIN gangs g ON (u.gang = g.gangID) " .
"WHERE (u.location = %u) " .
"ORDER BY %s %s LIMIT %u, 50", $ir['location'], $by, $ord, $st);
$q = $db->query($sql);

 

I would also suggest filtering what people order by...

Try using

&ord=blah

somewhere in the URL..

[POG1]

Re: [V2] User List Question

Also, try not to select all from both tables ;)

[TMan]

Re: [V2] User List Question

thanks guys this worked for me :-)

I believe the $ord and the $by are set by the user clicking on Level | Ascending...

[POG1]

Re: [V2] User List Question

Yeah they will be, you obviously don't want to use the get data directly though