CiviCRM Community Forums (archive)

*

News:

Have a question about CiviCRM?
Get it answered quickly at the new
CiviCRM Stack Exchange Q+A site

This forum was archived on 25 November 2017. Learn more.
How to get involved.
What to do if you think you've found a bug.



  • CiviCRM Community Forums (archive) »
  • Old sections (read-only, deprecated) »
  • Support »
  • Using CiviCRM »
  • Using CiviEvent (Moderator: Yashodha Chaku) »
  • SQL Select help
Pages: [1]

Author Topic: SQL Select help  (Read 1608 times)

hp3kman

  • I post occasionally
  • **
  • Posts: 74
  • Karma: 4
SQL Select help
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

  • Administrator
  • I’m (like) Lobo ;)
  • *****
  • Posts: 15963
  • Karma: 470
    • CiviCRM site
  • CiviCRM version: 4.2+
  • CMS version: Drupal 7, Joomla 2.5+
  • MySQL version: 5.5.x
  • PHP version: 5.4.x
Re: SQL Select help
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



A new CiviCRM Q&A resource needs YOUR help to get started. Visit our StackExchange proposed site, sign up and vote on 5 questions

hp3kman

  • I post occasionally
  • **
  • Posts: 74
  • Karma: 4
Re: SQL Select help
August 29, 2008, 05:16:01 pm
Excellent...thanks for the tip!

Matt2000

  • I post frequently
  • ***
  • Posts: 288
  • Karma: 27
    • http://www.ninjitsuweb.com
Re: SQL Select help
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....
Drupal/CiviCRM micro-blogging http://twitter.com/matt2000

Ninjitsu Web Development http://www.NinjitsuWeb.com/

hp3kman

  • I post occasionally
  • **
  • Posts: 74
  • Karma: 4
Re: SQL Select help
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

  • I post frequently
  • ***
  • Posts: 288
  • Karma: 27
    • http://www.ninjitsuweb.com
Re: SQL Select help
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.
Drupal/CiviCRM micro-blogging http://twitter.com/matt2000

Ninjitsu Web Development http://www.NinjitsuWeb.com/

hp3kman

  • I post occasionally
  • **
  • Posts: 74
  • Karma: 4
Re: SQL Select help
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.  :-[

Pages: [1]
  • CiviCRM Community Forums (archive) »
  • Old sections (read-only, deprecated) »
  • Support »
  • Using CiviCRM »
  • Using CiviEvent (Moderator: Yashodha Chaku) »
  • SQL Select help

This forum was archived on 2017-11-26.