mySQL Join Query in cakePHP

Question

I am new to cakePHP and i am reading the manuels and have bought the book, i have come across a issue and need some assistance.

At the moment i query my DB like this (this is the whole class) :

<?php

class TapplicantController extends AppController {

    public function index() {

        $this->set('page_title', 'Dashboard');

        $tapplicant = $this->Tapplicant->query("SELECT tapplicant.AppID, tapplicant.AppIPAddress, tapplicant.AppAffID, tapplicant.AppDate, tapplicant.FirstName, tapplicant.LastName, tapplicant.Email, tapplicant.AppDomain, toutcome.AffCommission FROM tapplicant INNER JOIN toutcome ON tapplicant.AppID=toutcome.AppID LIMIT 0,15 ");

        $this->set('tapplicant', $tapplicant );     


    }
}

and then i display it like this and it works fine :

<?php foreach ($tapplicant as $tapplicant) : ?>
        <tr>

            <td><?=$tapplicant['tapplicant']['AppID']; ?></td> 
            <td><?=$tapplicant['tapplicant']['AppAffID']; ?></td> 
            <td><?=$tapplicant['tapplicant']['FirstName']; ?></td>
            <td><?=$tapplicant['tapplicant']['LastName']; ?></td>
            <td><?=$tapplicant['tapplicant']['Email']; ?></td>
            <td><?=$tapplicant['tapplicant']['AppDate']; ?></td>
            <td><?=$tapplicant['tapplicant']['AppDomain']; ?></td>
            <td><?=$tapplicant['tapplicant']['AffCommission']; ?></td>

        </tr>
        <?php endforeach; ?>

The problem is i have Inner Joined 2 tables and its not working , i get the following error : Undefined index: AffCommission

use <td><?=$tapplicant['toutcome']['AffCommission']; ?></td> — Abhishek
– Abhishek, Commented May 20, 2015 at 12:19

drmonkeyninja · Accepted Answer · 2015-05-20 16:36:05Z

1

You shouldn't be using query() to retrieve data in CakePHP unless you really can't write the query using the Cake way. To use joins in Cake your query should look something like this:-

$tapplicant = $this->Tapplicant->find(
    'all',
    array(
        'fields' => array(
            'Tapplicant.*',
            'Toutcome.*'
        ),
        'joins' => array(
            array(
                'table' => 'toutcome',
                'alias' => 'Toutcome',
                'type' => 'INNER',
                'conditions' => array('Tapplicant.AppID = Toutcome.AppID')
            )
        ),
        'limit' => 15
    )
);

The resulting array should then be indexed by the model aliases so $tapplicant['tapplicant']['AffCommission']; should be something like $tapplicant['Toutcome']['AffCommission'];. The easiest way to debug this would be to look at what the returned array is so use either print_r($tapplicant) or debug($tapplicant) in your code to look at what's returned.

The Containable behaviour would probably make it even simpler as long as you associate your models correctly.

You're not using CakePHP naming conventions which you really should be otherwise you're going to have a lot of difficulties working with Cake. I'd recommend you read through the online docs from the start before jumping in at the deep end!

edited May 20, 2015 at 16:36

answered May 20, 2015 at 13:32

drmonkeyninja

8,5404 gold badges35 silver badges59 bronze badges

Sign up to request clarification or add additional context in comments.

2 Comments

MatHatrik Over a year ago

Hi , thanks for the answer. after using the above and then running a debug, it does not join the tables.

drmonkeyninja Over a year ago

@MaxPayneUK I've updated my answer so that it specifically includes the fields from both tables (forgot to include that before). If that doesn't fix it then you need to look at the query being generated by Cake and try running it directly against the database to see if it works. If you've not already got it installed the DebugKit plugin will help (github.com/cakephp/debug_kit/tree/2.2).

Indrajeet Singh · Accepted Answer · 2015-05-21 07:23:56Z

0

Try:
$options['joins'] = array(
    array(
        'table' => 'toutcome',
        'alias' => 'Toutcome',
        'type' => 'INNER',
        'conditions' => array('Tapplicant.AppID = Toutcome.AppID')
         )
);

$options['fields'] = array(
    Tapplicant.AppID, 
    Tapplicant.AppIPAddress, 
    Tapplicant.AppAffID, 
    Tapplicant.AppDate, 
    Tapplicant.FirstName, 
    Tapplicant.LastName, 
    Tapplicant.Email, 
    Tapplicant.AppDomain, 
    Toutcome.AffCommission
);

$options['limit'] = 15;

$tapplicant = $this->Tapplicant->find('all', $options);

answered May 21, 2015 at 7:23

Indrajeet Singh

2,99927 silver badges28 bronze badges

Collectives™ on Stack Overflow

mySQL Join Query in cakePHP

2 Answers 2

2 Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

2 Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related