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?