I have 3 tables; contact, properties and property_contact (the relation table)
the relational table is there to allow me to save all the properties that a certain contact has seen so far.
This is my DB schema
DROP TABLE IF EXISTS `contacts`;
CREATE TABLE IF NOT EXISTS `contacts` (
`contactID` int(11) NOT NULL AUTO_INCREMENT,
`firstName` varchar(50) NOT NULL,
`lastName` varchar(50) NOT NULL,
`email` varchar(30) NOT NULL,
`website` varchar(30) NOT NULL,
`phone` varchar(20) NOT NULL,
`cell` varchar(20) NOT NULL,
`fax` varchar(20) NOT NULL,
`cityID` int(11) NOT NULL,
`date` datetime NOT NULL,
PRIMARY KEY (`contactID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=8 ;
DROP TABLE IF EXISTS `properties`;
CREATE TABLE IF NOT EXISTS `properties` (
`propertyID` int(11) NOT NULL AUTO_INCREMENT,
`ownerID` int(10) NOT NULL,
`propertyBedrooms` int(11) NOT NULL,
`propertyBathrooms` int(11) NOT NULL,
`propertySize` int(11) NOT NULL,
`propertyStatus` varchar(20) NOT NULL,
`propertyDescription` varchar(1000) NOT NULL,
`propertyPrice` int(11) NOT NULL,
`propertyType` varchar(30) NOT NULL,
`propertyLocation` varchar(50) NOT NULL,
PRIMARY KEY (`propertyID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;
DROP TABLE IF EXISTS `property_contact`;
CREATE TABLE IF NOT EXISTS `property_contact` (
`propertyID` int(100) NOT NULL,
`contactID` int(100) NOT NULL,
`priority` int(11) NOT NULL
) ENGINE=MyISAM DEFAULT CHARSET=latin1;
CREATE TABLE IF NOT EXISTS `city` (
`cityID` int(11) NOT NULL AUTO_INCREMENT,
`cityName` varchar(50) NOT NULL,
PRIMARY KEY (`cityID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;
CREATE TABLE IF NOT EXISTS `types` (
`typeID` int(11) NOT NULL AUTO_INCREMENT,
`typeName` varchar(30) NOT NULL,
PRIMARY KEY (`typeID`)
) ENGINE=MyISAM DEFAULT CHARSET=latin1 AUTO_INCREMENT=5 ;
And this is my code
public function contacts()
{
if (!$this->ag_auth->logged_in())
{
redirect('login');
}
$crud = new grocery_CRUD();
//$crud->set_table('contacts');
$crud->set_relation_n_n('properties', 'property_contact', 'properties', 'contactID', 'propertyID', '{propertyType} {propertyLocation}', 'priority');
$crud->display_as('cityID','City');
$crud->set_subject('contact');
$crud->set_relation('cityID','city','cityName');
//$crud->set_relation('propertyType','types','typeName');
$output = $crud->render();
$this->_example_output($output);
}
It all works perfectly, no problems there.
The problem I'm having is that the {propertyType} and {propertyLocation} in my set_relation_n_n function are being displayed as ids while the actual 'type' and 'location' of a property are being fetched from 2 other tables; city and types which have a designated name that I would like to display instead.
[attachment=304:grid.jpg]
Is there a way to get the Type and Location columns to show the actual name instead of the ID?
I hope I made sense, thank you.
