Need a little guidance on some select syntax

[eluser]wan-geek[/eluser]
So I'm plugging along quite well. Thanks for the awesome framework.

I'm trying to find a way to grab some fields that seem to be causing some confusion in the select/join statements and could use a slap on the hand.

I have 2 tables:
subnet & netdesignator. Their makeup is like so:

Code:

CREATE TABLE `netdesignator`
(
        `id` INTEGER  NOT NULL AUTO_INCREMENT,
        `netdesignator` VARCHAR(32),
        `description` VARCHAR(256),
        PRIMARY KEY (`id`)
)Type=MyISAM;


CREATE TABLE `subnet`
(
        `id` INTEGER  NOT NULL AUTO_INCREMENT,
        `netdesignator_id` INTEGER,
        `subnet` VARCHAR(18),
        `description` VARCHAR(256),
        PRIMARY KEY (`id`),
        INDEX `subnet_FI_1` (`netdesignator_id`),
        CONSTRAINT `subnet_FK_1`
                FOREIGN KEY (`netdesignator_id`)
                REFERENCES `netdesignator` (`id`)
)Type=MyISAM;

So there's a column in each table that's named "description". Therein lies my problem.

In SQL, I want to do this:

Code:

select subnet,subnet.description,netdesignator from subnet LEFT JOIN netdesignator ON subnet.netdesignator_id = netdesignator.id;

OUTPUT:
+-------------------+------------------------------------+---------------+
| subnet            | description                        | netdesignator |
+-------------------+------------------------------------+---------------+
| 192.168.xxx.0/30  | SiliconHotrod P2P F/W Transit Link | internal      |
| 192.168.xxx.0/24  | SiliconHotrod LAB                  | internal      |
| 192.168.xxx.0/24  | SiliconHotrod LAN                  | internal      |
| 192.168.xxx.0/24  | SiliconHotrod Windows Systems      | internal      |
| 24.xxx.xxx.xxx/28 | SiliconHotrod WAN                  | external      |
| 192.168.xxx.0/24  | SiliconHotrod VoIP                 | internal      |
+-------------------+------------------------------------+---------------+

and return the subnet.subnet value, the subnet.description value, and the "resolved" netdesignator value... in SQL, this is easy. But how to implement it in this PHP syntax?


I have this in my controller:

Code:

function index()
        {
                $data['title'] = 'NetDisco';
                $this->db->orderby('subnet');
                $data['query'] = $this->db->get('subnet');
                $data['designators'] = $this->Subnet_Model->get_netdesignator();
                $this->load->view('subnet_view',$data);
        }

And this in my model:

Code:

<?php
class Subnet_Model extends Model {

    function Subnet_Model()
    {
        parent::Model();
    }
    function get_netdesignator()
    {
        $designators = array();
        $this->db->select('netdesignator');
        $query = $this->db->get('netdesignator');
        foreach ($query->result() as $netdesrow)
        {
                $designators[$netdesrow->netdesignator] = $netdesrow->netdesignator;
        }
                return $designators;
    }
}
?>

I've tried playing with $this->db->join and $this->db->select but keep running into the netdesignator.description field returning instead of the subnet.description field.


End result is I'm trying to build auto-filled dropdown menus that default to the already selected value when displayed.

Pardon me if I'm completely overlooking something simple, I've just been staring at this a bit too long.

Any tips/help/smacks-on-the-hand appreciated.

Cheers,
-Chris

[eluser]Armchair Samurai[/eluser]
If you're trying to duplicate your SQL statement with Active Record, I'd do it this way:

Code:

$this->db->select('x.subnet, x.description, y.netdesignator');
$this->db->join('netdesignator y', 'x.netdesignator_id = y.id', 'left');
$query = $this->db->get('subnet x');

[eluser]wan-geek[/eluser]
Thank you VERY much. That was exactly what I needed to see.

Cheers,
-Chris