Take the 2-minute tour ×

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 * FROM 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.

share|improve this question
You can do a direct, SELECT COUNT(*) ... query, rather than return all the rows and count your results. –  Anthony Forloney Nov 21 '10 at 22:09
add comment

3 Answers

up vote 1 down vote accepted

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.

share|improve this answer
Great, now could you tell me also, when I create a new entry, and I'm also adding a 4th parameter, a date, how to set a default for that 4th parameter to yesterday (the day before the entry was created)? –  ShadowX360 Nov 21 '10 at 22:21
@ShadowX360: It's simplest to do this in PHP using strtotime('yesterday') and pass the timestamp value to the database. –  casablanca Nov 21 '10 at 22:24
add comment

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...

share|improve this answer
add comment

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.

share|improve this answer
add comment

Your Answer


