MySQL LOCATE function not working in an IF block

Question

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.

I highly doubt you want to program like this: create table abc ( specialty varchar(100) not null ); insert abc(specialty) values ('this,computers,a'),('computers'),('d,computers'); select specialty,CONCAT(specialty,(IF(LOCATE(specialty, 'Computers,') > 0, '', 'Computers,'))) from abc; — Drew, Oct 04 '16 at 21:50
I suggest you look at [Junction Tables](http://stackoverflow.com/a/32620163) and code it for performance and sanity — Drew, Oct 04 '16 at 21:52
@Jayvee, you're correct, thank you. Juggling the needle/haystack orders for PHP, JS, and MySQL has fried my brain. — Eric, Oct 04 '16 at 22:11
@Drew, you're correct too, upon further reflection I will repent and normalize my data. — Eric, Oct 04 '16 at 22:11

score 1 · Accepted Answer · answered Oct 04 '16 at 22:21

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,')))

MySQL LOCATE function not working in an IF block

1 Answers1