I'm developing a system in which I have an entity 'program' which has some 'locations' via a link table. I'm using Zend Framework with Zend_Db_Table.
location 1-----* programlocation *------1 program
In my mapper I have a function save() which should save the program:
public function save(Application_Model_Program $program)
{
try
{
//start transaction
$this->_getDbTable()->getAdapter()->beginTransaction();
//make sure category is saved (has an id)
$categoryMapper = new Application_Model_Mapper_ProgramCategory();
$categoryMapper->save($program->getCategory());
$programData = array(
'title'=>$program->getTitle(),
'slug'=>$program->getSlug(),
'description'=>$program->getDescription(),
'dateFrom'=>$program->getDateFrom()->toString(self::DB_DATE_FORMAT),
'dateTo'=>$program->getDateTo()->toString(self::DB_DATE_FORMAT),
'category'=>$program->getCategory()->getId()
);
if($program->getId() === null)
{
$newId = $this->_getDbTable()->insert($programData);
$program->setId($newId);
}
else
{
$where = $this->_getDbTable()->getAdapter()->quoteInto("id = ?", $program->getId());
$this->_getDbTable()->update($programData, $where);
}
//save locations to program
$programProgramLocationMapper = new Application_Model_Mapper_Link_ProgramProgramLocation();
$programProgramLocationMapper->saveLocationsToProgram($program->getLocations(), $program);
//commit
$this->_getDbTable()->getAdapter()->commit();
}
catch(Exception $e)
{
//rollback transaction
$this->_getDbTable()->getAdapter()->rollBack();
throw $e;
}
}
The Application_Model_Mapper_Link_ProgramProgramLocation::saveLocationsToProgram method looks like this:
public function saveLocationsToProgram(
array $locations,
Application_Model_Program $program)
{
$deleteWhere = $this->_getDbTable()->getAdapter()->quoteInto("program = ?", $program->getId());
$this->_getDbTable()->delete($deleteWhere);
foreach($locations as $location)
{
$data = array('program'=>$program->getId(), 'location'=>$location->getId());
$this->_getDbTable()->insert($data);
}
}
My problem is an exception is raised in on the last shown insert statement "Mysqli statement execute error: Duplicate entry '1-1' for key 'PRIMARY'". So it seems the delete statement before the inserts is not executed correctly. But the delete statement correctly returns the amount of entries deleted, and if I do a select after the delete the entries seem to be deleted correctly.
The same problem happens in a similar structure with tags to files.
I have no idea what's causing this and how to solve it, if any of you has it would be great as it's blocking the functionality of the system.
