Lexx87
asked on
Simple MySQL IF statement troubles
I'm having some trouble using an IF statement in a simple query. I've not used SQL much so i'm not really sure of the syntax!
I basically want to say IF this data does not exist in the table, then insert this other data.
I tried something like this:
SET @existCount=0; SELECT @existCount := COUNT(*) FROM user_data WHERE username = 'Lexx87'; IF @existCount = 0 THEN INSERT into user_data values ('Lexx87', '1000Hz', 10)
Meaning if the count is at 0 (No username Lexx87 exists) then insert those values, but it doesn't seem happy with that!
I've taken some of that syntax from a different thread, so it may be out of date but I don't know!
Any help will be appreciated.
I basically want to say IF this data does not exist in the table, then insert this other data.
I tried something like this:
SET @existCount=0; SELECT @existCount := COUNT(*) FROM user_data WHERE username = 'Lexx87'; IF @existCount = 0 THEN INSERT into user_data values ('Lexx87', '1000Hz', 10)
Meaning if the count is at 0 (No username Lexx87 exists) then insert those values, but it doesn't seem happy with that!
I've taken some of that syntax from a different thread, so it may be out of date but I don't know!
Any help will be appreciated.
ASKER
But what if the username does exist?
My attempt was to just test the method, as more often than not the username and other values are going to exist. So I want to say that if those values already exist (all three are unique with the username a primary) then insert something else.
My table has fields username, frequency, decibel, frequency2, decibel 2.
frequency 2 and decibel2 are null to start off with, with the frst three fields filled in with data according to a users choice. If a user does the same operation a second time, I want to say that IF values username, frequency, decibel don't exist, INSERT into user_data values ('Lexx87', '1000Hz', 10);
ELSE IF they do exist
INSERT frequency2 and decibel2 where username=Lexx87
Am I making sense? :p My problem I think is that i'm thinking too much like Java for it, but different solutions for IF statements seem to follow different syntax in different places I look so i'm a little confused I guess.
My attempt was to just test the method, as more often than not the username and other values are going to exist. So I want to say that if those values already exist (all three are unique with the username a primary) then insert something else.
My table has fields username, frequency, decibel, frequency2, decibel 2.
frequency 2 and decibel2 are null to start off with, with the frst three fields filled in with data according to a users choice. If a user does the same operation a second time, I want to say that IF values username, frequency, decibel don't exist, INSERT into user_data values ('Lexx87', '1000Hz', 10);
ELSE IF they do exist
INSERT frequency2 and decibel2 where username=Lexx87
Am I making sense? :p My problem I think is that i'm thinking too much like Java for it, but different solutions for IF statements seem to follow different syntax in different places I look so i'm a little confused I guess.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
Thank you very much for the help :-) Works a treat! Sorry to provide something so basic but the only thing i've needed to do in SQL so far is nothing more SELECT * from table ;)
Very much appreciated.
Alex
Very much appreciated.
Alex
Create a unique index (preferably a primary key) on the username column, then you can just do:
INSERT into user_data values ('Lexx87', '1000Hz', 10);
It will fail with a 'duplicate entry' error if the username allready exists. Just ignore the error, or make the server ignore it:
INSERT IGNORE into user_data values ('Lexx87', '1000Hz', 10);