[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