Page 1 of 1

SQL question.....NEED HELP ASAP!!!!!

Posted: Wed Jun 22, 2005 11:56 am
by smet
i'd like to know how to write the code to check a database to see if a specific value has already been entered. example....i'd like to check to see if a person's username has been entered into the database.

Posted: Wed Jun 22, 2005 11:58 am
by Burrito

Code: Select all

select * from users where username = '".mysql_real_escape_string($_POST['username'])."'

Posted: Wed Jun 22, 2005 12:14 pm
by nielsene
I'd suggest changing that to either:

Code: Select all

select count(*) from users where username = '".mysql_real_escape_string($_POST['username'])."'
or

Code: Select all

select username from users where username = '".mysql_real_escape_string($_POST['username'])."'
or

Code: Select all

select username, ... from users where username = '".mysql_real_escape_string($_POST['username'])."'
* lists are very brittle in SQL queries and if your schema changes, you can have problemss. In this case, however all you care about is whether there is a row or not.

The SELECT usernamee FROM users ... version is probably the "best" solution in this case. It allows you to use the num_rows() type functions to check for the presence of the match, without needing to actually look at the data.

The count(*) version is probably my preferenced version, but it does mean you have to fetch the row to check for success as there will always be a row.

Both of these version show a very good "intent" of the query, you can tell from the SQL what its used for -- checking existence. Thus if someone comes along to change the query, they are unlikely to break it. -- expeciall importantt if the query gets isolated from the code (either in a phrasebook or in various OOP breakups).

the SELECT username, ... FROM version is useful if you're trying to get more use at a smaller number of queries such as if you're trying to keep a short "PhraseBook" of queries. This method loses a little bit of the "intent" but it is useful in a variety of places and might make a good candidate for a seperate functional query, etc