CiviCRM Community Forums (archive)

I do a contact search in my 2.2.6 civicrm for ALL contacts and it displays X results.

However when I click the "LAST" button on the results page the results end at the letter "V".  Our contacts go all the way to the letter "Z".   In other words, CiviCRM thinks it is displaying the last of our results on its "last" page, but it in fact is not, there are approx 30-40 results left undisplayed.  If I click the letter "Z" in the alphabet bar, I will see the contacts who are missing from the results.

My guess is because this website has had a lot of importing and changing of contacts as well as deleting contacts that some "ghost rows" are messing up the count(*) of the search query.

Anyone experienced anything like this before?  I can't duplicate it on the demo site cause the contacts there are only about 100 of them, but I will offer a login to our site to any of the core CiviCRM developers if you want to take a look at this bug.

Thanks,
Stoob

dbQuery and CiviCRM results both show 939.  However, the last ~30 are not visible on the "last" page of the results stops somewhere in the letter "V".

Interestingly, if the number of rows per page is set to 50 the last page cuts off somewhere in the letter V.  However if the rows per page is 100, it cuts off somewhere in the letter Y.  In both cases, people are still missing, as we have several Z contacts.

Seems to be a continuing problem, have tried but cannot replicate on the demo site.  I'll linger around IRC hoping to get some insight.

In the meantime check out this fascinating screenshot.

Hey stoob - I do recall hitting this problem a fair while ago - will do a search through my posts and see if i can pick it. i was going to say it has something to do with some records being counted twice eg for people who have two primary phones or emails, so may be worth just seeing if you have any such undesiriables.

Yes you are both correct.  I really appreciate Kurund's help with this.  I am going to post the query he used on here shortly when I get it from him..  It turns out the particular site in question had been started when it was 2.0.5 and upgraded many times since then.  Somewhere along the line over 50 contacts had multiple "primary" phone numbers and emails, which is a "no-no".  Also possibly an issue is phone numbers and emails with a NULL contact_id 

Kurund's theory is this issue was either a bug of 2.0 or a bug of the 2.0 > 2.1 upgrade process.

Regardless, we were able to fix most of the pagination issues.  There are still a few left (I'm off by 5 contacts) but the majority of the work is done.

Thanks again everyone.  I will most more when I know more.

Stoob:

I was on vacation for few weeks .  Here is the query

SELECT DISTINCT(contact_a.id) as contact_id, contact_a.sort_name  as `sort_name`, civicrm_phone.id as phone_id  FROM civicrm_contact contact_a LEFT JOIN civicrm_address ON ( contact_a.id = civicrm_address.contact_id AND civicrm_address.is_primary = 1 ) LEFT JOIN civicrm_state_province ON civicrm_address.state_province_id = civicrm_state_province.id  LEFT JOIN civicrm_country ON civicrm_address.country_id = civicrm_country.id  LEFT JOIN civicrm_email ON (contact_a.id = civicrm_email.contact_id AND civicrm_email.is_primary = 1)  LEFT JOIN civicrm_phone ON (contact_a.id = civicrm_phone.contact_id AND civicrm_phone.is_primary = 1)  LEFT JOIN civicrm_im ON (contact_a.id = civicrm_im.contact_id AND civicrm_im.is_primary = 1)  LEFT JOIN civicrm_worldregion ON civicrm_country.region_id = civicrm_worldregion.id  LEFT JOIN civicrm_option_group option_group_gender ON (option_group_gender.name = 'gender') LEFT JOIN civicrm_option_value gender ON (contact_a.gender_id = gender.value AND option_group_gender.id = gender.option_group_id)  WHERE  ( 1 )    ORDER BY contact_a.sort_name, contact_a.id asc  \G;

When you fire this query you will get list of all phone_id's and you will have to manually check for duplicate phone_id and accordingly update the contact.

HTh
Kurund