$insert_sql = "INSERT INTO exported_leads (lead_id, partner_id) VALUES ('$id','$partner_id')
ON DUPLICATE KEY UPDATE
lead_id = VALUES(lead_id), partner_id = VALUES(partner_id), export_date = CURRENT_TIMESTAMP";
$count = $pdo->exec($insert_sql);
$count_total = $count_total + $count;
i have this code. problem is that whenever there's an existing match in the db it overwrites the partner_id with a new value. i need to keep which one exported the row when. i was thinking about adding columns to cover those, but the problem with that is i dont know how many exporters there can be. sometimes it can be 5, sometimes it can be over 20.
i currently use this query to find what i should export:
$sql = $pdo->query('SELECT p.* FROM prospects p
LEFT JOIN exported_leads e
on p.id = e.lead_id WHERE p.partner_id != '.$partner_id.' AND (e.lead_id IS NULL OR datediff(now(), e.export_date) > 90)
LIMIT '.$monthly_uttag.'');
and this also shows why i cant just paste new rows in the already exported db. because it keeps finding the first record. not the newest entry first. which screws it up.
anyhow, how would you do this and still keep a date when every partner_id exported that row? say for example the row id=1. partner1 exported that row on 2014-01-01 and partner3 exported it on 2014-05-15. i need to keep both those dates somehow.
how would you do this? im looking for an indefinitely large possible amount of partners