I am using cake php
I have a table called relates that holds the primary id values from the other tables.
I used cakephp to generate the crud. it works fine. if you click on the number in the relates table it goes to the appropriate table and displays the information like it should.
how can you combine the information from the other tables and display the information instead of showing the relationships primary ids?
example:
//relate table show this
Client 1
Client Address 1
Contact 1
Contact Info 1
Accounting 1
I would like to look like this
client: john smith
client: address 123 easy st
Contact: sam
Contact Info: 714-822-1111
Accounting: 500.00
I know my code for the model is not correct but when i join the tables i get no errors but the tables that i join do not display. basically i need this query to be used but i cant figure how to make it show more than one table at a time
<?php
//model file for relationship table
class Relate extends AppModel {
var $name = 'Relate';
var $primaryKey = 'relate_id';
//The Associations below have been created with all possible keys, those that are not needed can be removed
var $belongsTo = array(
'ParentRelate' => array(
'className' => 'Relate',
'foreignKey' => 'relate_id',
'conditions' => '',
'fields' => '',
'order' => ''
),
'Client' => array(
'className' => 'Client',
'foreignKey' => 'client_id',
'conditions' => '',
'fields' => '',
'order' => ''
),
'ClientAddress' => array(
'className' => 'ClientAddress',
'foreignKey' => 'client_address_id',
'conditions' => '',
'fields' => '',
'order' => ''
),
'Contact' => array(
'className' => 'Contact',
'foreignKey' => 'contact_id',
'conditions' => '',
'fields' => '',
'order' => ''
),
'ContactInfo' => array(
'className' => 'ContactInfo',
'foreignKey' => 'contact_info_id',
'conditions' => '',
'fields' => '',
'order' => ''
),
'Accounting' => array(
'className' => 'Accounting',
'foreignKey' => 'accounting_id',
'conditions' => '',
'fields' => '',
'order' => ''
),var $hasOne = array(
'ChildRelate' => array(
'className' => 'Relate',
'foreignKey' => 'relate_id',
'dependent' => false,
'conditions' => '',
'fields' => '',
'order' => ''
)
);
var $hasMany = array(
'ChildRelate' => array(
'className' => 'Relate',
'foreignKey' => 'relate_id',
'dependent' => false,
'conditions' => '',
'fields' => '',
'order' => '',
'limit' => '',
'offset' => '',
'exclusive' => '',
'finderQuery' => '',
'counterQuery' => ''
)
);
}
?>
heres the sql query. relate is the table that relates all the tables together
I know that the names are not complaint to cakephp's name scheme but it will be. I am justing looking for some direction and a format to help me out
"SELECT
agency.agency,
agency_address.agent_address,
agency.agency_id,
bond.bond_number,
bond.Surety,
bond.bond_type,
bond.paying_state,
bond.bond_state,
bond.term,
bond.cancellation_clause,
bond.bond_amt,
bond.first_issue_date,
bond.issue_date,
bond.exp_date,
bond.red_stared,
bond.red_stared_date,
bond.date_purged,
bond.bond_id,
client.client_id,
client.client_name,
agency_contact.agent_first,
agency_contact.agent_last,
agency_info.agent_phone,
agency_info.agent_fax,
agency_info.agent_email,
bond.bond_number,
bond.surety,
bond.bond_type,
bond.paying_state,
bond.bond_state,
bond.bond_amt,
bond.issue_date,
bond.exp_date,
client_address.client_address,
client_address.client_city,
client_address.client_st,
client_address.client_zip,
contact.client_first,
contact.client_middle,
contact.client_last,
contact_info.client_phone,
contact_info.client_fax,
contact_info.client_email,
obligee.obligee,
obligee.obligee_address,
obligee.obligee_city,
obligee.obligee_state
relate.client_id,
relate.client_address_id,
relate.contact_id,
relate.contact_info_id,
relate.accounting_id,
relate.obligee_id,
relate.bond_id,
relate.realte_id
FROM
agency,
bond,
client,
relate,
agency_address,
agency_contact,
agency_info,
client_address,
contact,
contact_info,
obligee
WHERE
agency.sgency_id=relate.agency_id
AND client.client_id=relate.client_id
AND bond.bond_id=relate.bond_id
AND agency_address.agency_address_id=relate.agency_address_id
AND agency_contact.agency_contact_id=relate.agency_contact_id
AND agency_info.agency_info_id=relate.agency_id
AND client_address.client_address_id=relate.client_address_id
AND contact.contact_id=relate.contact_id
AND contact_info.contact_info_id=relate.contact_info_id
AND obligee.obilgee_id=relate.obligee_id AND relate_id = %s"