CODEWITHSUNDEEP
phpcakephpcakephp-3.0
Ray
Ray

Reputation: 1145

CakePHP get record from another table if the id matches

I am new to CakePHP and still learning as I make my way through each problem.

I have two tables: customers and stores. In stores table I have a foreign key called customer_id that holders customer id from customers table.

In CakePHP, I created controllers, models, and view for above tables. From CustomerController.php in view action, I am trying to get store that matches id of customer.

CustomerController.php page:

class CustomersController extends AppController
{

    public function index()
    {
        $customers = $this->Customers->find('all'); // Find all the records from the database.
        $this->set('customers', $customers);
        $stores = $this->Customers->Stores->find('all');
        $this->set('stores', $stores);        
    }

    public function view($id = NULL) 
    {
        $customer = $this->Customers->get($id); // Find a record for individual record.
        $this->set('customer', $customer);

        // $stores = $this->Customers->Stores->find('all');
        $stores = $this->Customers->Stores->find('list', [
                'keyField' => 'id',
                'valueField' => 'store_name'
            ]);        
        $this->set('store', $stores);
    }
}

SQL tables:

CREATE TABLE IF NOT EXISTS `customers` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `first_name` varchar(50) DEFAULT NULL,
  `last_name` varchar(50) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=4 ;

--
-- Dumping data for table `customers`
--

INSERT INTO `customers` (`id`, `first_name`, `last_name`) VALUES
(1, 'Ray', 'Mak'),
(2, 'John', 'Smith'),
(3, 'Mike', 'Gorge');

-- --------------------------------------------------------

--
-- Table structure for table `states`
--

CREATE TABLE IF NOT EXISTS `states` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `state_name` varchar(100) DEFAULT NULL,
  PRIMARY KEY (`id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=6 ;

--
-- Dumping data for table `states`
--

INSERT INTO `states` (`id`, `state_name`) VALUES
(1, 'TX'),
(2, 'VA'),
(3, 'WI'),
(4, 'AZ'),
(5, 'FL');

-- --------------------------------------------------------

--
-- Table structure for table `stores`
--

CREATE TABLE IF NOT EXISTS `stores` (
  `id` int(11) NOT NULL AUTO_INCREMENT,
  `customer_id` int(11) DEFAULT NULL,
  `store_name` varchar(50) DEFAULT NULL,
  `corp_name` varchar(200) DEFAULT NULL,
  `street_address` varchar(200) DEFAULT NULL,
  `city` varchar(50) DEFAULT NULL,
  `state_id` int(11) DEFAULT NULL,
  `zipcode` int(11) DEFAULT NULL,
  PRIMARY KEY (`id`),
  KEY `customers_idx` (`customer_id`),
  KEY `states_idx` (`state_id`)
) ENGINE=InnoDB  DEFAULT CHARSET=latin1 AUTO_INCREMENT=2 ;

View page:

<pre>
<?php 
print_r(json_encode($store));
print_r(h($customer));
?>
</pre>

I can use a custom SQL query with left join to get the results, but in cakephp its confusing how I can get data from another table when id matches.

Any help will be appreciated :)

Upvotes: 0

Views: 5075

Answers (1)

MotsManish
MotsManish

Reputation: 485

Ray,

You have mentioned in comments that you resolved your issue, but if you are using the below code in CustomersController :: view function, then $id does not represent stores id.

$stores = $this->Customers->Stores->find('all',[ 'conditions' => array('Stores.id' => $id)]);

In order to get stores associated with customers, you must refer to below code

$stores = $this->Customers->Stores->findByCustomerId($id);
//where $id represents customer_id from stores table

Upvotes: 3

Related Questions