Sign in to follow this  

Mysql - Count issues

This topic is 3198 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Recommended Posts

Hey all, how's it going? I am having a problem with my count function. I want to return the number of results that the SQL Query would normally return. Here is my query (heh)
select count(*) as count
from account_info ai, resume_main rm, 
resume_data rd, user_details ud, 
search_resume_words w, search_resume_occurence o 
WHERE ai.A_ID = rm.A_ID and rd.R_ID = rm.R_ID and ud.A_ID = ai.A_ID 
and (w.word like '%violin%') and o.word_id = w.word_id and rm.R_ID = o.R_ID
The problem here is that, in my search_resume_words table, I have two entries - 1 is violin, the other is violinist. Now for some reason it counts both of those results as seperate entries (I've used group by rm.R_ID, rm.A_ID and so forth but nada.), where as the normal sql query (while exlucing count) would return only 1 entry (It is using the same where statements though) - but I have no way of counting that Of course if I remove like '%violin%' and just make it 'violin' it gives me the correct count of data returned from the tables. But, I dont want to limit my users search results Any suggestions what to do? I want an accurate search result count. *edit* HEH! Why is it when I post this up I find a solution ALMOST immediately?Little did I realise that PHP had a function called mysql_num_rows I had a sudden instinct to go through all the commands.. and whaddya know? *groans* Sorry if anyone was reading this and redesigned it all in the process XD haha

Share this post


Link to post
Share on other sites
(i don't fully understand the problem, but) be careful with using count_rows. If you decide to do a LIMIT on the query, count_rows will only tell you how many rows came back from query, not how many records the query could have returned without the LIMIT.

Share this post


Link to post
Share on other sites
Quote:
Original post by dimitri89
Now for some reason it counts both of those results as seperate entries (I've used group by rm.R_ID, rm.A_ID and so forth but nada.), where as the normal sql query (while exlucing count) would return only 1 entry (It is using the same where statements though)


Are you sure of that ? I mean without selecting a few columns and doing 'distinct'.

Share this post


Link to post
Share on other sites

This topic is 3198 days old which is more than the 365 day threshold we allow for new replies. Please post a new topic.

If you intended to correct an error in the post then please contact us.

Create an account or sign in to comment

You need to be a member in order to leave a comment

Create an account

Sign up for a new account in our community. It's easy!

Register a new account

Sign in

Already have an account? Sign in here.

Sign In Now

Sign in to follow this