Author Topic: CiviCRM integration with views (Read 6795 times)

sswari · April 12, 2012, 02:56:46 pm

Not sure this is the best place to do this, but I hope it helps somebody.

If you are using views to access CiviCRM tables, do not forget to modify the civicrm.view.inc file found in 'drupal_root'/sites/all/modules/civicrm/drupal/modules/views to include the

'database' => 'civicrm' line under each of the ['table']['base'] = array (

statements

Should look something like
$data['civicrm_contact']['table']['base'] = array(
'field' => 'id', // Governs the whole mozilla
'title' => t('CiviCRM Contacts'),
'help' => t("View displays CiviCRM Contacts, of people, organizations, etc."),
'database' => 'civicrm',
\ );

Good luck

Hershel · April 14, 2012, 04:35:29 pm

Actually this is not the correct way to do this. Please see our wiki page here:

http://wiki.civicrm.org/confluence/display/CRMDOC41/Views3+Integration

for instructions how to easily update settings.php instead. That is the correct and recommended method.

sswari · April 15, 2012, 07:35:37 am

I am not using View 3, does that matter?

Thanks

Hershel · April 15, 2012, 07:37:14 am

That wiki page should work for Views 2 also.

sswari · April 15, 2012, 07:50:54 am

Hershel,

I will take a much closer look at the page, but my upgrade was not working until I made those changes, even with the integration stuff.

Now just as a side note, even with the changes I noted above + the stuff from the Link you provided, I am able to get "most" of the views event stuff, but when I add any of the event date fields, the site throws a php error.

I will remove the changes and double check my settings.php file.

Just some added Info

I do have 2 seperate DBs for Drupal and Civicrm each have different user names and passwords, does that matter.

My settings.php file does not have an array instead, it is as such

require_once('dbconfig.php');

if (!isset($dbserver) || empty($dbserver))

$dbserver='localhost';

$db_url['default'] = "$dbtype://$dbuser:$dbpass@$dbserver/$dbname";

$db_url['civicrm'] = "$dbtype://$ccdbuser:$ccdbpass@$dbserver/$ccdbname";

where the DB user names and passwords are set in the dbconfig.php file

How should I change this to be in the
$databases = array (
'default' => (
array (
'default' =>
array (

format or do I even need to, just add the intigration stuff after my config info?

Thanks for the help
Sam

Coleman Watts · April 15, 2012, 11:09:02 am

I think the ideal is to have 2 dbs under the same sql user. This keeps things tidy while making it easy for Drupal to access the Civi DB. All your extra fudging may be necessary because of the separate users. Normally a db_prefix would take care of it.

Hershel · April 15, 2012, 12:20:22 pm

The method in the wiki indeed depends on your Drupal DB user having SELECT rights to the CiviCRM DB.

sswari · April 15, 2012, 02:09:24 pm

OK, so after some research, the
$databases = array (
'default' => (
array (
'default' =>
array (

format seems to be for D7 I am working with D6 so keeping the current $db_url statements.

I am aware of the single user concept, but I did not set up this site, so that is what I have.

Everything was working fine before I upgraded to civicrm 4.1.1 and the latest version of views 2.

Now, If I remove the edits from civicrm.view.inc, I have no data connection between views and CiviCRM. So something is wrong.

Is there an easy way to tell if the CiviCRM 3.x database really got updated to 4.11?

I ask, because I just discovered what might be a problem with my upgrade process.

Thanks
Sam

Hershel · April 16, 2012, 03:05:10 am

Quote from: sswari on April 15, 2012, 02:09:24 pm

OK, so after some research, the
$databases = array (
'default' => (
array (
'default' =>
array (

format seems to be for D7 I am working with D6 so keeping the current $db_url statements.

Yes, that's for D7 only, but the integration page on your site will show you the correct code for your site, Drupal 6 in this case.

Quote from: sswari on April 15, 2012, 02:09:24 pm

Now, If I remove the edits from civicrm.view.inc, I have no data connection between views and CiviCRM. So something is wrong.

Yes, as noted above, this method relies on the Drupal user having access to the CiviCRM DB

Quote from: sswari on April 15, 2012, 02:09:24 pm

Is there an easy way to tell if the CiviCRM 3.x database really got updated to 4.11?

Yes, run the upgrader again. It will tell you.

sswari · April 16, 2012, 06:48:13 am

First of all, thank you all for your help so far on this issue.

Ok, so running

http://nmsea.org/sun/civicrm/upgrade?reset=1

gives me
______
Your database has already been upgraded to CiviCRM 4.1.1

Return to CiviCRM home page.
Have you registered this site at CiviCRM.org? If not, please help strengthen the CiviCRM ecosystem by taking a few minutes to fill out the site registration form. The information collected will help us prioritize improvements, target our communications and build the community. If you have a technical role for this site, be sure to check "Keep in Touch" to receive technical updates (a low volume mailing list).
_______
in a green box, tells me the DB is really trully 4.1.1

I can access CiviCRM info through the CiviCRM menus which tells me that Drupal does indeed have access to the DB. (maybe not)

But no views integration.
What problems might I have if I change the username and password on the CiviCRM database to match the Drupal auth info?

Thanks
Sam

Hershel · April 16, 2012, 06:51:04 am

Quote from: sswari on April 16, 2012, 06:48:13 am

First of all, thank you all for your help so far on this issue.

Happy to help.

Quote from: sswari on April 16, 2012, 06:48:13 am

What problems might I have if I change the username and password on the CiviCRM database to match the Drupal auth info?

You must check on your server/host to make sure that user has access first.

But generally speaking, as long as a MySQL user has the correct access to the database, you can change the user and password whenever you want.

sswari · April 16, 2012, 07:16:43 pm

Ok,

I am not able to change the username and password on the CiviCRM database, but the Drupal user for the Drupal6 database does have select rights granted for the civicrm db.

Now, I maybe should rephrase the issue a bit.

Using views to create a view of the crm data does not work completely. I can setup the view (select and configure the fields) but no data is shown. I read a thread somewhere here in the forums about a guy that had mostly the same issue, but if he setup a drupal user view and added the crm user fields, he could get to the data that way. I tried that and no go. A drupal user view with only drupal fields works, shows data and all, as soon as I add a civicrm field, the data no longer displays.
_____ edit start
The above mentioned thread also mentioned something about using the output of the query from views to see if it returned the data, in my case, yes it did. This is logged into mysql as the drupal user and running the query.
_____edit end

also discovered that if I remove the db_url line for the civicrm db from the settings.php file, I can still see the civicrm data through the civicrm menus. This is probable due to the dsn line in the civicrm.settings.php file, is that correct? Does this give is any more help in solving this problem?

Thanks again for everybody's help
Sam

sswari · April 16, 2012, 09:12:13 pm

OK, here is a major part of the fix

/* What I had for the db_urls when this started
$db_url['default'] = "$dbtype://$dbuser:$dbpass@$dbserver/$dbname";

$db_url['civicrm'] = "$dbtype://$ccdbuser:$ccdbpass@$dbserver/$ccdbname";
*/

/* had this for a while but, it Must be also for D7 along with the new array layout for db_urls
$databases['default']['default']['prefix']= array(
Plus all of the array info
*/
/* THIS WORKS!!!!!! (mostly - Dates still throw an error see new post) Notice the coma at the end of the second definition */
$db_url = array(

"default" => "$dbtype://$dbuser:$dbpass@$dbserver/$dbname",

"civicrm" => "$dbtype://$ccdbuser:$ccdbpass@$dbserver/$ccdbname",
);

$db_prefix = array(
'default' => '',

'civicrm_acl' => '`civicrm`.',

'civicrm_acl_cache' => '`civicrm`.',

'civicrm_acl_contact_cache' => '`civicrm`.',

'civicrm_acl_entity_role' => '`civicrm`.',
...
...
...

The error on the date fields is as follows, but I will start a new thread for that.

Error Description: 500: Internal server error. Please see server or PHP logs for error information.

flug · February 17, 2013, 01:49:02 pm

FYI under Drupal 6 I ran into some problems.

I wrote some explanation of the solution here:

http://wiki.civicrm.org/confluence/display/CRMDOC43/Views3+Integration

The main point: Rather than "$databases['default']['default']['prefix']= array(", for Drupal 6 the relevant array is $db_prefix.

Donald Lobo · February 17, 2013, 04:53:57 pm

note that most (or all) of the docs / code and QA is being done on Drupal 7.

lobo

CiviCRM Community Forums (archive)

News:

Author Topic: CiviCRM integration with views (Read 6795 times)

sswari

CiviCRM integration with views

Hershel

Re: CiviCRM integration with views

sswari

Re: CiviCRM integration with views

Hershel

Re: CiviCRM integration with views

sswari

Re: CiviCRM integration with views

Coleman Watts

Re: CiviCRM integration with views

Hershel

Re: CiviCRM integration with views

sswari

Re: CiviCRM integration with views

Hershel

Re: CiviCRM integration with views

sswari

Re: CiviCRM integration with views

Hershel

Re: CiviCRM integration with views

sswari

Re: CiviCRM integration with views

sswari

Re: CiviCRM integration with views

flug

Re: CiviCRM integration with views

Donald Lobo

Re: CiviCRM integration with views