Jump to content


Photo

SQL query to retrieve contact info and custom field data

database tables sql query

  • Please log in to reply
1 reply to this topic

#1 daveturner101

daveturner101

    Newbie

  • New Members
  • Pip
  • 3 posts

Posted 07 August 2013 - 11:54 AM

Hi,

 

I'm writing a php script that performs a custom query on the zurmo database.

 

I'm using the following query to join a few of the zurmo tables together so that I can get some data out of each of them:

 

$result_zurmo_three_month = mysqli_query($con_zurmo,"SELECT *
  FROM contact 
  LEFT JOIN person 
  ON contact.person_id = person.id
  LEFT JOIN email
  ON person.primaryemail_email_id = email.id
  LEFT JOIN address
  ON person.primaryaddress_address_id = address.id
  LEFT JOIN customfield
  ON contact.subscstm_ownedcustomfield_id = customfield.id
  WHERE customfield.value LIKE '%3-month%'
  ORDER BY postalcode DESC
  ");

 

I'm having some trouble accessing the 'Title' for each contact. (Eg Mr, Mrs, Ms...). These are stored in the customfield table. I'm able to get at the ID number of the entry in the person table but i cant link this up with it's corresponding 'value' in the customfield table.

 

I get the id number of the title with this: 

 

$row['title_ownedcustomfield_id']

 

Full code to generate each line of info:

 

while($row = mysqli_fetch_array($result_zurmo_three_month)) {

$grabSubStart = $row['subsstartcstm'];
if ($grabSubStart == '2013-03-01') {
  $subStart = 'No 1 - April 2013 (Available 1st March)';
};


  $three_month_zurmo_csv_line_full = 
  $row['title_ownedcustomfield_id'] . "; "
  . $row['firstname'] . "; "
  . $row['lastname'] . "; "
  . $row['street1'] . "; "
  . $row['street2'] . "; "
  . $row['state'] . "; "
  . $row['postalcode'] . "; "
  . $row['value'] . " sub; "
  . $subStart . "; " 
  . $row['emailaddress'] . "; ";
  

  $three_month_zurmo_csv_email = $row['emailaddress'];

  if(!in_array($three_month_zurmo_csv_email, $sub_csv_email_array)) {
    if (!in_array($three_month_zurmo_csv_email, $three_month_zurmo_csv_email_array)) {
      if (!in_array($three_month_zurmo_csv_email, $three_month_csv_email_array)) {
        echo $three_month_zurmo_csv_line_full;
        echo "<br>";
        array_push($three_month_zurmo_csv_full_array, $three_month_zurmo_csv_line_full);
        array_push($three_month_zurmo_csv_email_array, $three_month_zurmo_csv_email);
        $result_counter++;
      };
    };
  };


};

 

 

Am i joining the tables up correctly? Anyone any ideas?

 

Many thanks...

 

Dave 



#2 windsor

windsor

    Advanced Member

  • Moderators
  • 272 posts
  • LocationTampa, Florida USA

Posted 07 August 2013 - 01:47 PM

Think the Title, like "Mr." , "Mrs." , etc is in customfielddata table in the serializeddata column. It's kind of a modified json format. which you will need to parse.

dont see that you are joining to that table your query.

 

Does it have to be sql? Zurmo has a serialize() function in RedBeanModel  you might try to create the contact object, then serialize, then parse the json string.

Of course this doesn't help much if you need sql for other reasons, but since the custom fields are serialized data and you need to parse anyway...

 

Yet another option using the built in reporting feature of zurmo, you can create a report and have it generate the sql that you need.







Also tagged with one or more of these keywords: database, tables, sql, query

0 user(s) are reading this topic

0 members, 0 guests, 0 anonymous users