[SOLVED] Paginated query with left join - trouble getting order

[amites]

Hello,

 

been a little while since I stopped by here, the new site looks great,

 

anyway on to my question,

 

I built a query that uses a dynamic left join to gather 3 sets of data from the same table, the data it's pulling is a list of users who have made posts in a contact system

 

the trouble I'm having is that I made the query paginate the results, and when it gets from page 2 to page 3 the entries begin repeating, I tried using order by which ended up with 0 results,

 

any ideas?

		$query = "SELECT u.id AS user_id, u.username AS name, Count(l.id) AS cnt
				FROM bil_users AS u
				Left Join bil_msg_look AS l ON l.userid = u.id";
		if ($active == 1) {
			$query .= "\nAND l.active =  '$active'";
			if ($read >= 1) {
				$query .= "\nAND l.has_results >=  '1'";
			}
		}
		if (isset($limit_end) && intval( $limit_end) > 2007) {
			$query .= "\nAND l.created BETWEEN '$limit_begin' AND '$limit_end'";
		}
// original attempt to calm query, works with repeats after the first few pages
	$query .= "\nGROUP BY u.id"
// pagination
."					 LIMIT $limit_bg, $limit_end";

[amites]

suppose I could simplify this by working with a single query,

 

$query = "SELECT u.id AS user_id, u.username AS name, Count(l.id) AS cnt
FROM bil_users AS u
Left Join bil_msg_look AS l ON l.userid = u.id
GROUP BY u.id
LIMIT 0, 20"

 

trouble is that if I make it look like:

 

$query = "SELECT u.id AS user_id, u.username AS name, Count(l.id) AS cnt
FROM bil_users AS u
Left Join bil_msg_look AS l ON l.userid = u.id
GROUP BY u.id
LIMIT 0, 20
ORDER BY name ASC"

 

then I get 0 results, so the question becomes: How can I run this query to get results in the same order each time it runs (I don't care how they are ordered)??

[fenway]

As long as you're ordering by the non-joined table, the order should remain the same.

[amites]

when I run the query with the ORDER BY I get an error

 

question is, how can I order the results?

[fenway]

What error?

[amites]

syntax error at line 6

 

near ORDER BY name ASC

[fenway]

LIMIT goes after ORDER BY.

[amites]

THANK YOU FENWAY!!

 

seems to be those little obvious syntax errors that take me the longest to find