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"

Recommended Answers

All 5 Replies

Member Avatar for P0lT10n

Very nice code, but it's too much for me... i am tire i mean :P

i think its something to do with my view page perhaps pagnation

what I understand is that you need to display the exact information from relating tables. I did it modifying my view after generating with cake bake all funtions in the console.
you have to modify it like my states table was linked with event for in which state the event is going to be occurred.

$states = $this->Event->State->find('list',array('fields' => array('State.id','State.state')));

the array of fields will give you the information you pass as I did States.name.

thats what i did and it works thanks for your help

Member Avatar for P0lT10n

mark this as solved...

Be a part of the DaniWeb community

We're a friendly, industry-focused community of developers, IT pros, digital marketers, and technology enthusiasts meeting, networking, learning, and sharing knowledge.