I have 3 tables: properties, contact, property_contact
$crud->set_relation_n_n('properties', 'property_contact', 'properties', 'ContactID', 'propertyID', 'propertyType', 'priority');This is the SQL 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,
`contactID` int(10) NOT NULL,
`propertyBedrooms` int(11) NOT NULL,
`propertyBathrooms` int(11) NOT NULL,
`propertyAmenities` varchar(1000) NOT NULL,
`propertySize` int(11) NOT NULL,
`propertyStatus` varchar(20) NOT NULL,
`propertyDescription` varchar(1000) NOT NULL,
`propertyPrice` int(11) NOT NULL,
`propertyType` varchar(50) 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;
The problem is I'm getting an error whenever I hit the "EDIT" button. Adding a new record works without a problem, and I checked the database, everything is being saved correctly.
This is the error
[quote]Error Number: 1052
Column 'ContactID' in where clause is ambiguous
SELECT *, propertyType as sa9026bbe FROM (`property_contact`) JOIN `properties` ON `property_contact`.`propertyID` = `properties`.`propertyID` WHERE `ContactID` = '1' ORDER BY `property_contact`.`priority`
Filename: C:\wamp\www\codeign\system\database\DB_driver.php
Line Number: 330[/quote]
I have no idea what went wrong.