Jump to content


daveturner101

Member Since 07 Dec 2012
Offline Last Active Aug 07 2013 11:54 AM
-----

#7857 SQL query to retrieve contact info and custom field data

Posted by daveturner101 on 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