Drupal 7 - Views 3 - CiviCRM 4.4 Views Integration

[farmrchrys]

Hello,

I have followed the instructions (http://wiki.civicrm.org/confluence/display/CRMDOC40/Views3+Integration) to edit my settings.php file. I have separate databases for Drupal and CiviCRM, but the same host, username and password for both databases.

My settings.php file now includes this:

Code: [Select]

$databases = array (
  'default' =>
  array (
    'default' =>
    array (
      'database' => 'smac_drupal',
      'username' => 'myusername',
      'password' => 'mypassword',
      'host' => 'localhost',
      'port' => '',
      'driver' => 'mysql',
      'prefix' => '',
    ),
  ),
);
$databases['default']['default']['prefix']= array(
  'default' => '',
  'civicrm_acl'                              => '`smac_civicrm`.',
  'civicrm_acl_cache'                        => '`smac_civicrm`.',
  'civicrm_acl_contact_cache'                => '`smac_civicrm`.',
  'civicrm_acl_entity_role'                  => '`smac_civicrm`.',
 ... etc. ...
  'civicrm_website'                          => '`smac_civicrm`.',
  'civicrm_word_replacement'                 => '`smac_civicrm`.',
  'civicrm_worldregion'                      => '`smac_civicrm`.',
);
I enabled the CiviCRM Events view, and the calendar displays more or less as expected, but it does not display events. The Views preview gives me this error:

SQLSTATE[42S02]: Base table or view not found: 1146 Table 'smac_drupal.civicrm_event' doesn't exist

However, I am able to see all the CiviCRM fields when I click "add" if I choose to add a new field to the view.

Any ideas why this is not working?

[petednz]

what do you get if you create a new View based on Events - same issue?

[farmrchrys]

Yes, same issue. I can set up the view (choosing CiviCRM fields), but I get the same error on the view preview and even if I set up a text rather than a calendar display, it is blank.

[petednz]

Query I got from a 'civi contact' view is

Code: [Select]

SELECT civicrm_contact.id AS id
FROM
{civicrm_contact} civicrm_contactand that works in Views and in sql

Query I get from a 'civi event' view is

Code: [Select]

SELECT civicrm_event.id AS id
FROM
{civicrm_event} civicrm_event
which also works in sql but in Views just gives me Array as the result for Event ID

[farmrchrys]

Thank you, Pete. Forgive me, I'm not an expert. Is this supposed to give me a clue to a solution? Unfortunately, I'm still clueless.

[petednz]

well it is a clue for someone who is able to look at the code in the civi-views module and go 'omg why is this like that' ;-)
which is neither of us - and i will task to one of the fuzion crew to poke their nose in but unlikely to be in near future

other person to poke is chris chinchilla who took on supporting that module from memory - if i see him i will

[farmrchrys]

Well, I hope you do run into Mr. Chinchilla.

Just for clarification, is the "civi-views" module you refer to the one located at /sites/all/modules/civicrm/drupal/modules/views?

Thanks.

[Hershel]

You need to review your settings.php file as I don't think you have the required code.

[farmrchrys]

Not the first time I have felt dumb on here: My mistake. I had edited the settings file and thought I had uploaded it, but I missed the error message generated by my FTP program indicating that I had not changed the file's permission on the server to be writable, so the file was not saved. I edited the settings file and saved it properly and the calendar works now. Thanks, both of you, for taking the time with me.

[petednz]

Glad you got sorted - can't see what is going on with mine but not going to dig since I don't need it - but I see that while on the 'upcoming' display I do have a result for eg Oct 6th, nothing shows on the Month Calendar.

In terms of sql


This returns a result for the upcoming

Code: [Select]

SELECT civicrm_event.title AS civicrm_event_title, civicrm_event.id AS id, civicrm_event.start_date AS civicrm_event_start_date
FROM
{civicrm_event} civicrm_event
WHERE (( (civicrm_event.is_active <> '0') AND (civicrm_event.is_public <> '0') ))
ORDER BY civicrm_event_start_date ASC
of

Civi Surgery: Monday, October 6, 2014 - 10:00
Civi Surgery: Friday, October 10, 2014 - 10:00


This does not return a result on Oct Month

Code: [Select]

SELECT civicrm_event.title AS civicrm_event_title, civicrm_event.id AS id, civicrm_event.start_date AS civicrm_event_start_date
FROM
{civicrm_event} civicrm_event
WHERE (( (civicrm_event.is_active <> '0') AND (civicrm_event.is_public <> '0') )AND( (DATE_FORMAT(ADDTIME(FROM_UNIXTIME(civicrm_event.start_date), SEC_TO_TIME(46800)), '%Y-%m') >= '2014-10' AND DATE_FORMAT(ADDTIME(FROM_UNIXTIME(civicrm_event.start_date), SEC_TO_TIME(43200)), '%Y-%m') <= '2014-10') ))
I wondered if it might be a date format issue but returned my settings to same as on drupal.demo.civicrm

[Hershel]

farmrchrys, glad you got it.

Pete, the last bit of SQL you posted does look wrong. It looks like it's looking for events that start after "13 hours before October 1st" and before "12 hours before October 1st". That's a one hour window, and it's just before October. I think. Look at formatted this way:

Code: [Select]

  DATE_FORMAT(ADDTIME(FROM_UNIXTIME(civicrm_event.start_date), SEC_TO_TIME(46800)), '%Y-%m') >= '2014-10' AND
  DATE_FORMAT(ADDTIME(FROM_UNIXTIME(civicrm_event.start_date), SEC_TO_TIME(43200)), '%Y-%m') <= '2014-10') ))
Bizarre.

[petednz]

thanks hershel but think the sql is doing the right thing

actually i just ran a hunch and cleared drupal caches - and now they show on Month as well as on Upcoming. (I also changed Date Format settings back to my preferred and recleared caches and they still show)

i don't get why the calendar display is caching when the upcoming one isn't but at least we know the code is good - though i am not arguing whether the greater/lesser than needs a tweak but at least it does pick up the right results