MySQL If Statement troubles

Centinul · 04-23-2006, 10:45 PM

I'm trying to write an SQL script that will do the following:

CHECK to see if the user is in the user table
If the User is NOT in the user table THEN ADD the user.

I'm very new to MySQL. This is probably very simple I just can't figure it out at the moment.

Thanks.

Guttorm · 04-24-2006, 04:50 AM

Hi

First you need to make a UNIQUE index on the column that should be unique (like username). Then you can just insert, and the insert will fail if the username is already in use.

You can also use REPLACE INTO instead of INSERT INTO if you dont want an error when this happens.

Hope this helps,
Guttorm.

Centinul · 04-24-2006, 05:43 AM

This table is built into MySQL... I don't want to be adding an index to it if I don't have to. That is why I'm trying to figure out how to use the IF statement.

Guttorm · 04-24-2006, 08:03 AM

Hi again.

Ok, so you mean the MySQL users table. You should look into the GRANT command instead then to give the privileges (as few as possible). But still it makes no difference - why do you need to make this check?

If you simply grant some privileges to some user - if that user existed before, nothing will happen. Why do you need such a check?

BTW, the UNIQUE thing in the MySQL users table seems to be a combination of username and hostname.

smallville · 04-25-2006, 02:59 AM

hi! what applications are you using? just Mysql? or mysql and php? mysql and perl? or just mysql?

Centinul · 04-25-2006, 07:20 AM

Just MySQL

smallville · 04-26-2006, 01:31 AM

try this :

insert into users_table select uname from
(select case when count(aa.username_field)=0 then 'whatever_name' else '' end as uname
from
(select username_field from users_table where username_field='whatever_name') as aa) as bb
where uname<>''

but make sure that your select statement fields has the same number as your insert.