如何检查表中是否已存在值？

Question

I have been struggling with a "check if e-mail exists-problem" in my database. The whole function is about changing your e-mail address for your account.

I want to check if the e-mail address already exists in my database. I guess you'll know what I'm after if you just check my code:

if($_POST["changeemail2"]=="Send request")
{
$newemail=$_POST["newmail"];
$sql="SELECT email FROM tbluser";
$result=$objMysql->query($sql);

while($dbmails = mysql_fetch_assoc($result))
{
    if(in_array($newmail, $dbmails) && empty($_SESSION["emailerror"]))
    {
    $_SESSION["emailerror"]="That e-mail address<br />is already in use.";
    }
}


if(empty($newemail))
{
    $_SESSION["emailerror"]="You didn't select<br />a new e-mail address";
}


if($_SESSION["emailerror"]=="")
{
    $oldemail=$_SESSION["user"]["email"];
    if($objMysql->sendchangeEmail($oldemail, $newemail))
    {
        $_SESSION["emailsuccess"]="E-mail verification has been sent to your current e-mail.";
        $code="success";
    }
}

}

I have tried many different aspects but my brain is just not working right now.

Answer 1

Use a unique constraint. Try to insert/update the record and catch the exception thrown when the unique constraint is violated. This is the only way to guarantee an unique e-mail adresses; first checking and then updating is prone to concurrency issues since it is possible that someone else updates his/her record to the same value you're trying to set just after you did the check and before updating the record.

Also: learn how to use where-clauses. You're now retrieving ALL records, iterating over them etc. which takes unecessarily much resources, takes too long and is just a plain waste. If you want to check for a record matching a criteria you write:

Select foo, bar from table where baz = 123

Where baz = 123 is your criterium. Imagine what would happen when you have 500, or even 500,000 records in your current setup. The database would execute your query, gather ALL rows from that database, transfer them to your application where your application would iterate all 500,000 results. Or you ask the DB to do what it's good at (and why you're using it in the first place): Gimme the/all record(s) that match criterium X. You'd get 1 or none records (given the unique constraint): 1 = some record matches your criterium, none = no records exist. Saves transfering and "manually having to look at" 499,999 records ;-)