Sim Posted February 2, 2011 Share Posted February 2, 2011 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] Quote Link to comment Share on other sites More sharing options...
a_bertrand Posted February 2, 2011 Share Posted February 2, 2011 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! Quote Link to comment Share on other sites More sharing options...
Sim Posted February 2, 2011 Author Share Posted February 2, 2011 I'm trying to count # of referals and get top 10 referrers ;] Quote Link to comment Share on other sites More sharing options...
a_bertrand Posted February 2, 2011 Share Posted February 2, 2011 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] Quote Link to comment Share on other sites More sharing options...
Sim Posted March 20, 2011 Author Share Posted March 20, 2011 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.... ;[ Quote Link to comment Share on other sites More sharing options...
Recommended Posts
Join the conversation
You can post now and register later. If you have an account, sign in now to post with your account.