MySQL - Check before insert

I am putting together a mod auth mysql setup for a web site.
Using a bash script, I enter the name and other details for the user/passwd but I need to check for two things before inserting.

This is my current command;

mysql -u test -ptest -D somedb -h 192.168.43.3 -e "insert into htaccess (username,password) VALUES ('$NAME','$PASS_CODE')"

I need a new command which does the following;

1- Check to see if that user exists
2- If user doesn't exist, then create a new record with name/password
3- If user exists, check to see if the password is the same, different or non existent
4- If non existent or different, then update
5- If the same, don't update
projectsAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

GaryCommented:
Set a unique key on the username and password together

Change your sql to
insert into htaccess (username,password) VALUES ('$NAME','$PASS_CODE') ON DUPLICATE KEY UPDATE username='$NAME';

Open in new window


The unique key will prevent multiple entries and the ON DUPLICATE... will just update the username and carry on

You can also use ON DUPLICATE KEY IGNORE - but not really recommended as it just gives a warning and doesn't report any errors that have occured
0
projectsAuthor Commented:
My fields look like this at the moment;

Field name    	Type    	        Allow nulls?    	Key    	Default value
username 	varchar(45) 	No 	                Primary 	NULL 	
password 	char(45) 	        No 	                None 	NULL

Open in new window


I don't have any 'unique key' settings. Can you tell me how to do this so I do it properly. Then I will test.

I can do it via command line webmin or phpmyadmin.
0
GaryCommented:
ALTER TABLE htaccess ADD UNIQUE KEY user_pass (username, password)

Open in new window

0
Get expert help—faster!

Need expert help—fast? Use the Help Bell for personalized assistance getting answers to your important questions.

arnoldCommented:
Gary's example is one, but he updated the wrong column

http://dev.mysql.com/doc/refman/5.0/en/insert-on-duplicate.html

modified example of Gary's code

insert into htaccess (username,password) VALUES ('$NAME','$PASS_CODE') ON DUPLICATE KEY UPDATE password='$PASS_CODE';

Open in new window


Another option is to use if not exists select username from htaccess where username='$NAME' then insert else update
depending on the variations, you can use  case statement as well. ......
0
arnoldCommented:
The update of password even if the same, is better than going through verifying whether the password is the same.
It is a simple coparison when they are both plain, if they are hashed password('password')
0
projectsAuthor Commented:
Ok, added those but I guess I need the full syntax as I am messing it up.

mysql -u me -psomepass -D somedb -h 192.168.23.43 "insert into htaccess (username,password) VALUES ('$NAME','$PASS_CODE') ON DUPLICATE KEY UPDATE username='$NAME';"
0
GaryCommented:
@arnold
If the user/pass are the same then it doesn't matter which column is updated

What error are you getting projects and did you add the unique key
0
projectsAuthor Commented:
Yes, I added the unique key but my syntax was wrong in the bash script. I had to add -e.

Anyhow, it is creating the account just fine but never updates it and there are no errors.

The user has select, insert and update access.
0
arnoldCommented:
gary,
If the duplicate is triggered, it means the username exists as it is the primary, then you would update the password, no reason to update the username.

projects,

you can stop using the -D database_name
by using the insert into database_name.table_name VALUES ('$NAME','$PASS_CODE') on DUPLICATE KEY update password='$PASS_CODE';

If your table only has two columns, the order of values is important, but the column names can be left out.
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
GaryCommented:
Yeah I need more coffee!

The unique key should be on the username only not the password as well.
Run this

ALTER TABLE htaccess DROP INDEX user_pass, ADD UNIQUE KEY username (username)
0
projectsAuthor Commented:
>you can stop using the -D database_name

I'm not sure I can. The workstation and the server are across different networks. I am not running this on the local server so how would it know which database to use?

So, I added another record, then I ran the same command to change it's password but nothing changed and no errors.

mysql -u someuser -psomepass -D somedb -h 192.168.1.40 -e "insert into htaccess (username,password) VALUES ('$LOCATION','$PASS_CODE') ON DUPLICATE KEY UPDATE username='
$LOCATION';"

Open in new window

0
projectsAuthor Commented:
Interesting. I just noticed that when I ran this command, all of the username field was rearranged into alphabetical order.
0
projectsAuthor Commented:
A little syntax change and it works fine now. Thanks.
0
arnoldCommented:
there are multiple ways to achieve what you are looking for as well as some more complicated dealing with different behaviors depending on specific values.



The check should done before you even go through modifying mysql.  
are your intent is on sending a single command, and have all the needs satisfied?
0
projectsAuthor Commented:
My original quest was to read the DB, check for conditions, then insert/update as needed.
It's always nice to have smarter over potential problems of course.
0
projectsAuthor Commented:
It seems to be working fine. The only thing I'd like to do is to get rid of an error message

Before adding the on duplicate function, the script would display;

ERROR 1062 (23000) at line 1: Duplicate entry 'TC11054' for key 'username'

Now, instead, I would like the script to say 'Updated User TC11054 password' or 'Created User TC11054' as needed. But, that's not such a big deal.
0
arnoldCommented:
The only way to achieve informational data response, is to use selects, and then inserts or with case statements or use an stored procedure

Exec perform_user_taks
Where you run selects to see whether the user exists. If the user exists check the password, if non-existent or unmatched, update and then return the message string.
0
projectsAuthor Commented:
I kind of understand that but it's a little beyond me. That's why I'm on this site looking for the actual solutions until I can hire someone to take care of all this :)
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
MySQL Server

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.