query returns 0 results.

[Sim]

I followed same concept as my other query's but this one doesn't work. Not sure why. I know the difference has to be because its only 1 table instead of 2. but i don't know.

Working query:

[mysql]SELECT votes.voteUserID, COUNT(votes.voteUserID) as countz, user.userID, user.userHandle

FROM user, votes

WHERE user.userID=votes.voteUserID

AND votes.voteDate>='$starttime'

AND votes.voteDate<='$endtime'

GROUP BY votes.voteUserID

ORDER BY countz

DESC LIMIT $recs[/mysql]

none working query.. it doesn't error, just returns no results. ;]

[mysql]SELECT userID, userRefID, userHandle,COUNT(userRefID) as countz

FROM user

WHERE userID=userRefID AND userRefDate>='1296540000' AND userRefDate<='1298959200' AND userActCode='0'

GROUP BY userRefID

ORDER BY countz

DESC LIMIT 10[/mysql]

[a_bertrand]

WHERE userID=userRefID ?? Are you sure you have users which point to their own ref? I'm quiet sure it doesn't happen...

Also it is really a bad practice to quote numbers. If it is a number keep it as number!

[Sim]

I'm trying to count # of referals and get top 10 referrers ;]

[a_bertrand]

Easy:

[mysql]select userRefID, count(userID) from user where userRefID is not null and userRefId <> 0 group by userRefID order by count(userID) desc limit 0,10[/mysql]

[Sim]

I feel embarassed. I got a new query that just displays my results with same ID over and OVER..

 

SELECT DISTINCT article_id, article_title, article_cat, article_meta_key, article_meta_desc, category_id, category_name, SUBSTRING_INDEX(article_text,' ',50) AS text FROM pqdb_articles, pqdb_categories WHERE article_title LIKE ('%shopper%' OR article_text LIKE '%shopper%') AND category_id = article_cat ORDER BY article_id LIMIT 0, 16

 

it displays same article_id more then once.... ;[