[solved] CiviEvent and Views integration -- no results

[GinkgoFJG]

I set up a View in Drupal to display all active, public CiviEvents.

The Views UI tells me this is generated query:

Code: [Select]

SELECT civicrm_event.id AS id,
civicrm_event.title AS civicrm_event_title,
civicrm_address.street_address AS civicrm_address_street_address,
civicrm_event.start_date AS civicrm_event_start_date,
DATE_FORMAT((civicrm_event.start_date + INTERVAL -14400 SECOND), '%Y%m%%d%H%i') AS civicrm_event_start_date_minute,
DATE_FORMAT((civicrm_event.end_date + INTERVAL -14400 SECOND), '%Y%m%%d%H%i') AS civicrm_event_end_date_minute
FROM civicrm_event civicrm_event
LEFT JOIN civicrm_loc_block civicrm_loc_block ON civicrm_event.loc_block_id = civicrm_loc_block.id
LEFT JOIN civicrm_address civicrm_address ON civicrm_loc_block.address_id = civicrm_address.id
WHERE (civicrm_event.is_active <> 0) AND (civicrm_event.is_public <> 0)
ORDER BY civicrm_event_start_date_minute ASC, civicrm_event_end_date_minute ASC
When I run that query via PHPMyAdmin, I get one result -- exactly what I expect.  However, Views is opting to display the "Empty text" that I've specified for cases in which no results are returned.  I've tried flushing all caches via the Admin Menu module, to no avail.

I'm not sure if this is a CiviCRM problem or a Views problem, but I figured I was most likely to get a useful answer here.

By the way, CiviCRM is v. 3.1.6, and Views is v. 6.x-2.11.

Thank you,
-Pitt

[JoeMurray]

Check your version of the Date module and search d.o for issues that arise between Date and Views. That MAY be the problem.

[Eileen]

Hi,

I haven't had any bright ideas but we are definitely using Events Views using that mix of drupal/ civi.

[GinkgoFJG]

Sorry for the long delay in responding.  We found that the cause of the problem is that we were using separate databases for Drupal and CiviCRM.  If we copy the relevant tables from our CiviCRM db to our Drupal db, everything works as expected.  Fortunately, for this particular application, the data was fairly static and we could get away with this.

I haven't dug into the code, but my gut tells me this is a problem with Views, as it wouldn't have a way of knowing about the other database.  Then again, maybe Civi's integration with Views needs to inform Views of this possibility.  Anyone have any insight into where the fix should go?

[petednz]

you may need to check up on issues relating to prefixing tables - can't offer more advice but may help you down the right track

[GinkgoFJG]

Yes, that's right.  Here's some documentation on that: http://wiki.civicrm.org/confluence/display/CRMDOC32/Views2+Integration+Module (see second bullet point).

And here's the $db_prefix array you should use for a default install, though you will need to update this with each custom data group you create, as each custom data group is stored in its own database table.  Also, this list may change as the CiviCRM database changes shape through different versions, so please don't regard this as a definitive list and always refer to the documentation (or your own database structure) if in doubt:

Code: [Select]

