Solved

MySQL - Check before insert

Posted on 2014-11-16
18
347 Views
Last Modified: 2014-11-17
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
0
Comment
Question by:projects
  • 9
  • 5
  • 4
18 Comments
 
LVL 58

Expert Comment

by:Gary
ID: 40445830
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
 

Author Comment

by:projects
ID: 40445840
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
 
LVL 58

Expert Comment

by:Gary
ID: 40445853
ALTER TABLE htaccess ADD UNIQUE KEY user_pass (username, password)

Open in new window

0
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 
LVL 77

Expert Comment

by:arnold
ID: 40445859
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
 
LVL 77

Expert Comment

by:arnold
ID: 40445860
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
 

Author Comment

by:projects
ID: 40445862
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
 
LVL 58

Expert Comment

by:Gary
ID: 40445865
@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
 

Author Comment

by:projects
ID: 40445881
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
 
LVL 77

Accepted Solution

by:
arnold earned 250 total points
ID: 40445883
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
 
LVL 58

Assisted Solution

by:Gary
Gary earned 250 total points
ID: 40445894
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
 

Author Comment

by:projects
ID: 40446140
>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
 

Author Comment

by:projects
ID: 40446142
Interesting. I just noticed that when I ran this command, all of the username field was rearranged into alphabetical order.
0
 

Author Comment

by:projects
ID: 40448101
A little syntax change and it works fine now. Thanks.
0
 
LVL 77

Expert Comment

by:arnold
ID: 40448159
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
 

Author Comment

by:projects
ID: 40448190
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
 

Author Comment

by:projects
ID: 40448256
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
 
LVL 77

Expert Comment

by:arnold
ID: 40448269
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
 

Author Comment

by:projects
ID: 40448286
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

Featured Post

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Both Easy and Powerful How easy is PHP? http://lmgtfy.com?q=how+easy+is+php  Very easy.  It has been described as "a programming language even my grandmother can use." How powerful is PHP?  http://en.wikipedia.org/wiki/PHP  Very powerful.  But a…
Does the idea of dealing with bits scare or confuse you? Does it seem like a waste of time in an age where we all have terabytes of storage? If so, you're missing out on one of the core tools in every professional programmer's toolbox. Learn how to …
Learn several ways to interact with files and get file information from the bash shell. ls lists the contents of a directory: Using the -a flag displays hidden files: Using the -l flag formats the output in a long list: The file command gives us mor…
This tutorial gives a high-level tour of the interface of Marketo (a marketing automation tool to help businesses track and engage prospective customers and drive them to purchase). You will see the main areas including Marketing Activities, Design …

803 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question