如何检查表中是否已存在值? [英] How do I check if value already exists in table?

问题描述

我一直在为数据库中的检查电子邮件是否存在问题"而苦苦挣扎.整个功能是关于更改帐户的电子邮件地址.

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.

推荐答案

使用 unique constraint .尝试插入/更新记录，并捕获违反唯一约束时引发的异常.这是保证唯一的电子邮件地址的唯一方法.先检查然后更新很容易出现并发问题，因为其他人可能会在检查之后和更新记录之前将他/她的记录更新为您尝试设置的相同值.

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.

还:学习如何使用 where -条款.现在，您正在检索所有记录，遍历它们，等等.这将不必要地占用大量资源，花费的时间太长，而且仅仅是浪费.如果要检查符合条件的记录，请编写:

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

其中baz = 123是您的标准.想象一下，如果当前设置中有500条甚至500,000条记录，将会发生什么.该数据库将执行您的查询，从该数据库中收集所有行，然后将它们转移到应用程序中，在该应用程序中您的应用程序将迭代所有500,000个结果.或者您要求数据库做其擅长的事情(以及为什么要首先使用它):给与标准X匹配的所有记录(给定).您将获得1条记录或无记录(给定唯一的记录)约束):1 =某些记录与您的标准匹配，无=没有记录存在.节省了传输和手动查看"的499,999条记录;-)

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 ;-)

这篇关于如何检查表中是否已存在值?的文章就介绍到这了，希望我们推荐的答案对大家有所帮助，也希望大家多多支持IT屋！