php 如何检查MySQL中是否存在一行？（即检查电子邮件是否存在于 MySQL 中）

How to check if a row exists in MySQL? (i.e. check if an email exists in MySQL)

提问by user2882684

I need help checking if a row exists in the database. In my case, that row contains an email address. I am getting the result:

我需要帮助检查数据库中是否存在一行。就我而言，该行包含一个电子邮件地址。我得到的结果：

email no longer exists [email protected]

This is the code I'm currently using:

这是我目前使用的代码：

if (count($_POST)) {
    $email = $dbl->real_escape_string(trim(strip_tags($_POST['email'])));

    $query = "SELECT `email` FROM `tblUser` WHERE `email` = '$email'";
    $result = mysqli_query($dbl, $query);
    if (is_resource($result) && mysqli_num_rows($result) == 1) {
        $row = mysqli_fetch_assoc($result);
        echo $email . " email exists " .  $row["email"] . "\n";
    } else {
        echo "email no longer exists" . $email . "\n";
    }
}

Is there a better way to check if a row exists in MySQL (in my case, check if an email exists in MySQL)?

有没有更好的方法来检查 MySQL 中是否存在一行（在我的情况下，检查 MySQL 中是否存在电子邮件）？

回答by Funk Forty Niner

The following are tried, tested and proven methods to check if a row exists.

以下是用于检查行是否存在的尝试、测试和证明的方法。

(Some of which I use myself, or have used in the past).

（其中一些是我自己使用的，或者过去使用过的）。

Edit:I made an previous error in my syntax where I used mysqli_query()twice. Please consult the revision(s).

编辑：我在语法中犯了一个错误，我使用了mysqli_query()两次。请查阅修订版。

I.e.:

IE：

if (!mysqli_query($con,$query))which should have simply read as if (!$query).

if (!mysqli_query($con,$query))应该简单地读为if (!$query).

• I apologize for overlooking that mistake.

• 我很抱歉忽略了那个错误。

Side note:Both '".$var."'and '$var'do the same thing. You can use either one, both are valid syntax.

附注：无论'".$var."'和'$var'做同样的事情。您可以使用任何一种，两者都是有效的语法。

Here are the two edited queries:

以下是两个编辑过的查询：

$query = mysqli_query($con, "SELECT * FROM emails WHERE email='".$email."'");

    if (!$query)
    {
        die('Error: ' . mysqli_error($con));
    }

if(mysqli_num_rows($query) > 0){

    echo "email already exists";

}else{

    // do something

}

and in your case:

在你的情况下：

$query = mysqli_query($dbl, "SELECT * FROM `tblUser` WHERE email='".$email."'");

    if (!$query)
    {
        die('Error: ' . mysqli_error($dbl));
    }

if(mysqli_num_rows($query) > 0){

    echo "email already exists";

}else{

    // do something

}

You can also use mysqli_with a prepared statementmethod:

您还可以使用mysqli_准备好的语句方法：

$query = "SELECT `email` FROM `tblUser` WHERE email=?";

if ($stmt = $dbl->prepare($query)){

        $stmt->bind_param("s", $email);

        if($stmt->execute()){
            $stmt->store_result();

            $email_check= "";         
            $stmt->bind_result($email_check);
            $stmt->fetch();

            if ($stmt->num_rows == 1){

            echo "That Email already exists.";
            exit;

            }
        }
    }

Or a PDO methodwith a prepared statement:

或者带有准备好的语句的 PDO 方法：

<?php
$email = $_POST['email'];

$mysql_hostname = 'xxx';
$mysql_username = 'xxx';
$mysql_password = 'xxx';
$mysql_dbname = 'xxx';

try {
$conn= new PDO("mysql:host=$mysql_hostname;dbname=$mysql_dbname", $mysql_username, $mysql_password); 
     $conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
} catch (PDOException $e) {
     exit( $e->getMessage() );
}

// assuming a named submit button
if(isset($_POST['submit']))
    {

        try {
            $stmt = $conn->prepare('SELECT `email` FROM `tblUser` WHERE email = ?');
            $stmt->bindParam(1, $_POST['email']); 
            $stmt->execute();
            while($row = $stmt->fetch(PDO::FETCH_ASSOC)) {

            }
        }
        catch(PDOException $e) {
            echo 'ERROR: ' . $e->getMessage();
        }

    if($stmt->rowCount() > 0){
        echo "The record exists!";
    } else {
        echo "The record is non-existant.";
    }


    }
?>

• Prepared statements are best to be used to help protect against an SQL injection.

