Author Topic: SQL Select help (Read 1608 times)

hp3kman · August 29, 2008, 03:42:45 pm

Not sure if this is the appropriate forum to post this in, but here goes...

I'd like to modify my JCalPro Joomla component to display CiviEvent data, and I'm having trouble with selecting the data I'll need...

I'm trying to get the appropriate data from civicrm_option_group, civicrm_option_value, and civicrm_event, which I think should give me what I need, but I can't seem to wrap my head around the SELECT statement which will do it...

Any pointers....

Donald Lobo · August 29, 2008, 05:09:38 pm

Your best bet would be to find the relevant CiviCRM pages which give you the information you are looking for. Once you load the page you can check your mysql logs and extract the relevant queries. You can alternatively add:

define( 'CIVICRM_DAO_DEBUG', 1 );

to you civicrm.settings.php and it will spit out all the mysql queries on the screen.

lobo

hp3kman · August 29, 2008, 05:16:01 pm

Excellent...thanks for the tip!

Matt2000 · August 29, 2008, 08:22:12 pm

Is there a reason you're not getting the event data via the API? That would be better future-proofed against schema changes.

But if you're set on using direct SQL, the following pattern seems to work, though I make no claims about it's efficiency, and you'll need to continue it with a few more JOINs to get all the data, but...

Code: [Select]

SELECT * FROM civicrm_event e 
  LEFT JOIN civicrm_option_value v 
    ON e.event_type_id = v.value 
        AND v.option_group_id = (SELECT g.id FROM civicrm_option_group g WHERE g.name ='event_type') 
  LEFT JOIN....

hp3kman · August 30, 2008, 05:13:45 am

Matt --

Thanks for your reply...hadn't really thought about getting the data via the API...where can I read up on that???...in the Developer section?

Matt2000 · August 30, 2008, 07:19:14 am

Yup, there's the documentation, or just look at the code itself in the /api/ folder; it's pretty well organized.

Just call civicrm_initialize(), then include() the api file(s) you need, and go.

hp3kman · September 12, 2008, 03:14:58 pm

thanks for the post, Matt...clear as milk!

You make it sound so easy...been looking at the doc, but I can't seem to make much sense out of it...and I'm on Joomla, and it seems most of the example code snippets are for drupal

Can you give me a little more direction...

What I'm trying to do is modify a Joomla module to display CiviEvent data...so my module is located in modules/com_mymodule.

Your SQL will work for what I need, but I think you're right about using the api...trouble is, I'm not quite sure HOW to use it.

CiviCRM Community Forums (archive)

News:

Author Topic: SQL Select help (Read 1608 times)

hp3kman

SQL Select help

Donald Lobo

Re: SQL Select help

hp3kman

Re: SQL Select help

Matt2000

Re: SQL Select help

hp3kman

Re: SQL Select help

Matt2000

Re: SQL Select help

hp3kman

Re: SQL Select help