On Duplicate Key Update issue

I keep a statistics table that data is copied into each night with a cron job. I can run the PHP page at any time, and it should update the columns of the statistics tables if the totals from the various tables have gone up since the last time the cron was run. totalMembers is all new members that have registered for the day, totalLogins are all the logins that have happened for the day, etc. Here is my PHP code:

INSERT INTO statistics (date, members, logins, notifications, searches, referrals, favorites, payments, campaigns, testimonials, notes, matches, messages)

VALUES (‘$yesterday’, ‘$totalMembers’, ‘$totalLogins’, ‘$totalNotifications’, ‘$totalSearches’, ‘$totalReferrals’, ‘$totalFavorites’, ‘$totalPayments’, ‘$totalCampaigns’, ‘$totalTestimonials’, ‘$totalNotes’, ‘$totalMatches’, ‘$totalMessages’)

ON DUPLICATE KEY UPDATE
members = IF(members < VALUES($totalMembers), VALUES($totalMembers), members),
logins = IF(logins < VALUES($totalLogins), VALUES($totalLogins), logins),
notifications = IF(notifications < VALUES($totalNotifications), VALUES($totalNotifications), notifications),
searches = IF(searches < VALUES($totalSearches), VALUES($totalSearches), searches),
referrals = IF(referrals < VALUES($totalReferrals), VALUES($totalReferrals), referrals),
favorites = IF(favorites < VALUES($totalFavorites), VALUES($totalFavorites), favorites),
payments = IF(payments < VALUES($totalPayments), VALUES($totalPayments), payments),
campaigns = IF(campaigns < VALUES($totalCampaigns), VALUES($totalCampaigns), campaigns),
testimonials = IF(testimonials < VALUES($totalTestimonials), VALUES($totalTestimonials), testimonials),
notes = IF(notes < VALUES($totalNotes), VALUES($totalNotes), notes),
matches = IF(matches < VALUES($totalMatches), VALUES($totalMatches), matches),
messages = IF(messages < VALUES($totalMessages), VALUES($totalMessages), messages)

Here I am echoing the query, with the totals from the tables filled in, when I execute the PHP code:

[COLOR=#000000][FONT=arial]INSERT INTO statistics (date, members, logins, notifications, searches, referrals, favorites, payments, campaigns, testimonials, notes, matches, messages)

VALUES (‘2014-06-11’, ‘111’, ‘1182’, ‘757’, ‘1496’, ‘16’, ‘102’, ‘24’, ‘23’, ‘3’, ‘18’, ‘500’, ‘2’)

ON DUPLICATE KEY UPDATE
members = IF(members < VALUES(111), VALUES(111), members),
logins = IF(logins < VALUES(1182), VALUES(1182), logins),
notifications = IF(notifications < VALUES(757), VALUES(757), notifications),
searches = IF(searches < VALUES(1496), VALUES(1496), searches),
referrals = IF(referrals < VALUES(16), VALUES(16), referrals),
favorites = IF(favorites < VALUES(102), VALUES(102), favorites),
payments = IF(payments < VALUES(24), VALUES(24), payments),
campaigns = IF(campaigns < VALUES(23), VALUES(23), campaigns),
testimonials = IF(testimonials < VALUES(3), VALUES(3), testimonials),
notes = IF(notes < VALUES(18), VALUES(18), notes),
matches = IF(matches < VALUES(500), VALUES(500), matches),
messages = IF(messages < VALUES(2), VALUES(2), messages)[/FONT][/COLOR]

Here is the error that I’m getting:

Error: (1064) You have an error in your SQL syntax; check the manual that corresponds to your MySQL server version for the right syntax to use near '111), VALUES(111), members), logins = IF(logins < VALUES(1182), VALUES(1182), ’ at line 5

I had this query working fine when there were only two columns were being analyzed after the ON DUPLICATE KEY UPDATE. Now that I’ve added several more columns I can’t see where I’ve gone wrong. Any thoughts?

Thanks!

look at where it died – right at the first VALUES in the UPDATE portion

the syntax requires a column name there, check it out: http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

Thanks for pointing out my error r937. I got it working now.