• 准备好的语句最好用于帮助防止 SQL 注入。

---

---

N.B.:

注意：

When dealing with forms and POST arrays as used/outlined above, make sure that the POST arrays contain values, that a POST method is used for the form and matching named attributes for the inputs.

在处理上面使用/概述的表单和 POST 数组时，请确保 POST 数组包含值，POST 方法用于表单并匹配输入的命名属性。

• FYI: Forms default to a GET method if not explicity instructed.

• 仅供参考：如果没有明确指示，表单默认为 GET 方法。

Note: <input type = "text" name = "var">- $_POST['var']match. $_POST['Var']no match.

注意：<input type = "text" name = "var">-$_POST['var']匹配。$_POST['Var']没有匹配。

• POST arrays are case-sensitive.

• POST 数组区分大小写。

Consult:

咨询：

• http://php.net/manual/en/tutorial.forms.php

• http://php.net/manual/en/tutorial.forms.php

Error checking references:

错误检查参考：

• http://php.net/manual/en/function.error-reporting.php
• http://php.net/manual/en/mysqli.error.php
• http://php.net/manual/en/pdo.error-handling.php

• http://php.net/manual/en/function.error-reporting.php
• http://php.net/manual/en/mysqli.error.php
• http://php.net/manual/en/pdo.error-handling.php

Please note that MySQL APIs do not intermix, in case you may be visiting this Q&A and you're using mysql_to connect with (and querying with).

请注意，MySQL API 不会混合使用，以防您可能正在访问此问答并且您正在使用mysql_连接（和查询）。

• You must use the same one from connecting to querying.

• 从连接到查询，您必须使用相同的方法。

Consult the following about this:

关于这一点，请咨询以下内容：

• Can I mix MySQL APIs in PHP?

• 我可以在 PHP 中混合 MySQL API 吗？

If you are using the mysql_API and have no choice to work with it, then consult the following Q&A on Stack:

如果您正在使用mysql_API 并且无法选择使用它，请参考以下 Stack Q&A：

• MySql php: check if Row exists

• MySql php：检查行是否存在

The mysql_*functions are deprecated and will be removed from future PHP releases.

这些mysql_*函数已被弃用，并将从未来的 PHP 版本中删除。

• It's time to step into the 21st century.

• 是时候跨入21世纪了。

---

---

You can also add a UNIQUE constraint to (a) row(s).

您还可以向 (a) 行添加 UNIQUE 约束。

References:

参考：

• http://dev.mysql.com/doc/refman/5.7/en/constraint-primary-key.html
• http://dev.mysql.com/doc/refman/5.7/en/alter-table.html
• How to check if a value already exists to avoid duplicates?
• How add unique key to existing table (with non uniques rows)

• http://dev.mysql.com/doc/refman/5.7/en/constraint-primary-key.html
• http://dev.mysql.com/doc/refman/5.7/en/alter-table.html
• 如何检查值是否已存在以避免重复？
• 如何向现有表添加唯一键（具有非唯一行）

回答by Emilio Gort

You have to execute your query and add single quote to $email in the query beacuse it's a string, and remove the is_resource($query)$query is a string, the $result will be the resource

您必须执行查询并在查询中向 $email 添加单引号，因为它是一个字符串，并删除is_resource($query)$query 是一个字符串，$result 将是资源

$query = "SELECT `email` FROM `tblUser` WHERE `email` = '$email'";
$result = mysqli_query($link,$query); //$link is the connection

if(mysqli_num_rows($result) > 0 ){....}

UPDATE

更新

Base in your edit just change:

在您的编辑中，只需更改：

