MySQL IF Else Statement : What is the proper syntax?

I’d like to send MySQL an if/else statement to avoid doing multiple queries but I’m having a little trouble getting the syntax right.

I have a dictionary of words I’m learning. When adding a new word, I need to get the id of that word to use in another table. If the word doesn’t exist it can be inserted and I can use mysql_insert_id to get the newly generated id. If the word does exist mysql_insert_id won’t be correct, so I’ll instead need to just return the value.

I tried a simple example without much luck.


            IF (SELECT * FROM dictionary)
                BEGIN
                    SELECT * FROM dictionary
                END
            ELSE
                BEGIN
                    (SELECT * FROM dictionary)
                END
            END IF;

Does anyone know how I can get this query working?

You’ve probably checked it out but just for reference purposes here are the MySQL resources on the IF Statement:

http://dev.mysql.com/doc/refman/5.0/en/if-statement.html

and the IF() function:

http://dev.mysql.com/doc/refman/5.0/en/control-flow-functions.html#function_if

I’d personally recommend using PHP to program this logic (mainly because I’m so used to it). Is this an option for you?

which happens more often: the work already exists, or the word will be added?

I’d personally recommend using PHP to program this logic (mainly because I’m so used to it). Is this an option for you?
Yes this is an option for the short term as this is currently a personal project so I can spare the extra resources. But I fully intend to place everything online so there will (hopefully) be a lot of users which makes the extra resource usage harder to justify; so I’d rather just do it now then later.

And yes I looked at the MySQL manual, but their examples are fairly weak. I keep getting an error starting at the “If” portion of the statement.

which happens more often: the work already exists, or the word will be added?
This is a good question, and the answer is both.

In the beginning the word will usually be missing. As I add more and more vocabulary this will happen less often. I do plan to let other’s use the system in the future so I really have no idea what words they’ll want to learn, so the possibility of a missing word is always going to be present.

my suggestion is to create a UNIQUE index on the word column. when someone wants to add a word, do the INSERT without first checking to see if the word exists. if you get a duplicate value error, then do the select to get the id.

another alternative is to just use the word as the primary key and get rid of the id column altogether.

If the word is supposed to be unique then it ought to be the primary key. You should only add an id field to tables where there is no unique field to use for the primary key. With a properly normalised database having a table that doesn’t have a real field to use for the primary key should be far less common than having such a field.

OK, I realize I didn’t explain everything clearly. So let me clear it up now.

I have a unique field

It’s a combination of the word, the part of speech, and the language it belongs to. This is the primary identification used to ensure uniqueness, and yes it’s a unique field.

When I add a word I’m also adding other information besides the word, which is going in another table. To link the two entries together I need to take the words unique id and insert it into the other table (A foreign key).

So the problem remains, if the unique key isn’t there I need to do an INSERT, then retrieve the unique id to place into the other table. If it is there, I just need to retrieve it for use in the other table. EIther way I’m looking at two queries, and I think it could be reduced to one if I can get the if/else statement working properly.

my suggestion is to create a UNIQUE index on the word column. when someone wants to add a word, do the INSERT without first checking to see if the word exists. if you get a duplicate value error, then do the select to get the id.
This is what I’m going to do for now so I can move ahead to other functionality. I’ll go back and do it properly later.

Also, I tested two different queries.

"IF " produced “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 ‘IF’ at line 1”

"Select " produced “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 ‘’ at line 1”.

The error reported for 'SELECT ’ was the blank space after the keyword ‘SELECT’, while 'IF ’ reported an error right at the keyword at ‘IF’. Even in the example in the manual ‘IF’ is the first keyword, so I’m clueless as to why ‘IF’ would be throwing an error.

well said, stephen

:slight_smile:

sorry, if/else doesn’t work like that. it won’t execute one statement or the other; it only calculates a scalar value.

even if your IF worked the way you wanted, it would still execute two queries

sorry, if/else doesn’t work like that. it won’t execute one statement or the other; it only calculates a scalar value.
Oh, I was thinking a return value would suffice. Maybe something like EXISTS or NOT EXISTS.

even if your IF worked the way you wanted, it would still execute two queries

I would have thought it would have executed the query in memory since the information would have already (mostly) been there, and it would have saved a database connection.

Anyways, I’ve already done it in PHP so I’ll just leave it as it is I suppose. Thanks for the help.