$my_civicrm_db_name = 'example_civicrm.'; // change this to the name of your database, but be sure to leave the period at the end
$db_prefix = array(
  'civicrm_acl'                              => $my_civicrm_db_name,
  'civicrm_acl_cache'                        => $my_civicrm_db_name,
  'civicrm_acl_contact_cache'                => $my_civicrm_db_name,
  'civicrm_acl_entity_role'                  => $my_civicrm_db_name,
  'civicrm_activity'                         => $my_civicrm_db_name,
  'civicrm_activity_assignment'              => $my_civicrm_db_name,
  'civicrm_activity_target'                  => $my_civicrm_db_name,
  'civicrm_address'                          => $my_civicrm_db_name,
  'civicrm_address_format'                   => $my_civicrm_db_name,
  'civicrm_cache'                            => $my_civicrm_db_name,
  'civicrm_case'                             => $my_civicrm_db_name,
  'civicrm_case_activity'                    => $my_civicrm_db_name,
  'civicrm_case_contact'                     => $my_civicrm_db_name,
  'civicrm_component'                        => $my_civicrm_db_name,
  'civicrm_contact'                          => $my_civicrm_db_name,
  'civicrm_contact_type'                     => $my_civicrm_db_name,
  'civicrm_contribution'                     => $my_civicrm_db_name,
  'civicrm_contribution_page'                => $my_civicrm_db_name,
  'civicrm_contribution_product'             => $my_civicrm_db_name,
  'civicrm_contribution_recur'               => $my_civicrm_db_name,
  'civicrm_contribution_soft'                => $my_civicrm_db_name,
  'civicrm_contribution_type'                => $my_civicrm_db_name,
  'civicrm_contribution_widget'              => $my_civicrm_db_name,
  'civicrm_country'                          => $my_civicrm_db_name,
  'civicrm_county'                           => $my_civicrm_db_name,
  'civicrm_currency'                         => $my_civicrm_db_name,
  'civicrm_custom_field'                     => $my_civicrm_db_name,
  'civicrm_custom_group'                     => $my_civicrm_db_name,
  'civicrm_dashboard'                        => $my_civicrm_db_name,
  'civicrm_dashboard_contact'                => $my_civicrm_db_name,
  'civicrm_dedupe_rule'                      => $my_civicrm_db_name,
  'civicrm_dedupe_rule_group'                => $my_civicrm_db_name,
  'civicrm_discount'                         => $my_civicrm_db_name,
  'civicrm_domain'                           => $my_civicrm_db_name,
  'civicrm_email'                            => $my_civicrm_db_name,
  'civicrm_entity_file'                      => $my_civicrm_db_name,
  'civicrm_entity_financial_trxn'            => $my_civicrm_db_name,
  'civicrm_entity_tag'                       => $my_civicrm_db_name,
  'civicrm_event'                            => $my_civicrm_db_name,
  'civicrm_file'                             => $my_civicrm_db_name,
  'civicrm_financial_account'                => $my_civicrm_db_name,
  'civicrm_financial_trxn'                   => $my_civicrm_db_name,
  'civicrm_grant'                            => $my_civicrm_db_name,
  'civicrm_group'                            => $my_civicrm_db_name,
  'civicrm_group_contact'                    => $my_civicrm_db_name,
  'civicrm_group_contact_cache'              => $my_civicrm_db_name,
  'civicrm_group_nesting'                    => $my_civicrm_db_name,
  'civicrm_group_organization'               => $my_civicrm_db_name,
  'civicrm_im'                               => $my_civicrm_db_name,
  'civicrm_line_item'                        => $my_civicrm_db_name,
  'civicrm_loc_block'                        => $my_civicrm_db_name,
  'civicrm_location_type'                    => $my_civicrm_db_name,
  'civicrm_log'                              => $my_civicrm_db_name,
  'civicrm_mail_settings'                    => $my_civicrm_db_name,
  'civicrm_mailing'                          => $my_civicrm_db_name,
  'civicrm_mailing_bounce_pattern'           => $my_civicrm_db_name,
  'civicrm_mailing_bounce_type'              => $my_civicrm_db_name,
  'civicrm_mailing_component'                => $my_civicrm_db_name,
  'civicrm_mailing_event_bounce'             => $my_civicrm_db_name,
  'civicrm_mailing_event_confirm'            => $my_civicrm_db_name,
  'civicrm_mailing_event_delivered'          => $my_civicrm_db_name,
  'civicrm_mailing_event_forward'            => $my_civicrm_db_name,
  'civicrm_mailing_event_opened'             => $my_civicrm_db_name,
  'civicrm_mailing_event_queue'              => $my_civicrm_db_name,
  'civicrm_mailing_event_reply'              => $my_civicrm_db_name,
  'civicrm_mailing_event_subscribe'          => $my_civicrm_db_name,
  'civicrm_mailing_event_trackable_url_open' => $my_civicrm_db_name,
  'civicrm_mailing_event_unsubscribe'        => $my_civicrm_db_name,
  'civicrm_mailing_group'                    => $my_civicrm_db_name,
  'civicrm_mailing_job'                      => $my_civicrm_db_name,
  'civicrm_mailing_spool'                    => $my_civicrm_db_name,
  'civicrm_mailing_trackable_url'            => $my_civicrm_db_name,
  'civicrm_mapping'                          => $my_civicrm_db_name,
  'civicrm_mapping_field'                    => $my_civicrm_db_name,
  'civicrm_membership'                       => $my_civicrm_db_name,
  'civicrm_membership_block'                 => $my_civicrm_db_name,
  'civicrm_membership_log'                   => $my_civicrm_db_name,
  'civicrm_membership_payment'               => $my_civicrm_db_name,
  'civicrm_membership_status'                => $my_civicrm_db_name,
  'civicrm_membership_type'                  => $my_civicrm_db_name,
  'civicrm_menu'                             => $my_civicrm_db_name,
  'civicrm_msg_template'                     => $my_civicrm_db_name,
  'civicrm_navigation'                       => $my_civicrm_db_name,
  'civicrm_note'                             => $my_civicrm_db_name,
  'civicrm_openid'                           => $my_civicrm_db_name,
  'civicrm_openid_associations'              => $my_civicrm_db_name,
  'civicrm_openid_nonces'                    => $my_civicrm_db_name,
  'civicrm_option_group'                     => $my_civicrm_db_name,
  'civicrm_option_value'                     => $my_civicrm_db_name,
  'civicrm_participant'                      => $my_civicrm_db_name,
  'civicrm_participant_payment'              => $my_civicrm_db_name,
  'civicrm_participant_status_type'          => $my_civicrm_db_name,
  'civicrm_payment_processor'                => $my_civicrm_db_name,
  'civicrm_payment_processor_type'           => $my_civicrm_db_name,
  'civicrm_pcp'                              => $my_civicrm_db_name,
  'civicrm_pcp_block'                        => $my_civicrm_db_name,
  'civicrm_persistent'                       => $my_civicrm_db_name,
  'civicrm_phone'                            => $my_civicrm_db_name,
  'civicrm_pledge'                           => $my_civicrm_db_name,
  'civicrm_pledge_block'                     => $my_civicrm_db_name,
  'civicrm_pledge_payment'                   => $my_civicrm_db_name,
  'civicrm_preferences'                      => $my_civicrm_db_name,
  'civicrm_preferences_date'                 => $my_civicrm_db_name,
  'civicrm_premiums'                         => $my_civicrm_db_name,
  'civicrm_premiums_product'                 => $my_civicrm_db_name,
  'civicrm_price_field'                      => $my_civicrm_db_name,
  'civicrm_price_set'                        => $my_civicrm_db_name,
  'civicrm_price_set_entity'                 => $my_civicrm_db_name,
  'civicrm_product'                          => $my_civicrm_db_name,
  'civicrm_project'                          => $my_civicrm_db_name,
  'civicrm_relationship'                     => $my_civicrm_db_name,
  'civicrm_relationship_type'                => $my_civicrm_db_name,
  'civicrm_report_instance'                  => $my_civicrm_db_name,
  'civicrm_saved_search'                     => $my_civicrm_db_name,
  'civicrm_state_province'                   => $my_civicrm_db_name,
  'civicrm_subscription_history'             => $my_civicrm_db_name,
  'civicrm_tag'                              => $my_civicrm_db_name,
  'civicrm_task'                             => $my_civicrm_db_name,
  'civicrm_task_status'                      => $my_civicrm_db_name,
  'civicrm_tell_friend'                      => $my_civicrm_db_name,
  'civicrm_timezone'                         => $my_civicrm_db_name,
  'civicrm_uf_field'                         => $my_civicrm_db_name,
  'civicrm_uf_group'                         => $my_civicrm_db_name,
  'civicrm_uf_join'                          => $my_civicrm_db_name,
  'civicrm_uf_match'                         => $my_civicrm_db_name,
  'civicrm_website'                          => $my_civicrm_db_name,
  'civicrm_worldregion'                      => $my_civicrm_db_name,
);