PHP/MySQL | insert on duplicate key run php function??
I am fairly new to mysql, so any bright ideas are happily welcomed.
I have a database with customer information from multiple sources. (email, name, address, phone, etc). The users email is the primary id. If a user already exists in the database, and they sign up from a new source, I want to run a function to evaluate which data i want to save, based on which entry is most recent, and which information is most complete.
email | fname | lname | home_phone | mobile_phone | source
firstname.lastname@example.org | bob | smith | 1234567890 | | source1
Using the above example, if bob signed up again from a new source, only this time bob gave a mobile_phone, and NO home_phone I would want to keep the existing data with the home phone and ONLY add the mobile phone.
With INSERT ON DUPLICATE KEY UPDATE i have only been able to update the information with the new entry.
I would like to be able to possibly exit the sql query and run a php function to evaluate the data, and THEN do a sql insert on duplicate key update.
I should be able to write the php function, i just don't know how to exit a sql query and call a php function.
My apologies if I rambled, and Thank you very much if anyone has any ideas.
This is AFAIK not possible (and even if it was, it would be bad design for mixing business logic into the data persistence layer).
Just try to fetch the existing user first using a standard SELECT query, if not found, do an INSERT, if found, update the fields in PHP and do an UPDATE on the database.
From my point of view, here's what I would do:
Put the existing row in an array using the email in the where clause to get the right row.
Put the new row you just received from Bob entering his details also in an array.
Compare arrays based on NULL values in either array, if one has a NULL value use the other, if both exist use the new row.
Use the array to make an update SQL statement.
If it's not clear, please let me know. I'll try and contact you or even provide some code (though I am not that good in PHP).
Edit: I just found this post via a friend. It might be a late reply, but none the less is a reply. Sorry, if this got to you too late.
|All times are GMT -5. The time now is 02:34 PM.|