[RESOLVED] Syntax error when limiting SELECT

gamber

Trying to limit the reported users within a report to be equal to, or less than, their individual id.

That is limit Subscribers from seeing Members, etc (where the top down hierarchy is System Admin, Admin, Contributors, Members & Subscribers)

Here is my offending code:

	$query_rsUser = "SELECT UserID, Username, FirstName, LastName, Active, usertype.UserType FROM `user`, usertype 
		WHERE `user`.AccessLevel = usertype.AccessLevel
		HAVING Active = '1', `user`.AccessLevel >= ('{$_SESSION['loginAccessLevel']}')
		ORDER BY usertype.AccessLevel ASC, Username ASC";
	$query_limit_rsUser = sprintf("%s LIMIT %d, %d", $query_rsUser, $startRow_rsUser, $maxRows_rsUser);
	$rsUser = mysql_query($query_limit_rsUser, $connMI) or die('<p>Could not select the database because: <br /><b>' .mysql_error().'</b></p>');
	$row_rsUser = mysql_fetch_assoc($rsUser);

which is giving me an error of:

Could not select the database because:
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 ' user.AccessLevel >= ('1') ORDER BY usertype.AccessLevel ASC, Username ASC ' at line 3

All is fine when I eliminate the , user.AccessLevel >= ('{$_SESSION['loginAccessLevel']}') portion from HAVING, but then it gives a full list of all users.

BTW - able to limit other inquiries with the same portion of code.

pppppp

for having clause there should be a group by clause

pppppp

$query_rsUser = "SELECT UserID, Username, FirstName, LastName, Active, usertype.UserType FROM user, usertype
WHERE user.AccessLevel = usertype.AccessLevel
HAVING Active = '1' AND user.AccessLevel >= ('{$_SESSION['loginAccessLevel']}')
ORDER BY usertype.AccessLevel ASC, Username ASC";
$query_limit_rsUser = sprintf("%s LIMIT %d, %d", $query_rsUser, $startRow_rsUser, $maxRows_rsUser);
$rsUser = mysql_query($query_limit_rsUser, $connMI) or die('Could not select the database because: ' .mysql_error().'');
$row_rsUser = mysql_fetch_assoc($rsUser);

....... there should be "AND" instead of "," in having clause

gamber

my confusion is that some queries use "," and some use "AND" - thanks for clarification

The code did not work as you posted ppppp, it should be in the WHERE not the HAVING component of the selection

corrected code is:

	$query_rsUser = "SELECT UserID, Username, FirstName, LastName, Active, usertype.UserType FROM `user`, usertype 
		WHERE `user`.AccessLevel = usertype.AccessLevel AND `user`.AccessLevel >= ('{$_SESSION['loginAccessLevel']}')
		HAVING Active = '1' 
		ORDER BY usertype.AccessLevel ASC, Username ASC";