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

PHP programming forum. Ask questions or help people concerning PHP code. Don't understand a function? Need help implementing a class? Don't understand a class? Here is where to ask. Remember to do your homework!

Moderator: General Moderators

Post Reply
smet
Forum Newbie
Posts: 4
Joined: Mon Jun 06, 2005 12:29 pm

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

Post 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.
User avatar
Burrito
Spockulator
Posts: 4715
Joined: Wed Feb 04, 2004 8:15 pm
Location: Eden, Utah

Post by Burrito »

Code: Select all

select * from users where username = '".mysql_real_escape_string($_POST['username'])."'
User avatar
nielsene
DevNet Resident
Posts: 1834
Joined: Fri Aug 16, 2002 8:57 am
Location: Watertown, MA

Post 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
Post Reply