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 CiviCase (Moderator: Dave Greenberg) »
  • Odd characters showing up in civicrm_case table
Pages: [1]

Author Topic: Odd characters showing up in civicrm_case table  (Read 2076 times)

leapywca

  • Guest
Odd characters showing up in civicrm_case table
October 23, 2009, 10:51:02 am
Hi - in the civicrm_case table in our database, the column case_type_id is being auto incremented with some crazy characters. instead of just "1, 2, 3..." the values are showing up like this:(http://farm3.static.flickr.com/2447/4037120931_2bc5a7b149.jpg)

what is the square character surrounding the numbers? why is the field set to varchar(128) instead of int?

we're trying to do a join on this field and due to the odd characters can't figure out how to write it...

thanks!

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: Odd characters showing up in civicrm_case table
October 23, 2009, 11:08:38 am

a case can have multiple case types. that value is a (ctrl-A, octal 001) seperated list of case types for a case

a bit easier than adding another join table, but definitely very SQL un-friendly

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

leapywca

  • Guest
Re: Odd characters showing up in civicrm_case table
October 23, 2009, 11:39:33 am
hey Lobo - i'm kinda confused as to how you can make a case have multiple case types through the UI. would you be able to explain this?

maybe it will be easier if i explain what i'm trying to do - we are writing custom reports and we need to find the sum of participants enrolled in each case type.

also, is there a standard way to parse the list of octal 001 separated case_type_id's?

thanks again!
« Last Edit: October 23, 2009, 11:43:23 am by leapywca »

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: Odd characters showing up in civicrm_case table
October 23, 2009, 12:10:58 pm

u can not do multiple case types via the UI in 3.1 or prior. We added it since this was a valid use case from another org (which we have not yet transitioned to CiviCase)

i would do a like clause and use the seperator

SELECT count(*) FROM civicrm_case WHERE case_type_id LIKE '%caseType%';

where caseType = CRM_Core_DAO::VALUE_SEPARATOR . $caseTypeID . CRM_Core_DAO::VALUE_SEPARATOR

to get the list of caseTypes do:

               $checkedData = explode(CRM_Core_BAO_CustomOption::VALUE_SEPERATOR, substr($value,1,-1));

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

leapywca

  • Guest
Re: Odd characters showing up in civicrm_case table
October 26, 2009, 07:05:34 am
thanks again for the response.

I'm a little concerned that using LIKE won't return correct results. i.e.
SELECT count(*) FROM civicrm_case WHERE case_type_id LIKE '%1%';

will return both case_type_id=1 and case_type_id=11

we don't currently have this many case types in the system, but probably will in the future. let me know if i'm reading the sql properly and if you have any other suggestions

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: Odd characters showing up in civicrm_case table
October 26, 2009, 08:06:43 am

yes, your query will give u incorrect results, hence the query which i wrote above, encloses the case type with the separator character on each side which ensures you get the right result

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

leapywca

  • Guest
Re: Odd characters showing up in civicrm_case table
October 26, 2009, 10:21:06 am
ah i skimmed too quickly - thanks!
Pages: [1]
  • CiviCRM Community Forums (archive) »
  • Old sections (read-only, deprecated) »
  • Support »
  • Using CiviCRM »
  • Using CiviCase (Moderator: Dave Greenberg) »
  • Odd characters showing up in civicrm_case table

This forum was archived on 2017-11-26.