I have a mysql table to hold my members. I want to prevent people from writing to the database using the same email address. so im thinking my php code would be something along this…
$emailcheck = "select email from users where email = ".$email."";
if (mysql_num_rows != 1) {
echo "error";
} else {
//insert statement
}
Both of the above. You want to have a function to be able to return a value of true or false for account creation, and perhaps down the road an ajax function to check before submission?
select before insert is double handling unless you need to use the values rethred from the select before doing the insert - simply specifying fields in the database that need to be unique as unique will allow you to detect that the entry already exists when you try to insert it without having to select it first.
I would think the small amount of overhead created from a quick select on an indexed column, and a non-common feature such as registration is acceptable for a feature that can be used to save the user time, as well as page requests (speaking of an ajax request of course)
Doing a select first and then an insert means that all the entries which are NOT duplicates have two database accesses while the rare duplication has one.
Doing an insert with a duplicate key check means that all the non-duplicates have just the one database access needed and only the rare duplicate might need to do a second call to select the associated data from the existing record.
Assuming that 1% of entries result in a duplicate then for 1000 people using the select would require 1990 database calls while usingjust the insert with a duplicate key check followed by a select on the duplicates woul require 1010 database calls - a significant saving (which would be even higher when you consider that far fewer than 1% are likely to be duplicates).
Only if you expect duplicate errors over half the time does doing the select first make sense.
And if you put a unique index on the column, MySQL takes care of not inserting duplicates. If you don’t then you have to handle that in your code, and if you forget or make a mistake, your table will contain duplicate values. Why would you want to do stuff yourself when you can “delegate” it?
I was stating both, not just through server side code. I don’t see anything wrong with using the SQL to return a user taken message, but I’m suggesting that you create a function that does only the check for later use.