Forums - Thread Details
  • State Not Answered
  • Replies 2 replies
  • Subscribers 149 subscribers
  • Views 1977 views
  • Users 0 members are here
Related

MySQL error 1062: Duplicate entry ' ' for key 'PRIMARY'

Steven Heath
over 1 year ago

I am getting a MySQL error regarding a duplicate entry when using the following code. 

<?php

array_push($job_strings, 'list_refresh');
function list_refresh()

// This function adds any new contacts on present day into the target list, runs daily.
{
$db = DBManagerFactory::GetInstance();
$date = date('Y-m-d');
// Time for Date Modified Parameter insertion
$time = date('Y-m-d h:i:s a');
// Query to pull contacts only from today.
$contactquery = "SELECT id FROM contacts WHERE date_entered >= '$date'";
$results = $GLOBALS['db']->query($contactquery);
foreach ($results as $result){
$contactid = strval($result['id']);

// Loop through accounts module to get associative id. 
$accountquery = "SELECT account_id FROM accounts_contacts WHERE contact_id = '$contactid'";
$secondresults = $GLOBALS['db']->query($accountquery);
// Loop through Prospects List module to get final variable prepared.
foreach ($secondresults as $secondresult) {
$listquery = "SELECT prospect_list_id FROM prospect_lists_prospects WHERE related_id = '{$secondresult['account_id']}'";
$thirdresults = $GLOBALS['db']->query($listquery);
// Use all of the Selected Data to create new entry in target list.
// Execute results.
foreach ($thirdresults as $thirdresult) {
$createquery = "INSERT INTO prospect_lists_prospects (prospect_list_id, related_id, related_type, date_modified) VALUES ('{$thirdresult['prospect_list_id']}', '$contactid', 'contacts', '$time')";
$GLOBALS['db']->query($createquery);
}
}
}




//return true for completed
return true;
}

The error happens when the script gets to the Insert INTO execution.

I was under the impression that Sugar would auto assign the id for the primary key, but instead it is creating a blank column id. (I'm not inserting any data into the id field, only the other id fields.) What can I do to resolve this? Do I need to create a random id myself or is there some other method?

  • 0 over 1 year ago

    You're using insert into, but you're not setting an ID.  As the prospect_list_id is put in first, you're trying to set the prospect_list_id as the ID of the prospect_list_prospects table.  Do that more than once, and you'll get an error.  Also, you should be using the bean, where possible.

    DBManager should be used when using the bean isn't feasible.  

    You should load the prospect list bean, and the contact bean, and then relate the two.

    $targetlist = BeanFactory::getBean('ProspectLists', $prospect_list_id);
    $targetlist->load_relationship('contacts');
    $targetlist->contacts->add($contact_id);
  • 0 over 1 year ago in reply to Ken McCartney

    Thanks, I will try this method on our test environment. I had it resolved by giving a pseudo ID made up by time and an auto-increment in the same character limit and style format, but would definitely prefer best practice. Still new to sugar development, I will do some more reading up on beans.