Eric Eric - 2 months ago 20
MySQL Question

MySQL LOCATE function not working in an IF block

My database contains a table of speaker information (name, geography, specialty). I need to periodically update the table from a CSV file using a PHP script. The trouble I'm having is that the specialties are stored as a comma-separated list. If the table entry for the speaker already exists, I want to add the specialty from the CSV file unless that specialty is already in the list. Here's the (probably grotesque) SQL statement I came up with (being generated by my PHP script reading through the CSV file):

INSERT INTO speakers (email, last_name, first_name, specialty, country_code,
iot, imt, role, num_responses, sat_total)
VALUES ('dude@email.com', 'Last', 'First', 'Computers,', '777',
'AP', 'ASEAN', 'Big Shot', 15, 1500)
ON DUPLICATE KEY UPDATE num_responses = (num_responses + 15),
sat_total = (sat_total + 1500),
specialty = CONCAT(specialty,
(IF(LOCATE(specialty, 'Computers,') > 0, '', 'Computers,')))


My read of this is that if LOCATE finds the 'Computers,' string in the existing specialty column, it should return an empty string to the CONCAT function. However, every time I run the command, LOCATE is returning the 'Computers,' string, and so the specialty table entry string looks like 'Computers,Computers,' after the second run, 'Computers,Computers,Computers,' after the third, and so on... thank you in advance for the help.

Answer

For documentation purposes. As stated on comments, LOCATE parameters should be swapped in order to produce the desired output:

INSERT INTO speakers (email, last_name, first_name, specialty, country_code, 
  iot, imt, role, num_responses, sat_total) 
VALUES ('dude@email.com', 'Last', 'First', 'Computers,', '777', 
 'AP', 'ASEAN', 'Big Shot', 15, 1500) 
    ON DUPLICATE KEY UPDATE num_responses = (num_responses + 15), 
     sat_total = (sat_total + 1500),
  specialty = CONCAT(specialty, 
     (IF(LOCATE( 'Computers,',specialty) > 0, '', 'Computers,')))