Unique Value in MySQL Database

I'm trying to create a MySQL database that will hold all of my users and logins.

The table columns are: User_id passkey points

The user_id of each user has to be unique, and same with their passkey. Points is just an integer value that I will edit from time to time. Now, when I get a user to my site, I have to check if he is a new user or an old one. I check by seeing if their user_id is in the database already, and if not, I create a new entry to the table that inputs their user_id, passkey, and 0 for points. So, when I create the table, do I still have to specify unique for the user_id and passkey, even though I'll be checking first before creating new entries?

And how would I check if the user_id is already in the system? I'm thinking something like: SELECT * 开发者_如何学CFROM customers WHERE user_id='test'

And then count the rows, and if it is zero, I create a new entry, right? I'm trying to make sure I get everything right before I run my code. Thanks.

It's a good idea to anyway mark the fields as UNIQUE, because in the case you miss something in your code, the DB will still catch the error before things break down. I'm not sure why you want the passkey to be unique though, is there anything wrong with two users having the same passkey?

Your query is fine, but I guess you don't really need the actual user details when checking, so you can just ask for the count:

SELECT COUNT(*) FROM customers WHERE user_id='test'

and if the returned value is > 0, the user_id already exists.

You should specify unique and auto increment so that you just fill in NULL and it does it for you.

However, I would worry that you should have usernames and passkeys...

First set the passkey field default to 0. Then for the user_id make it auto_increment. This will go up by one each time a new row is added (e.g. new user). You will not need to check if the user_id is in the system when you insert to the database.

When inserting you just need to specify the passkey, presumably this is the user's password. All of the field editing can be done in phpMyAdmin, under Structure for your tabel.