if(is_resource($query) && mysqli_num_rows($query) > 0 ){
        $query = mysqli_fetch_assoc($query);
        echo $email . " email exists " .  $query["email"] . "\n";

By

经过

if(is_resource($result) && mysqli_num_rows($result) == 1 ){
        $row = mysqli_fetch_assoc($result);
         echo $email . " email exists " .  $row["email"] . "\n";

and you will be fine

你会没事的

UPDATE 2

更新 2

A better way should be have a Store Procedure that execute the following SQL passing the Email as Parameter

更好的方法应该是有一个存储过程，它执行以下 SQL，将电子邮件作为参数传递

SELECT IF( EXISTS (
                  SELECT *
                  FROM `Table`
                  WHERE `email` = @Email)
          , 1, 0) as `Exist`

and retrieve the value in php

并检索php中的值

Pseudocodigo:

伪羚羊：

 $query = Call MYSQL_SP($EMAIL);
 $result = mysqli_query($conn,$query);
 $row = mysqli_fetch_array($result)
 $exist = ($row['Exist']==1)? 'the email exist' : 'the email doesnt exist';

回答by robhoomph

After validation and before INSERT check if username already exists, using mysqli(procedural). This works:

在验证之后和插入之前检查用户名是否已经存在，使用 mysqli(procedural)。这有效：

//check if username already exists
       include 'phpscript/connect.php'; //connect to your database

       $sql = "SELECT username FROM users WHERE username = '$username'";
       $result = $conn->query($sql);

       if($result->num_rows > 0) {
           $usernameErr =  "username already taken"; //takes'em back to form
       } else { // go on to INSERT new record

回答by Dharman

There are multiple ways to check if a value exists in the database. Let me demonstrate how this can be done properly with PDO and mysqli.

有多种方法可以检查数据库中是否存在值。让我演示如何使用 PDO 和 mysqli 正确完成此操作。

PDO

PDO

PDO is the simpler option. To find out whether a value exists in the database you can use prepared statement and fetchColumn(). There is no need to fetch any data so we will only fetch 1if the value exists.

PDO 是更简单的选择。要查明数据库中是否存在值，您可以使用准备好的语句和fetchColumn(). 不需要获取任何数据，所以我们只会获取1值是否存在。

<?php

// Connection code. 
$options = [
    \PDO::ATTR_ERRMODE            => \PDO::ERRMODE_EXCEPTION,
    \PDO::ATTR_EMULATE_PREPARES   => false,
];
$pdo = new \PDO('mysql:host=localhost;port=3306;dbname=test;charset=utf8mb4', 'testuser', 'password', $options);

// Prepared statement
$stmt = $pdo->prepare('SELECT 1 FROM tblUser WHERE email=?');
$stmt->execute([$_POST['email']]);
$exists = $stmt->fetchColumn(); // either 1 or null

if ($exists) {
    echo 'Email exists in the database.';
} else {
    // email doesn't exist yet
}

For more examples see: How to check if email exists in the database?

有关更多示例，请参阅：如何检查数据库中是否存在电子邮件？

MySQLi

MySQLi

As always mysqli is a little more cumbersome and more restricted, but we can follow a similar approach with prepared statement.

与往常一样，mysqli 更麻烦，也更受限制，但我们可以对准备好的语句采用类似的方法。

<?php

// Connection code
mysqli_report(MYSQLI_REPORT_ERROR | MYSQLI_REPORT_STRICT);
$mysqli = new \mysqli('localhost', 'testuser', 'password', 'test');
$mysqli->set_charset('utf8mb4');

// Prepared statement
$stmt = $mysqli->prepare('SELECT 1 FROM tblUser WHERE email=?');
$stmt->bind_param('s', $_POST['email']);
$stmt->execute();
$exists = (bool) $stmt->get_result()->fetch_row(); // Get the first row from result and cast to boolean

if ($exists) {
    echo 'Email exists in the database.';
} else {
    // email doesn't exist yet
}

Instead of casting the result row(which might not even exist) to boolean, you can also fetch COUNT(1)and read the first item from the first row using fetch_row()[0]

除了将结果行（甚至可能不存在）转换为布尔值之外，您还可以COUNT(1)使用以下命令从第一行中获取和读取第一项fetch_row()[0]

For more examples see: How to check whether a value exists in a database using mysqli prepared statements

有关更多示例，请参阅：如何使用 mysqli 准备好的语句检查数据库中是否存在值

Minor remarks

小备注

• If someone suggests you to use mysqli_num_rows(), don't listen to them. This is a very bad approach and could lead to performance issues if misused.
• Don't use real_escape_string(). This is not meant to be used as a protection against SQL injection. If you use prepared statements correctly you don't need to worry about any escaping.
• If you want to check if a row exists in the database before you try to insert a new one, then it is better not to use this approach. It is better to create a unique key in the database and let it throw an exception if a duplicate value exists.

• 如果有人建议你使用mysqli_num_rows()，不要听他们的。这是一种非常糟糕的方法，如果滥用可能会导致性能问题。
• 不要使用real_escape_string(). 这并不意味着用作防止 SQL 注入的保护。如果您正确使用准备好的语句，则无需担心任何转义。
• 如果您想在尝试插入新行之前检查数据库中是否存在一行，那么最好不要使用这种方法。最好在数据库中创建一个唯一键，如果存在重复值，则让它抛出异常。