Results 1 to 2 of 2
  1. #1
    Join Date
    Apr 2004
    Location
    Utah
    Posts
    2

    Unanswered: Trouble with multiple JOIN

    Here's the mysql select statement:
    Code:
    SELECT sales_leads.id, sales_leads.product_id, products.product_name, products.highlight_color 
      AS product_highlight_color, sales_leads.disposition_id, dispositions.name 
      AS disposition_name, sales_leads.subdisposition_id, subdispositions.name 
      AS subdisposition_name, sales_leads.lead_source_id, companies.company_name 
      AS lead_source_name, sales_leads.lead_campaign_id, lead_campaigns.campaign_name 
      AS lead_campaign_name, sales_leads.lead_file_id, sales_leads.lead_quality, sales_leads.lead_opt_in_level, 
      	sales_leads.contact_name_prefix, sales_leads.contact_name_first, sales_leads.contact_name_middle, 
    	sales_leads.contact_name_last, sales_leads.contact_name_suffix, sales_leads.contact_name_full, 
    	sales_leads.contact_name_last_first, sales_leads.contact_company, sales_leads.contact_po_box, 
    	sales_leads.contact_street_line1, sales_leads.contact_street_line2, sales_leads.contact_street_line3, 
    	sales_leads.contact_city, sales_leads.contact_state_province, sales_leads.contact_postal_code, 
    	sales_leads.contact_county, sales_leads.contact_country_code, sales_leads.contact_phone_number1, 
    	sales_leads.contact_phone_number2, sales_leads.contact_fax_number, sales_leads.contact_email, 
    	sales_leads.contact_longitude, sales_leads.contact_latitude, sales_leads.contact_time_zone, 
    	sales_leads.contact_notes, sales_leads.contact_gender, sales_leads.contact_marital_status, 
    	sales_leads.contact_birthdate, sales_leads.best_time_to_call, sales_leads.assigned_to, 
    	employees.employee_name_last_first 
      AS assigned_to_name, sales_leads.assigned_on, sales_leads.callback_date, sales_leads.callback_time, 
      	sales_leads.do_not_phone, sales_leads.do_not_fax, sales_leads.do_not_email, sales_leads.do_not_postal_mail, 
      	sales_leads.do_not_bulk_email, sales_leads.do_not_bulk_postal_mail, sales_leads.disposition_count, 
    	sales_leads.updated_by, sales_leads.updated_on, sales_leads.created_by, sales_leads.created_on 
      FROM ((((((sales_leads 
      	JOIN products ON ((products.id = sales_leads.product_id))) 
    	JOIN dispositions ON ((dispositions.id = sales_leads.disposition_id))) 
    	JOIN subdispositions ON ((subdispositions.id = sales_leads.subdisposition_id))) 
    	LEFT JOIN companies ON ((companies.id = sales_leads.lead_source_id))) 
    	LEFT JOIN lead_campaigns ON ((lead_campaigns.id = sales_leads.lead_campaign_id))) 
    	LEFT JOIN employees ON ((employees.id = sales_leads.assigned_to)))
    ...after I run the query I want to run the following php ($obj is the array resulting from fetching from the query):
    Code:
    if( isset( $obj->id )) $this->setId( $obj->id );
            if( isset( $obj->product_id )) $this->setProductId( $obj->product_id );
            if( isset( $obj->disposition_id )) $this->setDispositionId( $obj->disposition_id );
            if( isset( $obj->subdisposition_id )) $this->setSubdispositionId( $obj->subdisposition_id );
            if( isset( $obj->lead_source_id )) $this->setLeadSourceId( $obj->lead_source_id );
            if( isset( $obj->lead_campaign_id )) $this->setLeadCampaignId( $obj->lead_campaign_id );
            if( isset( $obj->lead_file_id )) $this->setLeadFileId( $obj->lead_file_id );
            if( isset( $obj->lead_quality )) $this->setLeadQuality( $obj->lead_quality );
            if( isset( $obj->lead_opt_in_level )) $this->setLeadOptInLevel( $obj->lead_opt_in_level );
            if( isset( $obj->contact_name_prefix )) $this->setContactNamePrefix( stripslashes( $obj->contact_name_prefix ));
            if( isset( $obj->contact_name_first )) $this->setContactNameFirst( stripslashes( $obj->contact_name_first ));
            if( isset( $obj->contact_name_middle )) $this->setContactNameMiddle( stripslashes( $obj->contact_name_middle ));
            if( isset( $obj->contact_name_last )) $this->setContactNameLast( stripslashes( $obj->contact_name_last ));
            if( isset( $obj->contact_name_suffix )) $this->setContactNameSuffix( stripslashes( $obj->contact_name_suffix ));
            if( isset( $obj->contact_company )) $this->setContactCompany( stripslashes( $obj->contact_company ));
            if( isset( $obj->contact_po_box )) $this->setContactPoBox( stripslashes( $obj->contact_po_box ));
            if( isset( $obj->contact_street_line1 )) $this->setContactStreetLine1( stripslashes( $obj->contact_street_line1 ));
            if( isset( $obj->contact_street_line2 )) $this->setContactStreetLine2( stripslashes( $obj->contact_street_line2 ));
            if( isset( $obj->contact_street_line3 )) $this->setContactStreetLine3( stripslashes( $obj->contact_street_line3 ));
            if( isset( $obj->contact_city )) $this->setContactCity( stripslashes( $obj->contact_city ));
            if( isset( $obj->contact_state_province )) $this->setContactStateProvince( stripslashes( $obj->contact_state_province ));
            if( isset( $obj->contact_postal_code )) $this->setContactPostalCode( stripslashes( $obj->contact_postal_code ));
            if( isset( $obj->contact_county )) $this->setContactCounty( stripslashes( $obj->contact_county ));
            if( isset( $obj->contact_country_code )) $this->setContactCountryCode( stripslashes( $obj->contact_country_code ));
            if( isset( $obj->contact_phone_number1 )) $this->setContactPhoneNumber1( stripslashes( $obj->contact_phone_number1 ));
            if( isset( $obj->contact_phone_number2 )) $this->setContactPhoneNumber2( stripslashes( $obj->contact_phone_number2 ));
            if( isset( $obj->contact_fax_number )) $this->setContactFaxNumber( stripslashes( $obj->contact_fax_number ));
            if( isset( $obj->contact_email )) $this->setContactEmail( stripslashes( $obj->contact_email ));
            if( isset( $obj->contact_longitude )) $this->setContactLongitude( $obj->contact_longitude );
            if( isset( $obj->contact_latitude )) $this->setContactLatitude( $obj->contact_latitude );
            if( isset( $obj->contact_time_zone )) $this->setContactTimeZone( $obj->contact_time_zone );
            if( isset( $obj->contact_notes )) $this->setContactNotes( stripslashes( $obj->contact_notes ));
            if( isset( $obj->contact_gender )) $this->setContactGender( stripslashes( $obj->contact_gender ));
            if( isset( $obj->contact_marital_status )) $this->setContactMaritalStatus( stripslashes( $obj->contact_marital_status ));
            if( isset( $obj->contact_birthdate )) $this->setContactBirthdate( $obj->contact_birthdate );
            if( isset( $obj->best_time_to_call )) $this->setBestTimeToCall( $obj->best_time_to_call );
            if( isset( $obj->assigned_to )) $this->setAssignedTo( $obj->assigned_to );
            if( isset( $obj->assigned_on )) $this->setAssignedOn( $obj->assigned_on );
            if( isset( $obj->callback_date )) $this->setCallbackDate( $obj->callback_date );
            if( isset( $obj->callback_time )) $this->setCallbackTime( $obj->callback_time );
            if( isset( $obj->do_not_phone )) $this->setDoNotPhone( $obj->do_not_phone );
            if( isset( $obj->do_not_fax )) $this->setDoNotFax( $obj->do_not_fax );
            if( isset( $obj->do_not_email )) $this->setDoNotEmail( $obj->do_not_email );
            if( isset( $obj->do_not_postal_mail )) $this->setDoNotPostalMail( $obj->do_not_postal_mail );
            if( isset( $obj->do_not_bulk_email )) $this->setDoNotBulkEmail( $obj->do_not_bulk_email );
            if( isset( $obj->do_not_bulk_postal_mail )) $this->setDoNotBulkPostalMail( $obj->do_not_bulk_postal_mail );
            if( isset( $obj->disposition_count )) $this->setDispositionCount( $obj->disposition_count );
            if( isset( $obj->updated_by )) $this->setUpdatedBy( $obj->updated_by );
            if( isset( $obj->updated_on )) $this->setUpdatedOn( $obj->updated_on );
            if( isset( $obj->created_by )) $this->setCreatedBy( $obj->created_by );
            if( isset( $obj->created_on )) $this->setCreatedOn( $obj->created_on );
            if( isset( $obj->previously_assigned_to )) $this->setPreviouslyAssignedTo( $obj->previously_assigned_to );
            if( isset( $obj->last_call_attempt_on )) $this->setLastCallAttemptOn ( $obj->last_call_attempt_on );
            if( isset( $obj->lead_origination )) $this->setLeadOrigination ( $obj->lead_origination );
            if( isset( $obj->cost_per_lead )) $this->setCostPerLead ( $obj->cost_per_lead );
            if( isset( $obj->product_name )) $this->setProductName( stripslashes( $obj->product_name ));
            if( isset( $obj->product_highlight_color )) $this->setProductHighlightColor( stripslashes( $obj->product_highlight_color ));
            if( isset( $obj->disposition_name )) $this->setDispositionName( stripslashes( $obj->disposition_name ));
            if( isset( $obj->subdisposition_name )) $this->setSubdispositionName( stripslashes( $obj->subdisposition_name ));
            if( isset( $obj->lead_source_name )) $this->setLeadSourceName( stripslashes( $obj->lead_source_name ));
            if( isset( $obj->lead_campaign_name )) $this->setLeadCampaignName( stripslashes( $obj->lead_campaign_name ));
            if( isset( $obj->contact_name_full )) $this->setContactNameFull( stripslashes( $obj->contact_name_full ));
            if( isset( $obj->contact_name_last_first )) $this->setContactNameLastFirst( stripslashes( $obj->contact_name_last_first ));
            if( isset( $obj->assigned_to_name )) $this->setAssignedToName( stripslashes( $obj->assigned_to_name ));
            if( isset( $obj->lead_origination )) $this->setLeadOrigination( $obj->lead_origination);
            if( isset( $obj->cost_per_lead )) $this->setCostPerLead($obj->cost_per_lead);
    Only problem is it's not returning the sales leads...

  2. #2
    Join Date
    Apr 2004
    Location
    Utah
    Posts
    2
    OK, I changed the FROM part of my query to the following:
    Code:
    FROM ((((((sales_leads 
      	LEFT JOIN products ON ((products.id = sales_leads.product_id))) 
    	LEFT JOIN dispositions ON ((dispositions.id = sales_leads.disposition_id))) 
    	LEFT JOIN subdispositions ON ((subdispositions.id = sales_leads.subdisposition_id))) 
    	LEFT JOIN companies ON ((companies.id = sales_leads.lead_source_id))) 
    	LEFT JOIN lead_campaigns ON ((lead_campaigns.id = sales_leads.lead_campaign_id))) 
    	LEFT JOIN employees ON ((employees.id = sales_leads.assigned_to)))
    The result set has all the values I need but some of secondary code above isn't working.

    Example:
    Code:
    if( isset( $obj->disposition_name )) $this->setDispositionName( stripslashes( $obj->disposition_name ));
    doesn't work because obj->disposition name isn't getting set. I'll have to look at my fetchObject() function again.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •