0

I am creating a real estate website that has Listing ID's attached to each listing. I am running a script via shell in CakePHP that is parsing a csv file and should be updating any listing that already exists or inserting a new one if it does not.

The problem is that I keep getting a Duplicate entry '###' for key "PRIMARY' where ### is the Listing ID that is being provided by the CSV. This script is being run from the command line.

Here's a smaller version of what my table includes:

CREATE TABLE `listings` (
  `ListingID` int(11) NOT NULL,
  `AccessibilityYN` varchar(50) DEFAULT NULL COMMENT 'AccessibilityYN',
  `BathsFull` int(6) DEFAULT NULL COMMENT 'BathsFull',
  `BathsPartial` int(6) DEFAULT NULL COMMENT 'BathsPartial',
  `BathsTotal` decimal(5,1) DEFAULT NULL COMMENT 'BathsTotal',
  `Beds` int(11) DEFAULT NULL COMMENT 'Beds',
  PRIMARY KEY (`ListingID`)
) ENGINE=InnoDB DEFAULT CHARSET=latin1;

Here's my Listing Model (notice that I have public $primaryKey = 'ListingID';)

class Listing extends AppModel {
    public $name = 'Listing';
    public $primaryKey = 'ListingID'; 
    public $belongsTo = array(
        'Agent' => array(
            'className'    => 'Agent',
            'foreignKey'   => 'AgentID'
        )
    );
}

Here's the shell I'm running via command line:

class MyShell extends AppShell {

    public $uses = array('Listing');

    public function update_imported_listings() {

       /***SOME CODE HERE TO GET THE $entry FROM THE CSV***/

        $this->Listing->ListingID = $entry['ListingID'];

        if(!$this->Listing->exists()){
            $this->Listing->create();
        }

        if($this->Listing->save($entry)){
          echo "Saved Listing";
        } else {
          echo "Listing Failed";
        }
    }
}

I understand that CakePHP usually likes id to be the field used in the database, however I have set $primaryKey = 'ListingID' in my model. I've tried setting ListingID to Auto Increment in the DB but that didn't work.

Anybody have any ideas? I'm fresh out.

bigmike7801
  • 3,908
  • 9
  • 49
  • 77

3 Answers3

5

Setting ListingID doesnt' do anything

This line is your problem:

$this->Listing->ListingID = $entry['ListingID'];.

Irrespective of what your actual primary key field is in database the primary key value is always specified using Model->id property. As such, change it to: 

$this->Listing->id = $entry['ListingID'];

You don't need to call exists

There is not need to explicitly check if record with particular primary key values exists. Cake will automatically update and record instead of creating a new one if the data array passed to save() contains a valid primary key value which exists in db. Just ensure you call create() before saving if you are using save() in a loop.

AD7six
  • 63,116
  • 12
  • 91
  • 123
ADmad
  • 8,102
  • 16
  • 18
  • The problem when I do it this way though is I get the error `Column not found: 1054 Unknown column 'Listing.id' in 'where clause'` – bigmike7801 Mar 26 '13 at 15:06
  • 3
    Ensure your model file is being loaded. In the controller put a `debug(get_class($this->Listing))`, if it returns "AppModel" instead of "Listing" it means you probably named your model file incorrectly and it's not being loaded. – ADmad Mar 26 '13 at 15:09
  • It is returning `AppModel`. Am I not correctly calling the model in my shell script above by using `public $uses = array('Listing');`? Also, I have checked an my model file `LisingModel.php` has been named correctly. – bigmike7801 Mar 26 '13 at 15:15
  • OK, I'm an idiot. It should have been Listing.php and not ListingModel.php. That's what I get for jumping around from CakePHP to CodeIgniter all day. Thanks ;) Would you mind including your suggestion about checking the model file being named correctly so I can accept your answer? – bigmike7801 Mar 26 '13 at 15:18
  • It's not the first time someone made that mistake. I blame the non-intuitive naming convention for model files. Most other class types commonly created by users have the class type suffix in filename expect models. – ADmad Mar 26 '13 at 20:11
0

Don't break CakePHP conventions, use 'id' field as primary key.

http://book.cakephp.org/view/24/Model-and-Database-Conventions

Rorto
  • 1
  • 1
0

I was getting the same error running a cakePHP shell script, turns out the latest record's ID was at the maximum value allowed by the size of the column (whoever made the table used a medium int for the id column), so auto_increment wasn't working. Changed the column to allow for larger numbers to fix. Probably something obvious, but might help someone out there.

trans1t
  • 431
  • 5
  • 4